Results 1 to 4 of 4

Thread: iterating through .xls files in a folder - [RESOLVED]

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Jun 2002
    Posts
    586

    iterating through .xls files in a folder - [RESOLVED]

    Greetings,

    OK, I really appreciate eveyone's input here so far today. OK, now, I have a button on my VBA form, that when clicked should:

    1) Open a folder C:\FSR
    2) For each .xls file in that folder, go to "Tab 3" (which is the actual name of the third tab) and import external data to "tbl_Import_Temp", within the database being used, which is called "FinancialReports".

    Any idea the syntax for that? Here is what I have thus far:

    VB Code:
    1. Private Sub cmdStep1_Click()
    2.     Dim cnnConnection As ADODB.Connection
    3.     Dim rstImportTemp As ADODB.Recordset
    4.     'Dim Detail As String
    5.    
    6.     Set cnnConnection = CurrentProject.Connection
    7.    
    8.     Set rstImportTemp = New ADODB.Recordset
    9.     rstImportTemp.Open "tbl_Import_Temp", cnnConnection, adOpenStatic, adLockReadOnly, adCmdTable
    10.  
    11.     'open folder with Excel files
    12.     'for each file
    13.  
    14.         'Get External Data for tab 3 and put it into tbl_Import_Temp, using the first row as headers.
    15.     'next i
    16.    
    17.     MsgBox "Import of files, into the tbl_Import_Temp, is complete. Make sure the data looks OK; and then click the 'Step 2' button, below."
    18.      
    19.     rstImportTemp.Close
    20.     Set rstImportTemp = Nothing
    21.    
    22.     Set cnnConnection = Nothing
    23. End Sub

    Thank you,
    Jim
    Last edited by JimMuglia; Jul 22nd, 2005 at 10:38 AM. Reason: provide clarification, as well as to indicate resolution

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: How can I automate "Get External Data..." functionality in my code?

    1) Use functions like Dir (read the help files) and a string variable to hold the path and another to hold the returned filename.
    This will give you a filepath and a filename to use in step 2

    2)
    You can import via Access (if you are using that) import (I think it is docmd.transferstext or docmd.transferspreadsheet).

    Or you can open an instance of excel and loop through the cells with the data in.
    More coding, but possibly better/more flexible.

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Jun 2002
    Posts
    586

    Re: How can I automate "Get External Data..." functionality in my code?

    Thank you Ecniv.

    I now have

    VB Code:
    1. For i = 0 To 3
    2.         pathname(i) = Dir("C:\FSR\*.xls")
    3.         MsgBox pathname(i)
    4.     Next i

    My immediate goal is to have it loop through all the Excel files in "C:/FSR".

    I put 0 to 3 there becaue there are 4 files; but I'm actually going to need it to count the files, and then I can say

    i = 0 to filecount.

    Plus at this point, in the loop, the message box keeps displaying only the first file.

    So, two things:
    1) How do I get the filecount for the folder "C:\FSR", and
    2) How do I get the loop to recognize each file, not just the first one?

    For the record, its all .xls files in the folder.

    Thank you,
    Jim
    Last edited by JimMuglia; Jul 22nd, 2005 at 09:30 AM.

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Jun 2002
    Posts
    586

    Re: How can I automate "Get External Data..." functionality in my code?

    Well, I did some figuring out, and I guess a filecount is worthless to me because its just an issue of it iterating through each file.

    I'm all set with the following code I concocted:

    VB Code:
    1. flag = True
    2.     firstflag = True
    3.    
    4.     'go through each .xls file in the folder "C:\FSR"
    5.     Do While flag = True
    6.         If firstflag = True Then
    7.             excelfile = Dir("C:\FSR\*.xls")
    8.             If Len(excelfile) > 0 Then ImportFile (excelfile)
    9.         End If
    10.         firstflag = False
    11.         If firstflag = False Then excelfile = Dir()
    12.         If Len(excelfile) > 0 Then ImportFile (excelfile)
    13.         If Len(excelfile) = 0 Then flag = False
    14.     Loop

    Now that I'm done with being able to access each file, now I have to get on to the meat of the ImportFile function. Odds are, I'll be back in another thread.

    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width