vba 将访问查询导出到 Excel

Exporting Access Query to Excel

提问by MalsiaPro

I've got an Access 2007 database on which I have created around 15 SQL queries to process specific data, I have created a main frame navigation menu using menus in Access, I now need to extract all th queries to Excel using VBA code, I have managed to do this with the code below by creating a button and specifying this code to it.

我有一个 Access 2007 数据库，我在其中创建了大约 15 个 SQL 查询来处理特定数据，我使用 Access 中的菜单创建了一个主框架导航菜单，我现在需要使用 VBA 代码将所有查询提取到 Excel，我通过创建一个按钮并为其指定此代码，已设法使用以下代码完成此操作。

Private Sub query1_Click()
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "Total Users and Sessions", _
"C:\UsersandSessions.xls", , "Total Users & Sessions"
End Sub

Now my problem at the moment is that fine the query is exported to Excel, but it is done so without any formatting applied at all, I would like to add some formatting at least to the headers and maybe a title inside the spreadsheet, and one thing I dont really like is that all records are being started from the first cell. Also I would prefer that if I hit that button again in Access and the Excel spreadsheet has already exists with that query output then when clicked again it will write again to a the next available sheet.

现在我的问题是，很好地将查询导出到 Excel，但它是在没有应用任何格式的情况下完成的，我想至少在标题中添加一些格式，也许在电子表格中添加一个标题，还有一个我不太喜欢的是所有记录都是从第一个单元格开始的。此外，我希望如果我在 Access 中再次点击该按钮并且 Excel 电子表格已经存在该查询输出，那么当再次单击时，它将再次写入下一个可用的工作表。

Any suggestions or ideas a very welcome.

非常欢迎任何建议或想法。

回答by BIBD

The short story, is you can't. You might be able to do some scripting on the Excel side to format the resulting file. If you want something pretty, you probably want to create a report.

简短的故事，是你不能。您也许可以在 Excel 端编写一些脚本来格式化生成的文件。如果你想要一些漂亮的东西，你可能想要创建一个报告。

You could, instead mount the excel sheet as a table, and then on a separated sheet in the excel file, reference the first sheet, and format the second sheet for viewing.

您可以将 Excel 工作表安装为表格，然后在 Excel 文件中的单独工作表上，引用第一张工作表，并设置第二张工作表的格式以供查看。

回答by Knox

if you use DoCmd.TransferSpreadsheet and create an original and then edit it so that the formatting is correct, you can then run DoCmd.TransferSpreadsheet again and it will update the file with the values but keep the formatting.

如果您使用 DoCmd.TransferSpreadsheet 并创建一个原始文件，然后对其进行编辑以使格式正确，则您可以再次运行 DoCmd.TransferSpreadsheet，它将使用值更新文件但保留格式。

However, if a human then changes the file by adding new tabs, or adding calculations, etc, then the DoCmd.TransferSpreadsheet will no longer work and will fail with an ugly error message. So what we do in our enviroment is DoCmd.TransferSpreadsheet to an original file with formatting, and follow that up in the VBA by copying the file to the users desktop, and then opening that copy so the user doesn't mess up the original source excel file.

但是，如果随后有人通过添加新选项卡或添加计算等方式更改文件，则 DoCmd.TransferSpreadsheet 将不再工作，并且会失败并显示丑陋的错误消息。所以我们在我们的环境中做的是将 DoCmd.TransferSpreadsheet 转换为带有格式的原始文件，然后在 VBA 中通过将文件复制到用户桌面来跟进，然后打开该副本，这样用户就不会弄乱原始源excel文件。

This approach is a minimum code, clean, and easy to maintain solution. But it does require a extra "source" or original file to be hanging around. Works in Access 2007.

这种方法是代码最少、干净且易于维护的解决方案。但它确实需要一个额外的“源”或原始文件。适用于 Access 2007。

You also would like the results to end up on a new tab. Unfortunately, I think it will take some excel automation to do that. The VBA inside Acccess can call a function inside the VBA in Excel. That VBA could then copy the tabs as needed.

您还希望结果最终出现在新选项卡上。不幸的是，我认为需要一些 excel 自动化才能做到这一点。Acccess里面的VBA可以在Excel中调用VBA里面的函数。然后，该 VBA 可以根据需要复制选项卡。

