how to import all the spreadsheets in a folder

I love Access / Visual Basic for importing files.. I just laugh when I talk about doing this same thing in SSIS, it takes MUCH less time to write a simple loop in script than to do it in GUI like SSIS.


Option Compare Database
Option Explicit

Public Sub ImportAllSpreadsheets()
On Error GoTo errHandler

Dim wsh As New FileSystemObject
Dim fld As Folder
Dim fil As File
Dim tblName As String
Dim filPath As String

Set fld = wsh.GetFolder("C:DataABC")

For Each fil In fld.Files
If Right(fil.Name, 4) = ".xls" Then
tblName = Replace(Replace(Replace(Replace(fil.Name, " ", ""), ".xls", ""), "&", ""), "-", "")
filPath = fil.Path

' Stop

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tblName, filPath, True

End If
Next fil

Exit Sub
MsgBox Err.Number & " - " & Err.Description, vbOKOnly
Resume Next
End Sub

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.