访问查询到 excel 2010 以通过 vba 创建图形
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9974250/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access query to excel 2010 to create a graph via vba
提问by Sam 'Evilsam' Spiller
After an extensive search I have been unable to find any information on this that I could understand. there are numerous examples, but these are all for access 2003, and these do not work in access 2010.
经过广泛的搜索，我无法找到任何我能理解的信息。有很多例子，但这些都是针对 access 2003 的，这些在 access 2010 中不起作用。
I need to run a vba code that will export the results of a query (QryTotalSale) to excel 2010 and automatically create a bar chart of the data and show this over the database that is running.
我需要运行一个 vba 代码，它将查询 (QryTotalSale) 的结果导出到 excel 2010 并自动创建数据的条形图并在正在运行的数据库上显示它。
If anyone could give me some advise then I would greatly appreciate it, or even a link to a valid resource that will work in 2010.
如果有人能给我一些建议，那么我将不胜感激，或者甚至可以提供指向 2010 年有效资源的链接。
So far I can get excel to open, and display the results of the query in question.
到目前为止，我可以打开 excel，并显示相关查询的结果。
To make it more difficult I really need the query to open a specific excel file, which will be on a folder on the desktop and every time the button is pressed to run the VBA, a new page should be added to the excel workbook, and the new graph be shown, and saved into the spreadsheet, so that at a later date the entire excel file can be viewed.
为了使它变得更加困难，我真的需要查询来打开一个特定的 excel 文件，该文件将位于桌面上的一个文件夹中，每次按下按钮运行 VBA 时，都应将一个新页面添加到 excel 工作簿中，并且显示新图形，并将其保存到电子表格中，以便日后可以查看整个 excel 文件。
I have the below code, but it does not work. the bit about ranges would work in excel, but access does not seem to recognise range ( which does not really suprise me as it does not really work with ranges to my knowledge.)
我有下面的代码，但它不起作用。关于范围的一点可以在 excel 中工作，但访问似乎无法识别范围（这并没有真正让我感到惊讶，因为据我所知，它并不能真正适用于范围。）
My second thought was to have the first two doCmd's run, then have the next bit be forced to auto run in the excel file.
我的第二个想法是让前两个 doCmd 运行，然后强制下一个在 excel 文件中自动运行。
Private Sub SalesImage_Click()
DoCmd.OpenQuery "QryTotalSale"
DoCmd.RunCommand acCmdOutputToExcel
Dim myRange as range
Set myRange = B2 * C30
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=myRange, _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
End Sub
I found a adodb code type thing for the 2003 versions of access and excel, but could not get this to work. half of the options no longer seem to be recognised by access...
我为 2003 版本的 access 和 excel 找到了一个 adodb 代码类型的东西，但无法让它工作。一半的选项似乎不再被访问识别......
I am a long way off and would really appreciate any help.
我还有很长的路要走，非常感谢任何帮助。
Thanks
谢谢
Sam
山姆
回答by Fionnuala
Here are some notes. I have used late binding, so you do not need to set a reference to the Excel library, however, I have included notes on the types.
这里有一些注意事项。我使用了后期绑定，因此您不需要设置对 Excel 库的引用，但是，我已经包含了有关类型的注释。
Dim xl As Object ''Excel.Application
Dim wb As Object ''Excel.Workbook
Dim ws As Object ''Excel.Worksheet
Dim ch As Object ''Excel.Chart
Dim myRange As Object
Set xl = CreateObject("Excel.Application")
sExcelWB = "z:\docs\testchart.xls"
''This will overwrite any previous run of this query to this workbook
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", _
sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB)
''Sheets are named with the Access query name
Set ws = wb.Sheets("Query1")
Set ch = xl.Charts.Add
ch.ChartType = xlColumnClustered
xl.Visible = True
xl.UserControl = True
''Still not saved
