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

问题描述

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

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?

链接到数据从单元格 A1 开始的 Excel 工作表很容易，但是 Excel 电子表格中的数据从工作表中的其他位置开始，我不知道如何让 Access 精确定位它，尤其是如果非 A1 范围是动态的.

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 似乎没有选择命名/动态范围.

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

真实世界场景:

我有一个 Excel 电子表格文件，我们称之为ExcelFile1.xls"，它是从外部提供给我的(因此我无法真正更改其格式).

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 个工作表/选项卡，我们称之为动态"，有一个数据范围，我想在 Access 中用作表格，但它的列标题从第 14 行开始，一直延伸到列EL.我希望 Access 做的是将这个数据范围作为一个表格.此外，ExcelFile1.xls"也将定期更新，即新版本的ExcelFile.xls"文件将可用，但在第 14 行的列标题下方有更多数据，所以理想情况下我会例如，每当我覆盖以前版本的ExcelFile1.xls"时，都可以获取此范围内的新数据.

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".

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

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

这有一个工作表/标签，我们称之为静态"，它同样有一个我想要作为 Access 中表格的数据范围，而且还会有更新版本的ExcelFile2.xls"，它将覆盖我希望 Access 能够使用的以前的版本.这个范围是 A14:O19 并且永远是这个范围(即静态范围).

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).

总而言之:我想将 Access 链接到来自 2 个各自 Excel 文件的 2 个数据范围，以生成 Access 中可用的 2 个单独表格.1 个源数据范围是动态的，另一个是静态的.如果可能，我希望 Access 能够通过使用完全相同的文件名和路径的新版本覆盖源 Excel 文件来获取新数据.

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.

推荐答案

好的，从这里关于这个问题的有用指示，以及我在别处提出的其他一些问题，我想我有一个相当简单的解决方案，适合任何想要的人从 Excel 电子表格中提取数据作为动态范围或静态范围，其中 Excel 中的数据不在单元格 A1 中开始.

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.

动态范围:

请注意，此动态范围示例假定 Excel 中的单元格范围始终从最左上角的相同位置开始，并且列数始终相同 - 即唯一动态的是底部单元格范围的行号.

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:UsersMattDesktopExcelFile1.xls 替换为 Excel 文件的完整路径

• C:UsersMattDesktopExcelFile1.xls Replace with the full path of your Excel file

Dynamic 替换为 Excel 文件中包含的工作表的名称

Dynamic Replace with the name of the sheet contained within your Excel file

A14:A2000 替换为您要测试的范围以查看有多少非空单元格.这个范围应该: 从列标题所在的数据行开始；覆盖您要导入的数据中永远不会有空单元格条目的列；覆盖的范围足够大，以至于它始终会超过 Excel 电子表格中包含实际数据的行数.

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 替换为您想在 Access 中调用的表，该表将包含从您的 Excel 范围中提取的数据.

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 替换为您的工作表名称、最顶部/最左侧的单元格引用以及 Excel 区域最右侧的列字母.不要包含最底部/最右侧的行号，因为这是动态的，因此将其分配给 numberofrows 并稍后将其连接到此范围的末尾.

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 ... 将 13 替换为列标题开始位置上方的行数.例如.如果您的列标题从第 4 行开始，则此数字必须为 3.

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 替换为用于启动所有这些代码的按钮的名称.

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:UsersMattDesktopExcelFile1.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:UsersMattDesktopExcelFile1.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

静态范围:

这要简单得多，因为无需打开 Excel 工作簿即可对数据应用 CountA 函数.

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:UsersMattDesktopExcelFile2.xls 替换为 Excel 文件的完整路径

• C:UsersMattDesktopExcelFile2.xls Replace with the full path of your Excel file

ExcelStaticRangeData 替换为您想在 Access 中调用的表，该表将包含从您的 Excel 范围中提取的数据.

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 替换为您的工作表名称，以及您要导入的 Excel 中单元格的完整范围地址.由于此方法描述了如何从 Excel 中获取静态数据范围到 Access，因此您要导入的 Excel 中的数据不应超出此范围.

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 替换为用于启动所有这些代码的按钮的名称.

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:UsersMattDesktopExcelFile2.xls", True, "Static!A14:EL20"

End Sub    

Private Sub Command0_Click()

ImportDataFromRange

End Sub

注意:

• 这两种方法中的 acSpreadsheetTypeExcel9 位指的是您要导入的 Excel 文件的版本；在我的示例中，我正在导入 Excel 2000 格式；您可能要导入不同版本的 Excel，因此请参阅 this 看看你需要在代码中引用哪个版本；.xlsx 文件未列出，但应该是 acSpreadsheetTypeExcel12Xml.

• 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.

我的示例将数据作为指向源数据的活动链接引入 Access.如果您有大量数据，您可能会发现实际将数据导入并存储到 Access 中会更好，因为链接可能会导致一些性能问题.如果是这种情况，请将 acLink 换成 acImport.

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.

如果您还没有在 Access 中手动创建一个空白表(在您的代码中引用了相同的表名)，那么您要么需要这样做，要么注释掉 DoCmd.DeleteObject acTable, "yourAccessTable" 第一次运行代码，然后恢复这部分.

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.

可能还有更多事情可以做 - 即，如果您的数据源具有不同数量的列，则调整 CountA 函数以考虑动态列数.

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.

再次非常感谢@david-zemens、@gord-thompson 以及其他 StackoverFlow 用户帮助我实现这一目标 - 这对我和其他人都非常有用.

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屋！