Need on subfolders that are numbers

I have the following code which puts the names of all subfolders in a
folder into a table.  But, I want to limit it to only subfolders that
have a name that is numbers because most subfolders are named 1000,
1001, etc. but some subfolders in this folder  have a name like
"archives", I only want the numbered folders - How can I limit it to
only the number folders, what code should be added.:

Dim fso As Object, objfolder As Object
    Dim fld As Object, fyl As Object
    Dim n As Integer
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim cmd As ADODB.command
    Dim strSQL As String
    Dim strFile As String
    Dim strFullPath As String

    Set cmd = New ADODB.command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandType = adCmdText

    ' wipe the table
    strSQL = "DELETE * FROM tblSubFolders"
    cmd.CommandText = strSQL
    cmd.Execute

    ' set your parent folder here that you want subfolders from
    Set objfolder = fso.GetFolder("d:\apps\database documents")

    ' fill tblSubFolders with the folder names and created date
    If objfolder.Subfolders.Count Then
        For Each fld In objfolder.Subfolders
            lDirNum = lDirNum + 1

            strSQL = "INSERT INTO tblSubFolders(FolderName,
CreatedDateTime) VALUES (" & _
            Chr(34) & fld.Name & Chr(34) & "," & Chr(34) &
fld.DateCreated & Chr(34) & ")"

            cmd.CommandText = strSQL
            cmd.Execute
        Next fld
    End If

    Set cmd = Nothing
    Set fld = Nothing
    Set objfolder = Nothing
    Set fso = Nothing

0
brad7100
11/30/2007 3:12:36 AM
access 16762 articles. 3 followers. Follow

1 Replies
589 Views

Similar Articles

[PageSpeed] 15

change to:

   If IsNumeric(FolderName) Then  cmd.Execute


HtH

Pieter

<brad7100@bellsouth.net> wrote in message 
news:80a9acc8-21ba-476e-b869-b06eda79ec08@s36g2000prg.googlegroups.com...
>I have the following code which puts the names of all subfolders in a
> folder into a table.  But, I want to limit it to only subfolders that
> have a name that is numbers because most subfolders are named 1000,
> 1001, etc. but some subfolders in this folder  have a name like
> "archives", I only want the numbered folders - How can I limit it to
> only the number folders, what code should be added.:
>
> Dim fso As Object, objfolder As Object
>    Dim fld As Object, fyl As Object
>    Dim n As Integer
>    Set fso = CreateObject("Scripting.FileSystemObject")
>    Dim cmd As ADODB.command
>    Dim strSQL As String
>    Dim strFile As String
>    Dim strFullPath As String
>
>    Set cmd = New ADODB.command
>    cmd.ActiveConnection = CurrentProject.Connection
>    cmd.CommandType = adCmdText
>
>    ' wipe the table
>    strSQL = "DELETE * FROM tblSubFolders"
>    cmd.CommandText = strSQL
>    cmd.Execute
>
>    ' set your parent folder here that you want subfolders from
>    Set objfolder = fso.GetFolder("d:\apps\database documents")
>
>    ' fill tblSubFolders with the folder names and created date
>    If objfolder.Subfolders.Count Then
>        For Each fld In objfolder.Subfolders
>            lDirNum = lDirNum + 1
>
>            strSQL = "INSERT INTO tblSubFolders(FolderName,
> CreatedDateTime) VALUES (" & _
>            Chr(34) & fld.Name & Chr(34) & "," & Chr(34) &
> fld.DateCreated & Chr(34) & ")"

            cmd.CommandText = strSQL
           'Here
            If IsNumeric(FolderName) Then  cmd.Execute
>        Next fld
>    End If
>
>    Set cmd = Nothing
>    Set fld = Nothing
>    Set objfolder = Nothing
>    Set fso = Nothing
> 


0
Pieter
11/30/2007 8:56:01 AM
Reply:

Similar Artilces:

Non Numbers Cells
Hello, I would like to know if I can create a Macro that will do a search in column A (from A2 to A65536) and every row that has anything else than a number, delete the entire row (not just the cell). Thanks!!! Hi try the following macro: Sub delete_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If not isnumeric(Cells(row_index, 1).Value) then Rows(row_index).delete End If Next Application.ScreenUpdating = True End Sub >-----Origi...

How to join auto number and letters
Hi, I have a form for issuing a letter number for our letter book. In this form , I have a text box with auto number . what I need is to issue auto number start with letters. For example: APP&P1, which 1 is the auto number and it is start with “APP&P”. once the user complete the letter subject, letter receive and the sender , will receive a control number or letter number. What about having two fields, where the first field contains the letter prefix and the second field contains the number? You could display the two fields in a calculated control combining the data. "...

Deleting check numbers
I finally got online updates to match downloaded transactions with my manual entries. I did this by opening up a completely new file (starting pretty much from scratch), only way it would work. Now there are 2 new problems. One, when I "accept", it is replacing check numbers with "check", even though it keeps my manual payee name. And two, it keeps downloading the same transactions so I repeatedly have to accept them over and over again after each update. Any thoughts? Is this all still a part of the same Yodlee problems, or is there an adjustable setting that ...

count number 12-17-07
Is there a way to show a count of records with the same part numbers. Jeff Create a new query in design view. Add the table with the part numbers. Click on the Totals button (looks like a "sum of" button from Excel). Add the [PartNumber] field ... leave the aggregation as "GroupBy". Add the table's primary key field ... change the aggregation to "Count". Run it. Regards Jeff Boyce Microsoft Office/Access MVP "Jeff Klein" <jklein@nospam> wrote in message news:eKYDD6NQIHA.280@TK2MSFTNGP03.phx.gbl... > Is there a way to show a c...

Report data needs to conform to db format
Each month I receive a report, say 200 lines (rows) 8 columns The information is grouped and separated as follows: Date Name Code Div Cost 05/15/04 Jim Bc West 20.00 05/20/04 John CB East 15.00 06/15/04 Judy BC South 35.00 Grp1 70.00 The sample above is a block of 3 the next block might be a block of 1, then 3rd a block of 5 or 7 and so on I'm currently manually Inserting a new column A:A and entering into A2:A4 the "Grp1" whic...

Merging 2 excel sheets with numbers and words
I started with one sheet (using Excel 2003) but took a copy home (on a flash drive)to work on and added data (both numbers and "X" 's) - using Excel 2007. I saved the work done at home so Excel 2003 could "read" it. Then, I did some work on the copy my computer at work (BIG mistake!!) So, the problem is: I have some data on my flash drive copy which is not on the work computer sheet, and vice versa. I want to merge the 2 copies - so I cannot replace either one because data will be lost. Options??? Way to do this? (It seems like it will be easier to "Co...

put numbers back on a calendar template after removing shading?
O n the Event Schedule Planner template, when I remove shading from the calendar the numbers are removed also. It won't let me put them back. When I use "no fill" it removes the numbers too. Exactly what template are you using? What version Publisher? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "dfarnelli" <dfarnelli@discussions.microsoft.com> wrote in message news:162D36F1-E593-4EB2-B0A2-F769CBE0C1C0@microsoft.com... > O > n the Event Schedule Planner template, when I remove shading from the...

Formula to see if a number is allocated
I need to find out if a number is allocated or free. Basically on sheet1 is a load of information. in Column G on this sheet is a unique identifier number. In sheet2 I have a list of numbers say from 1-100 which correspond to the identifier numbers in sheet1. Id like a forumla something like: "If A1 in sheet 2 = any number in column G in sheet1, then print Allocated, else print free" At the moment I have: =IF(A1=Sheet1!G:G,"allocated", "free") But it does not work. What it is doing is If A1 = everything in column G then say allocated, else say fre...

Combining Worksheet Numbers
And old problem revisited. I brought this question to this board about a year ago and got two spectacular answers from Tom Ogilvy and Ken Wright. See previous thread ---> http://tinyurl.com/2yleu I will repeat my question again just in case Anyway, I have the same problem/question, but this time instead of all of the worksheets having a static set of values (names), the players will be changing positions from "season" to "season." Thus, the Start to finish thing doesn't seem to work. I am doing a career statistical page for an online soccer simulation. I want to...

Microsoft Office has encountered a problem and needs to close. --H
I'm having issues with Outlook 2007. I get the following error message; Microsoft Office has encountered a problem and needs to close. We are sorry for the inconvenience. Click on the link to see screen shot error; http://i115.photobucket.com/albums/n311/jokicmla/Outlook%20Error/outlookerror1con.jpg Then I get the following error dialog box, when I click "Send it to Microsoft". Click on the link to see screen shot error; http://i115.photobucket.com/albums/n311/jokicmla/Outlook%20Error/outlookerror1.jpg This is the report that I received from Microsoft after sending “error re...

Hiding a column of hyperlinks behind a column of numbers
I have two adjacent columns in Excel 2007: one with numbers, one with hyperlinks. I wish to "place" the column of hyperlinks behind the column of numbers, so if one clicks on the "number" the hyperlink will open. I know how to do this to each individual cell one at a time, but the columns have a couple thousand numbers and hyperlinks. Is there a way I can combine or merge the columns all at once to do what I want? Thank you for any response. Are your hyperlinks the result of the =HYPERLINK() function or are they Inserted hyperlinks?? -- Gary''s Stud...

Any1 know how-to create the input file needed for the Exchange DST Update Tool?
I have tried different methods suggested by MS, but I am unable to produce an input file in the following format: User_DNTABServer_Friendly_NameTABOS_Time Zone_Registry_KeyCRLF You would think the script would produce an output in the same format to use for the update... You are running MSEXTMZ in "Time Zone Extraction" Mode? (That is the mode that creates the TAB delimited TZ file) The account you are running under has correct permissions (Full mailbox access and Send As)? What does your .ini file look like? <slerche@gmail.com> wrote in message news:1171639396.9825...

Transation sequence number issue
Happy new year everybody!! I have a customer with this situation. his invoice must be sequentially he can't skip a transaction number for his invoices, when a transaction is aborted a number is skiped. i konw that one is not hard to fix but this is the main issue when he do a return he need to have a differnt count numbers than the transactions number for example when he sale something the transation number 1545 is generated but is he return or do a store credit the transaction number is 145 without affectin the secuence os the sale transaction number. can be done? the other probl...

Macro for detect palindromes and repeats in letters/numbers string
Dear all, I am looking to detect palindromes (sentence or number or other sequence of units that can be read the same way in either direction) and repeats (sequences of letters or numbers which are repeating atleast twice within a string) in some strings containing between 20-5000 letters. Has somebody any idea how could I perform that using an Excel macro? I would like that the string to be evaluated could be on cell "A1" and that the detected palindromes and repeats could be listed bellow A2 and C2, respectively; and that the number of times that they appear in the sentence could b...

The number 1 is appened to filename after file is opened
I am trying to find out why the number 1 is added to the file name after it is opened in Excel? I am using Excel from Office 97. It only happens when I open the file from Windows Explorer or from a shortcut on my desktop. It sounds like windows is using New as the default action when you doubleclick on the filename in windows explorer. If you right click on a *.xls file, you'll see a bunch of options--one will be Open and one will be New. Open should be the bold (default) one. I bet you'll see New in bold. If that's the case, you can try this: Close Excel and Windows Start...

Need to redownload/install after hard drive crash
My hard drive crashed and I'm in the process of reinstalling/rebuilding everything, however I don't have a saved copy of Money Premium that I purchased. I still have my emailed receipt and can provide proof of purchase. How do I download my registered copy again? Depends on the version, but if you bought the downloaded version of 2005 or 2006 see http://money.mvps.org/faq/article/132.aspx -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://money.mvps.org/faq/default.aspx for tips and fixes for MS Money. To send Microsoft your product wishes see http://mo...

VARIANT VT_BTSTR Do I need to free memory ?
I have something like that : VARIANT var; var.vt = VT_BSTR; var.bstrVal = ::SysAllocStringByteLen(...); ...... Do I need to call SysFree() to free memory after I finished using var ? Thanks in advance, Kamil Use VariantClear. Better yet, use one of many variant classes that come with VC++ libraries (COleVariant). Then it's done automatically. "kamil" <kamildobk@xxxpoczta.onet.pl> wrote in message news:%23I7De%23UhGHA.3572@TK2MSFTNGP04.phx.gbl... >I have something like that : > VARIANT var; > var.vt = VT_BSTR; > var.bstrVal = ::SysAllocStringByteLen(...)...

Number of fields in report
There is a limit to the number of fields that one can add to a report. What is it? -- See me for who I am and not who you want me to be. That way no one gets disappointed! Yes, there is a limit to the number of "controls" on both forms and reports. I think it is about 754. You could search Help on "specifications" to find all of these types of maximums. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "Lisa R" wrote: > There is a limit to the number of fields...

function help needed
I am trying to put together a function for error logging into an sql2005 table. The function will be in the front end of the application. Below is what I have so far that is getting me frustrated. Originally, I had a stored procedure to with the insert which when executed worked fine. Most of it is below the execute line. I am trying to move it into the front end. As of now, I'm not even sure if I'm on the right path or not. The INSERT INTO is the table and the field. The ErrorDate is a datetime and ApplicationSignon is a bit. The remaining fields are varchar. ...

Number format for Combobox
Hi, I have a user form that reads a list of numbers and places them into a combobox. Often I have to add a new number to the list and here is the problem. If I type a new number into the list and run the form, the new number is not displayed in the combobox. I have found that if I drag the old number list down and increase the number to the new one I require and then delete any intervening numbers that are not required, the combo box will then read the new additional number. I have tried formatting all numbers to the same format but it makes no difference. It is no hardship for me t...

Help Needed Urgently!! #2
that works great thank you very muc -- Zapp Branniga ----------------------------------------------------------------------- Zapp Brannigan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1549 View this thread: http://www.excelforum.com/showthread.php?threadid=27073 ...

tattslotto numbers
Hi I have (and bet not the only one) about 72 games in this big tattslotto entered in cells 6 across and 72 rows I would like to have excel check the winning numbers for me. A:1 to F:1 holds the individual game Row1 to row 72 holds the games K100 to K 106 holds the winning numbers a range called WN Can anyone help me with a formula please??? I would like the column G to count the number of corresponding numbers i got right Hope this is the way to do this Regards Graham Hi! Try this formula entered as an array with the key combo of CTRL,SHIFT,ENTER: =SUM(COUNTIF(A1:F1,wn)) Also, as an...

Repost:Money 2003 stops numbering loan payments
Money 2003. All of a sudden, money has stopped posting a "payment number" under the "pmt num" column on both my Mortgage and Car Loans. Both are paid via transfer from my checking account through electronic bill paying. On some of them Money will let me add the number later manually but on others it says "You cannot change this transaction into an electronic payment or transfer because the date is not within an acceptable range and cannot be changed." All transfers and payments went through properly with no problems and were recorded properly except for the miss...

Add totals at end of the report with unknow number of rows with VB
I have a report that I download into Excel each day. The amount of rows vary each day. I want to add to an existing macro code that will add at the end of the report (regardless of how many rows there are), in column the text of "Total" and then add the sum of column B (currency) and place that total in column B on the same row as Total. On the next row I would like to add the text "Items" in column A and then have the count display in Column B. I just don't know the code to show this because of not being able to designate what row it will display on. I coul...

Need lookup help
Hi, I've been working on this sheet off and on for about 3 years and I think I'm about done with it. However I have one lookup that I can't seem to figure out. To help see what I'm trying to explain, you can download the file at http://armybytes.com/cgi-bin/download.pl?file=1 Basically, I need the field titled 'Screening Table Weight' in both sheets 2 (DA Form 5500-R, cell D20) and 3 (DA Form 5501-R, cell I21) to fill themselves based on the person's height and age from the first sheet (Screening Table Weight) For instance, based on the DA Form 5500-R, the indi...