Okay, from the useful pointers on this question here, and on a few others I've posed elsewhere, I think I have a fairly simple solution for anyone wanting to pull data from an Excel spreadsheet as either a Dynamic Range or Static Range where the data in Excel does not start in cell A1.
Both of these examples use a button to initate the code. You obviously don't have to do it this way, but if you do you'll need to create a form and button and then run code builder off the button and replace that code with whichever solution below you need.
Dynamic Range:
Please be mindful that this example of a dynamic range assumes that your range of cells in Excel always starts in the same topmost-leftmost position and that the number of columns is always the same - i.e. the only thing that's dynamic is the bottom row number of your range of cells.
References you'll need to swap-out based on your own set up:
C:\Users\Matt\Desktop\ExcelFile1.xls Replace with the full path of your Excel file
Dynamic Replace with the name of the sheet contained within your Excel file
A14:A2000 Replace with the range you want to test to see how many non-empty cells there are. This range should: start from row of the data where your column headings are situated; cover a column in the data you are looking to import that will never have empty cell entries; cover a range large enough that it will always exceed the number of rows containing actual data in your Excel spreadsheet.
ExcelDynamicRangeData Replace with whatever you want to call the table in Access that will contain the data pulled from your Excel range.
Dynamic!A14:EL Replace with the name of your sheet, the top-most/left-most cell reference and the rightmost column letter(s) of your Excel range. Do not include the bottom-most/right-most row number as this is what needs to be dynamic, hence assigning this to numberofrows and concatenating that to the end of this range later.
numberofrows = 13 ... Replace the 13 with however many rows there are above where your column headings start. E.g. if your column headings start on row 4, this number needs to be 3.
Command0 Replace with the name of your button being used to initiate all this code.
Sub ImportDataFromRange()
' Assign the Excel Object
Dim excelapp As Object
Set excelapp = CreateObject("excel.application")
' Assign the workbook
Dim wb As Object
Set wb = excelapp.Workbooks.Open("C:\Users\Matt\Desktop\ExcelFile1.xls")
' Assign the result of your CountA function used in the next line
Dim numberofrows As Integer
' Get the bottom-most row number needed to complete our dynamic range address
numberofrows = 13 + excelapp.Application.CountA(wb.worksheets("Dynamic").Range("A14:A2000"))
' Delete any previous access table, otherwise the next line will add an additional table each time it is run
DoCmd.DeleteObject acTable, "ExcelDynamicRangeData"
' Import data from Excel using a range that now knows where bottom row number is
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "ExcelDynamicRangeData", "C:\Users\Matt\Desktop\ExcelFile1.xls", True, "Dynamic!A14:EL" & numberofrows
' Close and clean
wb.Close
Set wb = Nothing
excelapp.Quit
Set excelapp = Nothing
End Sub
Private Sub Command0_Click()
ImportDataFromRange
End Sub
Static Range:
This a lot simpler as there's no need to open the Excel workbook to apply a CountA function on the data.
References you'll need to swap-out based on your own set up:
C:\Users\Matt\Desktop\ExcelFile2.xls Replace with the full path of your Excel file
ExcelStaticRangeData Replace with whatever you want to call the table in Access that will contain the data pulled from your Excel range.
Static!A14:EL20 Replace with the name of your sheet, and the full range address of the cells in Excel you want to import. As this method is describing how to get a static range of data from Excel in to Access the data in Excel you are wanting to import should never go out of this range.
Command0 Replace with the name of your button being used to initiate all this code.
Sub ImportDataFromRange()
' Delete any previous access table, otherwise the next line will add an additional table
DoCmd.DeleteObject acTable, "ExcelStaticRangeData"
' Import data from Excel using a static range
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "ExcelStaticRangeData", "C:\Users\Matt\Desktop\ExcelFile2.xls", True, "Static!A14:EL20"
End Sub
Private Sub Command0_Click()
ImportDataFromRange
End Sub
NB:
The acSpreadsheetTypeExcel9 bit in both methods refers to the version of the Excel file you are importing; in my example I'm importing Excel 2000 format; you may be importing a different version of Excel so refer to this to see which version you need to reference in the code; .xlsx files are not listed, but that would be acSpreadsheetTypeExcel12Xml.
My example brings the data in to Access as an active link to the source data. You may find it better to actually import and store the data in to Access instead if you have a lot of data, as linking can cause some performance issues. Swap-out acLink for acImport if this is the case.
If you haven't already manually created a blank table in Access (with the same table name referenced in your code) then you'll either need to do that or comment-out DoCmd.DeleteObject acTable, "yourAccessTable" the first time the code is run and then reinstate this part afterwards.
There is likely more that can be done with this - i.e. adapting the CountA function to take in to account of dynamic numbers of columns if your data source has varying numbers of columns.
Another big thanks to @david-zemens, @gord-thompson as well as other StackoverFlow users for helping me get to this - it's going to be very useful to me and hopefully others.
