EXCEL loop access

[amal2004]

Hello ,

I have 200 workbooks in a folder , every workbook has a sheet with couple of lines of data , all workbooks are the same , I need to write code that will loop inside this folder , open every workbook , take the lines of data and have them all in one Table or excel sheet . Can we do this , thanks for the help

 

[TomHW]

This can be easily done. To get all of the files in a folder you could use something such as:

Dim objFSO as Object
Dim objFile as Object
Dim strPath as String
dim strFilePath as String

strPath = "C:\Workbooks"

Set objFSO = CreateObject("Scripting.FileSystemObject")

For Each objFile in objFSO.GetFolder(strPath)
    [green]' Here you have each file's name[/green]
    strFilePath = strPath & "\" & objFile.Name
    If InStr(strName,".xls") <> 0 Then
        [green]' In here we have made sure you have an Excel Spreadsheet[/green]
        DoCmd.TansferSpreadsheet ...
    End If
Next objFile

I wrote off the top of my head and it has not been tested, but it should give you some good direction. You will also need to finish the TransferSpreadhseet command. Look into the TransferSpreadsheet command to figure out what parameters to pass it. It shouldn't be too difficult, and you can import them all into the same table. The full path to the file including the name is stored in the variable strFilePath. Make sure you substitute the path to your folder as opposed to the same path I have in there.

Hope this helps out,
Tom

 

[amal2004]

Thanks for this nice start. but how will i include MS access in the code . Shell i use ADO or DAO ?

 

[TomHW]

This code goes inside of MS Access. Excel is not used at all. ADO and DAO are not necessary because "DoCmd.TransferSpreadsheet" does all of the work putting the information into the tables.

You can run this code anyway you like in Access. For instance, you could create a form and put this code behind a command button on the form. So when you click the button, Access will find all of the Excel files in the path you specified and import all of them to whatever table you specify in TransferSpreadsheet.

 

[amal2004]

TomHW,

Thanks for your help. i started working with it in access . It stops on this line ""For Each objFile in objFSO.GetFolder(strPath)""
And an error says Object does not support this property or method. Please help me find a way to loop through the files and start appending the data on the first sheet of every file to a one big table in access.

Thanks a million

 

[PHV]

And this ?
For Each objFile in objFSO.GetFolder(strPath).Files

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[amal2004]

i did my first import . Thanks , but i have an error after it loops the first time saying cant find object !!!

Thats what i have

Sub Test()
Dim objFSO As Object
Dim objFile As Object
Dim strPath As String
Dim strFilePath As String

strPath = "C:\Workbooks"

Set objFSO = CreateObject("Scripting.FileSystemObject")
For Each objFile In objFSO.GetFolder(strPath).Files

' Here you have each file's name

strFilePath = strPath & "\" & objFile.Name
If InStr(strFilePath, ".xls") <> 0 Then
' In here we have made sure you have an Excel Spreadsheet

DoCmd.TransferSpreadsheet acImport, 8, "TBL_DEC", objFile.Name, True

End If

Next objFile

 

[PHV]

Sub Test()
Dim objFSO As Object
Dim objFile As Object
Dim objFolder As Object
Dim strPath As String
Dim strFilePath As String

strPath = "C:\Workbooks"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)
For Each objFile In objFolder.Files
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[amal2004]

Same problem . the code goes throught all lines and when it does the import for the first file and loops ( Next objFile) it get stuck saying cant find file name !!!???

Please help

 

[amal2004]

I think the process is good if we find a way to make it loop without giving an error Cant find file name . What shell I do ???

 

[amal2004]

I did little modification on the code, but now I get a different error
“External table is not in the expected format”


This is what I have

<CODE>
Option Compare Database

Sub Test1()
Dim objFSO As Object
Dim objFile As Object
Dim strPath As String
Dim strFilePath As String
Dim objFolder As Object


strPath = "C:\Workbooks"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

For Each objFile In objFolder.Files





DoCmd.TransferSpreadsheet acImport, 8, "TBL_DEC", objFile, True



Next objFile

End Sub

<CODE/>

 

[TomHW]

Sorry, I've been on vacation for a few days. What I've noticed from your code that you originally posted with the error after importing the first file is that this line:

DoCmd.TransferSpreadsheet acImport, 8, "TBL_DEC", [purple][b]objFile.Name[/b][/purple], True

Should be this:

DoCmd.TransferSpreadsheet acImport, 8, "TBL_DEC", [purple][b]strFilePath[/b][/purple], True

You were using the file's name instead of its entire path when attempting to import. In your last bit of code you posted I noticed that you removed the check to determine if the file name had ".xls" at the end. Without this you may be attempting to import from a file that is not an Excel spreadsheet. I created a table with the same name as yours and ran the following code and it worked for 12 spreadsheets saved in the folder "C:\Workbooks". You will have to change the name of the folder in the code if this is not the name of the folder in which your files are stored.

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim strPath As String
    Dim strFilePath As String

    [green]' Change the following to the folder in which your files are stored[/green]
    strPath = "C:\Workbooks"

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.getfolder(strPath)
    For Each objFile In objFolder.Files

        [green]' Here you have each file's name[/green]
    
        strFilePath = strPath & "\" & objFile.Name
        If InStr(strFilePath, ".xls") <> 0 Then
            [green]' In here we have made sure you have an Excel Spreadsheet[/green]
    
            DoCmd.TransferSpreadsheet acImport, 8, "TBL_DEC", strFilePath, True
         
        End If
 
    Next objFile
End Sub

Hope this helps,

Tom