导入从Excel到MS-Access的动态和静态范围，不能从单元格A1开始 [英] Import Dynamic and Static ranges from Excel in to MS-Access that do not start at cell A1

问题描述

如何从Excel电子表格中链接数据范围，使该范围内的数据在Access中显示为可用表？

链接到Excel表格从单元格A1开始的数据很容易，但是Excel表格中的数据从表格的其他位置开始，我不知道如何使Access定位它，特别是如果非A1范围是动态的。 p>

当通过导入/链接向导时，访问似乎没有提取命名/动态范围。

Real世界场景：

我有一个Excel电子表格文件，我们称之为 ExcelFile1.xls ，这是外部提供给我的（所以我不能真正改变其格式）。

1个表格/标签页，让我们称之为动态，数据范围为希望作为Access中的表可用，但其列标题从第14行开始，直到列EL。我想要的Access要做的就是把这个数据范围作为表格。此外， ExcelFile1.xls 也将定期更新，即新版本的ExcelFile.xls文件将可用，但更多的数据位于第14行的列标题下，所以理想情况下像Access一样，当我覆盖以前版本的 ExcelFile1.xls 时，可以获取这个范围内的新数据。

我还有另一个Excel电子表格文件，我们称之为 ExcelFile2.xls ，再次提供给我外部。

这有一张表/标签，让我们来电它静态，类似地，我需要一个数据范围作为Access中的表，再次，将会有更新版本的 ExcelFile2.xls 将覆盖以前的版本，我最理想的是喜欢Access接听。该范围是 A14：O19 ，并且将始终为此范围（即静态范围）。

所以总结：我想将Access从2个相应的Excel文件链接到2个数据范围，以在Access中生成2个单独的表。源数据范围的1将是动态的，另一个将是静态的。如果可能，我希望Access可以接收新的数据，只需用完全相同的文件名和路径的新版本覆盖源Excel文件。

解决方案

好的，从这里的这个问题的有用指针，还有其他几个我在其他地方提出的，我想我有一个相当简单的解决方案，任何人想要从Excel电子表格拉数据动态范围或静态范围，Excel中的数据不会在单元格A1中启动。

这两个示例都使用按钮来启动代码。你显然不需要这样做，但是如果你需要创建一个表单和按钮，然后运行代码生成器，然后将代码替换成你需要的任何解决方案。

动态范围

请注意，动态范围的这个例子假定您的Excel中的单元格总是以相同的最左边的位置开始，列的数量总是相同的 - 即唯一的动态的是单元格范围的底行数。

您需要根据自己的设置进行交换：

• C：\Users\Matt\Desktop\ExcelFile1.xls 用Excel文件的完整路径替换




• 动态替换Excel文件中包含的工作表名称




• A14：A2000 替换要测试的范围，看看有多少n空的细胞有。此范围应：从您的列标题所在的数据行开始;覆盖您要导入的数据中不会有空单元格条目的列;覆盖一个足够大的范围，它将始终超过Excel电子表格中包含实际数据的行数。




• ExcelDynamicRangeData 替换为您希望在Access中调用表中包含从Excel范围中提取的数据的表。




• 动态！A14：EL 用表格的名称替换Excel范围的最上面/最左侧的单元格引用和最右边的列字母。不要包含最底层/最右行数，因为这是需要动态的，因此将其分配给 numberofrows 并将其连接到此范围的末尾。




• numberofrows = 13 ... 替换13，列标题开始。例如。如果您的列标题从第4行开始，则此数字需要为3。




• Command0 替换为您的按钮的名称用于启动所有此代码。

  Sub ImportDataFromRange（）
 
'分配Excel对象
 Dim excelapp As Object 
设置excelapp = CreateObject（excel.application）
 
'分配工作簿
 Dim wb As Object 
设置wb = excelapp.Workbooks.Open（C：\Users\Matt\Desktop\ExcelFile1.xls）
 
'分配下一行中使用CountA函数的结果
 Dim numberofrows As Integer 
 
'获取完成动态范围地址所需的最下行行数
 numberofrows = 13 + excelapp.Application.CountA（wb.worksheets（Dynamic）。Range（A14：A2000））
 
'删除任何以前的访问表，否则下一个行将在每次运行
 DoCmd时添加一个附加表。 DeleteObject acTableExcelDynamicRangeData
 
'使用现在知道底行号为
的范围从Excel导入数据DoCmd.TransferSpreadsheet acLink，acSpreadsheetTypeExcel9，ExcelDynamicRangeData，C：\ Users\Matt\Desktop\ExcelFile1.xls，True，Dynamic！A14：EL& numberofrows 
 
'关闭并清理
 wb.Close 
设置wb =没有
 excelapp.Quit 
设置excelapp =没有
 
 End Sub 
 
 Private Sub Command0_Click（）
 
 ImportDataFromRange 
 
 End Sub 
  

静态范围：

这很简单，因为没有必要打开Excel工作簿在数据上应用 CountA 函数。

您需要根据您自己设置：

• C：\Users\Matt\Desktop\ExcelFile2 .xls 用Excel文件的完整路径替换




• ExcelStaticRangeData 替换为您希望在Access中调用的表，其中包含从Excel范围中提取的数据。




• Static！A14： EL20 替换您的工作表的名称，以及要导入的Excel中单元格的全范围地址。由于此方法描述如何从Excel中获取数据的静态数据，以访问您希望导入的Excel中的数据不应超出此范围。




• Command0 替换为用于启动所有此代码的按钮名称。

  Sub ImportDataFromRange（）
 
'删除任何以前的访问表，否则下一行将添加一个额外的表
 DoCmd.DeleteObject acTableExcelStaticRangeData
 
'使用静态范围从Excel导入数据
 DoCmd.TransferSpreadsheet acLink，acSpreadsheetTypeExcel9， excel，C： Private Sub Command0_Click（）
 
 ImportDataFromRange 
 
 End Sub 
  

NB ：

• ac这两种方法中的SpreadsheetTypeExcel9 位是指要导入的Excel文件的版本;在我的例子中，我导入Excel 2000格式;您可能会导入不同版本的Excel，请参阅这个，看看你需要在代码中引用哪个版本？




• >我的示例将数据导入Access作为源数据的活动链接。如果您有大量数据，则可能会更好地将数据导入和存储到Access中，因为链接可能会导致一些性能问题。如果是这种情况，交换 acLink acImport




• 如果您尚未在Access中手动创建一个空白表（与代码中引用的表名相同），则需要执行此操作或注释掉 DoCmd .DeleteObject acTable，yourAccessTable第一次运行代码，然后再次恢复该部分。

可能有更多的可以做到这一点 - 即如果您的数据源具有不同列数，则调整CountA函数以考虑动态列数。

另外一个非常感谢@ david-zemens，@ gord-thompson以及其他StackoverFlow用户来帮助我，这对我来说非常有用，希望对他人有帮助。

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.

解决方案

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.

这篇关于导入从Excel到MS-Access的动态和静态范围，不能从单元格A1开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！