回答by The Gambill

My idea would be a hybrid of Excel automation from Access and creating a template in Excel as well that would have a data table linked to your query. To start create your data table in Excel. You can start three rows down and two columns to the right if you want or wherever. Go to your data tab and click access, find your db, choose your query you want to link to, choose table as the radio button but click properties next instead of ok, uncheck the enable background refresh, this part is critical ... under the definition tab in the connection string you will see a part that says Mode=Share Deny Write change that to Mode=Read, this will make sure that the query refreshes without errors from an MS Access VBA while the db is open and will keep your users from writing back to the db in case your query is a writeable query. Once you set that up you can adjust the table formatting however you choose from the table design tab and it will keep that formatting.

我的想法是将来自 Access 的 Excel 自动化与在 Excel 中创建模板相结合，该模板将链接到您的查询的数据表。开始在 Excel 中创建数据表。您可以根据需要或在任何地方开始向下三行和向右两列。转到您的数据选项卡并单击访问，找到您的数据库，选择您要链接到的查询，选择表作为单选按钮，但单击属性下一个而不是确定，取消选中启用后台刷新，这部分很关键...下在连接字符串中的定义选项卡中，您将看到一个部分，上面写着 Mode=Share Deny Write 将其更改为 Mode=Read，这将确保查询在数据库打开时从 MS Access VBA 刷新而没有错误，并将保持您的如果您的查询是可写查询，则用户不写回数据库。

For the purposes of this we are going to assume you started the table in cell B4 ,and your named the worksheet CurrentDay, for purpose of the following VBA example be sure to replace that reference with your actual placement.

为此，我们将假设您在单元格 B4 中启动表格，并将工作表命名为 CurrentDay，出于以下 VBA 示例的目的，请务必将该引用替换为您的实际位置。

Next go back to Access and write your VBA first ensure that in your VBA window you have the reference to Microsoft Excel 12.0 Object Library is selected by going to Tools > References and selecting it from the alphabetical listing. Create your sub as follows:

接下来返回到 Access 并编写您的 VBA，首先确保在您的 VBA 窗口中您有对 Microsoft Excel 12.0 对象库的引用，方法是转到“工具”>“引用”并从按字母顺序排列的列表中选择它。按如下方式创建您的子程序：

Sub query1_click()
Dim xl as Excel.Application
Dim wbk as Excel.Workbook
Dim wks as Excel.Worksheet
Dim RC as Integer
Dim CC as Integer 
Set xl = New Excel.Application
Set wbk = xl.wbk.Open "X:\Filelocation\FileName.xlsx" 'name and path you saved the file you previously created
xl.Visible = True 
'The above is not necessary but you may want to see your process work the first few times and it will be easier than going to task manager to end Excel if something fails. 
RC = xl.Application.CountA(xl.wbk.Worksheets("CurrentDay").Range("B:B")) + 3 'This will count the rows of data in your table including your header so you can copy the data to another tab dynamically as the size of your table expands and shrinks we add 3 to it because we started at row 4 and we need the location of the last row of the record set.

CC = xl.Application.CountA(xl.wbk.Worksheets("CurrentDay").Range("4:4")) + 1 'This counts the header row and adds one space because we will use this as a location holder for our copy / paste function

Set wks = xl.wbk.Worksheets.Add
wks.Name = format(date(),"MM_dd_yy") 'this will name the tab with today's date... you can eliminate this step if you just want the sheets to be the generic Sheet1, Sheet2, etc. 


   With xl.wbk
          .Worksheets("CurrentDay").Range(Cells(4,2),Cells(RC,CC)).Copy
          .wks.PasteSpecial xlPasteValues 'This pastes the values so that the table links do not paste otherwise every tab would just refresh everyday.
          .wks.PasteSpecial xlPasteFormats 'This gets your formatting.
          .RefreshAll 'This will refresh your table
    Wend

    With xl
        .Save 
        .Close False
        .Quit
    Wend
Set xl = Nothing
Set wbk = Nothing
Set wks = Nothing
End Sub

That should get you to have your data to not start on A1 of your sheets, save your old data each time, and automate the steps from access.

这应该让您的数据不在工作表的 A1 上开始，每次都保存旧数据，并自动执行访问步骤。