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

This entry was posted in SQL Server, Visual Basic and tagged . Bookmark the permalink.