yes, if you want to import the single months- May-Dec17,
or even the PortalQn sheets.
this code would import all the sheets in the workbook.(upon asking you)
you must link 1 sheet, then create the append query,qaImportXLsheet, that will put the data into your table.
usage:
ImportAllSheets1File "c:\temp\Ray test interviewer spreadsheet.xls"
Code:
'--------------
Public Sub ImportAllSheets1File(ByVal pvFile)
'--------------
Dim colShts As New Collection
Dim sht As Excel.Worksheet
Dim xl As Excel.Application
Dim itm
Dim sName As String, sTbl As String
On Error GoTo errImp
docmd.setwarnings false
Set xl = CreateObject("excel.application")
With xl
.Workbooks.Open pvFile
For Each sht In .Worksheets
sName = sht.Name
colShts.Add sName, sName
Next
.ActiveWorkbook.Close False
.Quit
End With
Set xl = Nothing
sTbl = "xlFile2Import"
For Each itm In colShts
'ask to import the sheet...not all are wanted
If MsgBox("Import " & itm, vbQuestion + vbYesNo, "Confirm") = vbYes Then
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, sTbl, pvFile, True
DoCmd.OpenQuery "qaImportXLsheet"
End If
Next
DoCmd.SetWarnings True
Exit Sub
errImp:
MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
Exit Sub
Resume
End Sub