Import range of cells from Excel to Access

[dinodeserter]

I have a database that I am working on and want to import a range of cells from excel. The user will be receiving a daily, monthly, and quarterly excel file in a zipped format. I already got a good starter solution from westconn1 (ThankYou!!!) on how to automatically download, unzip, and save the file. One thing that was suggested to me was to link another excel file to the one with the information and then use that file as the import. I would just need to make sure that the source files name always stays the same. Does anyone have any good solutions for this?

[dinodeserter]

OK, so I was looking online and actually found a pretty good solution that fits my needs. I am going to post it here with credit to the author. The only thing is that I need to either make a way so the file is never locked as opened since more than one user will be using the database, and also alert the user that this is happening.

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.

Matt Hall

[dinodeserter]

So I wanted this to work but I can't seem to be able to get it to go. I want to have a table with field names instead of just field1, field2 and so on. This way when link the data I can make sure that i have the information I am looking for. Here is the other problem. The workbook is macro enabled and it raises an error unless I turn it off using the excelapp.enableevents = false command at the beginning of the code. I read on another forum that it should be turned back on at the end of the code. I am assuming that's because the workbook would have to be saved since the macros have now been turned off. Anyone have any ideas?