I'm running a query from Access and exporting the results to Excel. Works just like I expect it to. What I would like to do next is manipulate the Excel file (autofit columns, format fields, etc.). I've manipulated Excel worksheets countless times from Excel. However this is the first time, doing it from Access. Below is the code I'm using. The query and export run great.
My issue is I'm unable to select / activate / manipulate Excel. Currently, the only Excel file open is the query result. However, I'm sure my user's will have multiple Excel files open, so I'll need to program for that situation as well.
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
Any help or suggestions would be greatly appreciated. Thanks in advance for your assistance.........
CodePudding user response:
You can start with something like this. Have fun!
With 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
With Late Binding:
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
End Sub