-
Jul 21st, 2005, 01:23 PM
#1
Thread Starter
Fanatic Member
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:
Private Sub cmdStep1_Click()
Dim cnnConnection As ADODB.Connection
Dim rstImportTemp As ADODB.Recordset
'Dim Detail As String
Set cnnConnection = CurrentProject.Connection
Set rstImportTemp = New ADODB.Recordset
rstImportTemp.Open "tbl_Import_Temp", cnnConnection, adOpenStatic, adLockReadOnly, adCmdTable
'open folder with Excel files
'for each file
'Get External Data for tab 3 and put it into tbl_Import_Temp, using the first row as headers.
'next i
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."
rstImportTemp.Close
Set rstImportTemp = Nothing
Set cnnConnection = Nothing
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
-
Jul 22nd, 2005, 03:53 AM
#2
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.
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...
-
Jul 22nd, 2005, 09:16 AM
#3
Thread Starter
Fanatic Member
Re: How can I automate "Get External Data..." functionality in my code?
Thank you Ecniv.
I now have
VB Code:
For i = 0 To 3
pathname(i) = Dir("C:\FSR\*.xls")
MsgBox pathname(i)
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.
-
Jul 22nd, 2005, 10:35 AM
#4
Thread Starter
Fanatic Member
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:
flag = True
firstflag = True
'go through each .xls file in the folder "C:\FSR"
Do While flag = True
If firstflag = True Then
excelfile = Dir("C:\FSR\*.xls")
If Len(excelfile) > 0 Then ImportFile (excelfile)
End If
firstflag = False
If firstflag = False Then excelfile = Dir()
If Len(excelfile) > 0 Then ImportFile (excelfile)
If Len(excelfile) = 0 Then flag = False
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|