Manipulating Excel sheet as Access query results

Question

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.........

Answer 1

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:

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