我正在從 Access 運行查詢并將結果匯出到 Excel。就像我期望的那樣作業。我接下來要做的是操作 Excel 檔案（自動調整列、格式欄位等）。我無數次從 Excel 操作 Excel 作業表。但是，這是第一次從 Access 中執行此操作。下面是我正在使用的代碼。查詢和匯出運行良好。
我的問題是我無法選擇/激活/操作 Excel。目前，唯一打開的 Excel 檔案是查詢結果。但是，我確信我的用戶會打開多個 Excel 檔案，因此我也需要針對這種情況進行編程。
DoCmd.OutputTo acOutputQuery, "Query1", acFormatXLSX, , True
Set xlapp = GetObject(, "Excel.Application")
MyReport = ""
MyReport = xlapp.workbooks(w).Name
xlapp.Workbook(MyReport).Activate
xlapp.Workbook(MyReport).worksheets(1).Activate
Range(xlapp.Workbook(MyReport).worksheets(1).cells(1, 1), xlapp.Workbook(MyReport).worksheets(1).cells(1, 1)).Select
任何幫助或建議將不勝感激。提前感謝你的幫助.........
uj5u.com熱心網友回復：
你可以從這樣的事情開始。玩得開心！
使用EarlyBinding：
Sub Access_ControlExcelWorkbook_EarlyBinding()
On Error GoTo errHandler
Dim appExcel As New Excel.Application
Dim xWb As Excel.Workbook
Dim xWs As Excel.Worksheet
Dim xRng As Excel.Range
Dim wbPath As String: wbPath = "YourWorkbookPath"
' Exit if workbook don't exist
If Len(Dir(wbPath)) = 0 Then Exit Sub
' Open workbook
Set xWb = appExcel.Workbooks.Open(wbPath)
' Show Excel
appExcel.Visible = True
' Sheet to control
Set xWs = xWb.Worksheets("Sheet1")
' Range to control
Set xRng = xWs.Range("A10")
' Write value in range
xRng.Value = "Control from Access"
' Auto fit columns
xWs.Cells.EntireColumn.AutoFit
' Save workbook
xWb.Save
exitRoutine:
' Close workbook
xWb.Close False
' Close Excel
appExcel.Quit
Exit Sub
errHandler:
Debug.Print Err.Description
Resume exitRoutine
End Sub
隨著后期系結：
Public Const xlCenter = -4108
Sub Access_ControlExcelWorkbook_LateBinding()
On Error GoTo errHandler
Dim appExcel As Object
Dim xWb As Object
Dim xWs As Object
Dim xRng As Object
Dim wbPath As String: wbPath = "YourWorkbookPath"
' Exit if workbook don't exist
If Len(Dir(wbPath)) = 0 Then Exit Sub
' Create an instance od Excel
Set appExcel = CreateObject("Excel.Application")
' Copy the rest of the code from early Binding
' Center column G
xWs.Columns("G:G").HorizontalAlignment = xlCenter
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/359966.html