How to import all files from the directory

[sabavno]

Hi,

I wonder if anyone could give me an idea of how I could do the following from VBA:

1 - Access a certain folder
2 - List through all the files that there are in that folder
3 - Import all excel files into the Access database.

Any help will be greatly appreciated.

Thanks.

 

[mincefish]

I can help you with the getting all excel files from a specified directory, but the import may be a little more tricky.

Here's the getting the files to pass to the import routine (which I've speculatively called fncImportFiles():

Function fncGetFiles()
Dim strName As String

strName = Dir("C:\Temp\Files\*.xls")

While Not strName = ""
    Call fncImportFiles(strName)
    strName = Dir
Wend

MsgBox "Finished!!"

End Function

There would then be a function that, when passed the name of a file, would import that file.

I have some code (at home ), that you can use to stream the importing of excel files. One way to do it is to make the excel file a linked tables, and simply run some SQL code to make a table from the data in your linked table, and then drop the link. That's quite efficient.

Are all the tables the same format\layout? That would make things infinitely easier - I suspect the answer, as always, will be no!!

HTH - for starters - I'll try and remember the code from home!

Mincefish

 

[sabavno]

Great,

Thanks a lot!

 

[mincefish]

Don't know if you still need the import part, but here's a way you could do it.

I'm also not sure how you'll know which spreadsheets need to be imported to which tables - I'll leave that up to you to figure out!! - I've made a suggestion though.

Function fncImportFiles(strName As String)
Dim rstTemp As New ADODB.Recordset
Dim rstWrite As New ADODB.Recordset
Dim conTemp As New ADODB.Connection
Dim iCtr As Integer

'Open recordset on table in database
'How are we going to know what the table is called?
'Ive assumed here that the spreadsheet has the same name as the table

rstWrite.CursorType = adOpenStatic
rstWrite.CursorLocation = adUseServer
rstWrite.ActiveConnection = CurrentProject.Connection
'Assuming the table is the same name as the spreadsheet, we want to remove the .xls
'from SpreadsheetName.xls to use as the table
rstWrite.Open "SELECT * FROM & " & Left(strName, Len(strName) - 4)

'open connection to spreadsheet
conTemp.Open "DRIVER=Microsoft Excel Driver (*.xls);DBQ=C:\Temp\Files\" & strName

'open recordset on spreadsheet
rstTemp.CursorType = adOpenStatic
rstTemp.CursorLocation = adUseServer
rstTemp.ActiveConnection = conTemp

'NB: This assumes that the data is on the first sheet - and you must have the $ after the name - might
'require some playing around with
rstTemp.Open "SELECT * FROM [Sheet1$]"

Do Until rstTemp.EOF
            
    rstWrite.AddNew
    For iCtr = 0 To rstWrite.Fields.Count - 1
        'Wang the data into the table opened up by rstWrite
        rstWrite.Fields(iCtr) = rstTemp.Fields(iCtr)
    Next
    rstWrite.Update
    
    rstTemp.MoveNext
Loop

'clean up
rstTemp.Close
rstWrite.Close
conTemp.Close

End Function

Incedentally, I've knocked this code out in 10 mins, so I'll apologise if there are any errors in it. I'll be keeping an eye on tek-tips over the next few days, so I'll try and respond with questions asap!

HTH

Mincefish