Am importing Excel 2010 xlsx that are reports generated by QuickBooks Enterprise 2015 Online and output as xlsx. files.
Problem is that sometimes QuickBooks includes the Report Heading in the xlsx and sometimes it does not. Per QB, it is not on their to fix list.
Users are not Excel savvy, so this needs to be done without user involvement in Access.
When the first row of the xlsx contains the Access table field names everything works great.
Problem is when there is a heading, 1st row does not contain field names and Access names the columns F1 to F48 plus last column has a variable date/rime in first row so Access assigns that as the field name instead of [f49]. And as a result, all the subsequent queries do not work. (I thought of doing a query and simply renaming the filed names to correct names but since the last field has a variable time/date and is different every time the xlsx is imported, I could not figure out how to reference that column.
What is best way to handle this?
Attached pdf is of both sheets: With heading and Without Heading.
Following is code I use to import the Excel 2010 xlsx. (Each QB xlsx always has 2 sheets and I always import only Sheet1 of each xlsx. )
Private Sub Command0_Click()
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "QB ITEM LIST"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "QB ITEM LIST", "C:\QB Reports as XLSX\QB ITEM LISTING.xlsx", True, "Sheet1!"
‘
‘
DoCmd.SetWarnings True
MsgBox "THIS PROCESS IS FINISHED, YOU CAN RETURN TO MAIN MENU!"
DoCmd.OpenForm ("Switchboard")
End Sub