Sorry to open this back up, but I'm having issues when using dynamic named ranges. The ranges themselves are working fine, but TransferSpreadsheet is not cooperating.
Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TempLanguageImport", ImportPath, False, Range:="Data"
Which resulted in the same error as above, the database engine not being able to find "Data". I also tried, among others:
Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TempImportTable", ImportPath, False, Range:="'Sheet1'!" & XLsheet.Range("Data").Address
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TempImportTable", ImportPath, False, Range:="'" & XLsheet.name & "'!" & XLsheet.Range("Data").Address
Both resulting in the database engine being unable to find the object ''Sheet1'$$A$5:$AG$2384', seemingly changing my exclamation point into a dollar sign. I only resolved this in the first place because it worked when I put the literal string address in the code, but obviously that won't get me very far.