我正在Access 2016中编写一个模块,该模块打开Excel电子表格,将数据复制到Excel表中,然后运行存储在Excel文件中的子文件,该文件显示一个消息框,然后生成一个图形(至少应该是这样的!)。
这是访问代码:
Option Compare Database
Option Explicit
Sub QueryExportMod()
'variable initilizations and definitions ---------------------------------------------------------------------------------------
Dim db As DAO.Database
Set db = CurrentDb
Dim totalFindingsQuery As String
Dim breakdownFindingsQuery As String
totalFindingsQuery = 'SQL text
breakdownFindingsQuery = 'SQL text
Dim tempQ1 As DAO.QueryDef
Dim tempQ2 As DAO.QueryDef
Set tempQ1 = db.CreateQueryDef("tempQ1", totalFindingsQuery)
Set tempQ2 = db.CreateQueryDef("tempQ2", breakdownFindingsQuery)
Dim rs1 As Recordset
Dim rs2 As Recordset
Set rs1 = db.OpenRecordset("tempQ1")
Set rs2 = db.OpenRecordset("tempQ2")
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open(CurrentProject.Path & "\ExportExcelTest.xlsm")
Set ws = wb.Worksheets("Sheet1")
Dim table As ListObject
Set table = ws.ListObjects("Table1")
'Modifying files and data ------------------------------------------------------------------------------------------------------
ws.ListObjects("Table1").DataBodyRange.Rows.Delete
ws.Range("A2") = "Total Findings"
ws.Range("B2").CopyFromRecordset rs1
ws.Range("A3").CopyFromRecordset rs2
'Call autoGraph, the Excel sub
xlApp.Run "autoGraph()"
xlApp.Visible = True
Set xlApp = Nothing
Set wb = Nothing
Set ws = Nothing
DoCmd.DeleteObject acQuery, "tempQ1"
DoCmd.DeleteObject acQuery, "tempQ2"
End Sub
'to be able to run the sub from a macro
Function KGQueryExportCall()
Call QueryExportMod
End Function
Sub autoGraph()
'AppActivate Application.Caption
MsgBox " <message> "
Dim tb1Range As Range
Set tb1Range = ActiveSheet.Range("Table1")
Range("Table1").Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Table1")
End Sub
当我运行Access sub时
QueryExportMod
,消息框被生成(我的另一个问题是,它总是在其他窗口后面。有人知道如何将其强制到当前窗口,即顶部的窗口吗?我试过了
AppActivate Application.Caption
我在Excel子文件中添加了两行调试代码,如下所示:
DeBug.Print("1")
MsgBox " <message> "
DeBug.Print("2")
最终得到了
1
2
1
2
在即时窗口中
我只得到
1
2
