Import Dynamic and Static ranges from Excel in to MS-Access that do not start at cell A1

Question

How might I link a data range from an Excel spreadsheet so that the data in that range appears as a useable table in Access?

Linking to an Excel sheet that has data starting at cell A1 is easy enough, but where data in the Excel spreadsheet starts elsewhere in the sheet, I'm not sure how to get Access to pin-point it, especially if that non-A1 range is dynamic.

Access doesn't seem to pick up named/dynamic ranges when going through the import/link wizard.

Real world scenario:

I have an Excel spreadsheet file, let's call it "ExcelFile1.xls", that is provided to me externally (so I can't really change its formatting).

1 of the sheets/tabs, let's call it "Dynamic", has a data range I want available as a table in Access, but its column headings start at row 14, going across to column EL. What I'd like Access to do is pick up this data range as a table. Furthermore, "ExcelFile1.xls" will also get updated periodically, i.e. a new version of the "ExcelFile.xls" file will become available, but with more data below row 14's column headings, so ideally I would like Access to pick up the new data in this range whenever I overwrite the previous version of "ExcelFile1.xls".

I also have another Excel spreadsheet file, let's call it "ExcelFile2.xls", again provided to me externally.

This has a sheet/tab, let's call it "Static", that similarly has a data range I want as a table in Access, and again, there will be newer versions of "ExcelFile2.xls" that will overwrite previous versions that I would ideally like Access to pick up on. This range is A14:O19 and will always be this range (i.e. static range).

So in summary: I'd like to link Access to 2 data ranges from 2 respective Excel files to produce 2 separate tables available in Access. 1 of the source data ranges will be dynamic and the other will be static. I'd like Access to pick up on the new data if possible by simply overwriting the source Excel files with new versions of the exact same file name and path.

Answer 1

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.

Answer 2

I'm not an Access guy but let me give you some pointers that should be able to help you.

1 of the sheets/tabs, let's call it "Dynamic", has a data range I want available as a table in Access, but its column headings start at row 14, going across to column EL. What I'd like Access to do is pick up this data range as a table. Furthermore, "ExcelFile1.xls" will also get updated periodically, i.e. a new version of the "ExcelFile.xls" file will become available, but with more data below row 14's column headings, so ideally I would like Access to pick up the new data in this range whenever I overwrite the previous version of "ExcelFile1.xls".

OK, so on this worksheet you will want to establish a dynamic Named Range. Basically you create a formula that determines/resizes the range whenever new data is added to it.

For an overview of how to create dynamic named ranges, start here:

http://support.microsoft.com/kb/830287

With VBA, then, worst-case scenario is that you can access this named range, read its contents in to an array variable or simply iterate over the rows/columns, and then write those contents out to your Access table. But, not being an Access programmer, there may be some slightly more efficient ways of doing this.

This has a sheet/tab, let's call it "Static", that similarly has a data range I want as a table in Access, and again, there will be newer versions of "ExcelFile2.xls" that will overwrite previous versions that I would ideally like Access to pick up on. This range is A14:O19 and will always be this range (i.e. static range).

You can likewise create another Named Range that defines as =$A$14:$O$19 which will be a static named range. Then, you can treat it just like the above.

EDIT Here is an example of getting the Excel data and then iterate over the rows & columns, you just need to add code to add the fields/records/etc to the table in Access.

Sub ImportDataFromRange()
'Access variables
Dim dbFile As Database
Dim tbl As TableDef, fld As Field

'Excel variables
Dim xlApp As Excel.Application
Dim xlFile As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range
Dim r#, c#
Dim clVal As String 'string to hold cell's value, may need to modify this type.

Set dbFile = CurrentDb

'Use this to create a new table definition
'    Set tbl = dbFile.CreateTableDef("Test")
'Use this if your table already exists:
    Set tbl = dbFile.TableDefs("Test")

'Get the info from Excel:
Set xlApp = New Excel.Application

Set xlFile = xlApp.Workbooks.Open("C:\Users\david_zemens\desktop\Book1.xlsx")
Set xlSheet = xlFile.Sheets("Sheet1")
Set xlRange = xlSheet.Range("A1:B10")

    For r = 1 To xlRange.Rows.Count
        For c = 1 To xlRange.Columns.Count

            'Add code to append new fields/records/etc to your table

        Next c
    Next r

xlApp.Quit
Set xlApp = Nothing


End Sub

Hope that's enough to get you started!