Importing an Excel Spreadsheet into Access when I do not know the ending range
Posted on 2013-01-26
I am having a hard time with something that I know should not be this hard. I have an excel spreadsheet that I need to import into an Access table each month. I know the starting range value (a5) but the file can change size each month so I do not know the ending range value.
In the past the file had the word total at the end so I just did a loop with DoCmd.TransferSpreadsheet acImport and kept checking (using a DLookup) for the word total to appear. That is how I knew to stop the loop.
Since this file has nothing at the end but empty space I tried looking for null or blank but it will not work. It runs and says that it is trying to import cells out of the range (error 3673).