Hello All;
I'm having an issue IMPORTING a specific Worksheet within an Excel Workbook. The current error (multiple errors encountered in numerous attempts to cure the fail) is "Field F1 does not exist in destination table "tbl_MyAccessTableName".
Details:
Destination table = "tbl_MyAccessTableName"
WorkBook name to open = "MyWorkBookName_withCurrentDate.xls"
WorkSheet Name = "My WorkSheetName" (yes, there is a space in the worksheet name, and yes there are multiple worksheets in this workbook)
Problematic issues: WorkSheet has column headings in row 1 (columns A through F (which remain constant)), however row range (number of records) varies each day (new file each day).
Destination table has different field names than that of the source worksheet, and has spaces in those source field names. Also, I want to APPEND the imported data to the existing data in the table. Destination table also has additional fields not being appended to.
Code currently being tried, which produces the above reference error:
strTable2=tbl_MyAccessTableName
strFile=Dir(strPath & "*.xls")
(The code prior to the DoCmd line navigates to the file location, and allows selection of the desired workbook file, and works as intended.)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable2, strFile, True, , Range = "[My WorkSheetName]$A2:F500"
'(using 500 in an attempt to over estimate possible number of records)
Removing the 2nd comma after "True" produces the error: Runtime error 3011, "The Microsoft Engine could not file the object "0", bla bla bla.
I have tried so many iterations of the "DoCmd" line that I have lost track. Also tried substituting the "$" with the "!", no joy.
Any help would be greatly appreciated.
Larry