LOOP HELP

  • Thread starter Thread starter auujxa2 via AccessMonster.com
  • Start date Start date
A

auujxa2 via AccessMonster.com

I need the code for importing all sheets in all workbooks within subfolders.
The sheets are the same. This may help... I already have a table that lists
the path and name of the workbooks. (FNAME and FPATH), but the sheets in all
the files are dynamic. So I need to loop through and transfer all
spreadsheets from all FPATH & FNAME files.

Please help!!

Thanks
 
I need the code for importing all sheets in all workbooks within subfolders.
The sheets are the same.  This may help... I already have a table that lists
the path and name of the workbooks.  (FNAME and FPATH), but the sheets in all
the files are dynamic. So I need to loop through and transfer all
spreadsheets from all FPATH & FNAME files.

Please help!!

Thanks
Click to expand...

you mean you can't find "TransferSpreadsheet" in the help files?
Funny, you can read this stuff... otherwise, please send money.
 
I know how the "TransferSpreadsheet" function works smartass. I'm looking
for the code that help import all sheets from all files. can you do a loop
within a loop? (loop through the pages while looping through the subfolders
to search for all files?)

I need the code for importing all sheets in all workbooks within subfolders.
The sheets are the same.  This may help... I already have a table that lists
Click to expand...
[quoted text clipped - 8 lines]
you mean you can't find "TransferSpreadsheet" in the help files?
Funny, you can read this stuff... otherwise, please send money.
Click to expand...
 
Since you obviously didn't know the answer, let me help you. you don't even
have to send me money...

Dim dbs As Database, rst As Recordset
Dim myPath As String
Dim myCount As Variant
Dim myFile As String
Dim SQL As String
Dim wkb As Excel.Workbook
Dim sht As Excel.WorkSheet
Dim xl As Excel.Application

DoCmd.OpenQuery "ClearMasterQry"

myCount = 0
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Files")
Set xl = New Excel.Application
xl.Visible = False

With rst
Do While Not rst.EOF
myCount = myCount + 1
myPath = rst.Fields("FPath")
myFile = rst.Fields("FName")
Set wkb = xl.Workbooks.Open(myPath & myFile)
With wkb
For Each sht In .Worksheets
DoCmd.TransferSpreadsheet acImport, 8, "MasterTable", myPath &
myFile, False, "U10:AG56"
SQL = "UPDATE MasterTable"
SQL = SQL & " SET F1 = '" & myFile & " - " & sht.Name & "'"
SQL = SQL & " WHERE F1 Is Null"
dbs.Execute SQL
Next
End With
rst.MoveNext
Loop
rst.Close
End With



I need the code for importing all sheets in all workbooks within subfolders.
The sheets are the same.  This may help... I already have a table that lists
Click to expand...
[quoted text clipped - 8 lines]
you mean you can't find "TransferSpreadsheet" in the help files?
Funny, you can read this stuff... otherwise, please send money.
Click to expand...
 
Back
Top