Hi. I need to be able to import excel worksheets in access on a regular basis (about 60 a day), and I wonder if it could be utomated in some way through VB.
You can automate the process in VBA. You can use the FileDialog object to select the folder where the Excel spreadsheets are located, then the FileSearch object to search through the folder for all Excel files to be imported and then use the DoCmd.TransferSpreadsheet command to import the Excel file into Access. You could use code such as this:
Private Sub cmdImportFiles_Click()
' Convert the Excel files to a text file so that they can then be imported
Dim intFileCount As Integer
On Error GoTo ErrorHere
.LookIn = "Folder of Excel files path name" ' You could use the FileDialog object to let the user select the folder with the Excel spreadsheets in them orjust hard code it.
.SearchSubFolders = False ' set to true if you want subfolders to be searched
.FileName = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute Then
For intFileCount = 1 To .FoundFiles.COUNT
' You might need to change some of these options depending on your version of Excel, range names, field names etc
DoCmd.TransferSpreadsheet transferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel9, TableName:="MyTable", FileName:=.FoundFiles(intFileCount), HasFieldNames:=True
MsgBox "No Excel files were found", vbInformation, "No Excel Files Found"
MsgBox "Error In: Form '" & Me.Name & "'" & vbCrLf _
& "Procedure: cmdImportFiles_Click" & _
vbCrLf & "Error Code: " & Err.Number & _
vbCrLf & "Error: " & Err.Description, vbExclamation, "Error Alert"