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

@CDP1802..........w is an integer and is the number of workbooks the user has open. Thanks......... — Shaves
– Shaves, Commented Nov 1, 2021 at 22:20
xlapp.Workbook(MyReport) should be Workbooks with s. Try xlapp.Workbooks.(w).Sheets(1).Columns("A:Z").Autofit — CDP1802
– CDP1802, Commented Nov 1, 2021 at 23:10
@CDP1802.............I'll give that a try later today. Thanks for the suggestion.. — Shaves
– Shaves, Commented Nov 2, 2021 at 11:47

Elio Fernandes · Accepted Answer · 2021-11-17 09:15:52Z

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

This is an early bound example. I always use late bound references because the tools I make are for other people to use. If they don't have their references in place, hint: they don't, then they will get runtime errors. You also have the option to get an existing instance or make a new one depending on if you use GetObject or CreateObject
I usually use Late Binding as you do! I usually start with early binding because the intellisense is very helpful., and then convert to late binding because you don't have intellisense in this mode. Because you said that it's your first time from Access to Excel, I did it in early binding. I am going to update the post with late binding!
@HackSlash...........Thanks for the info. I appreciate the help........
@ElioFernandes..........Thank you very much. I appreciate the info and will try it first thing tomorrow morning (as it's the end of the workday for me). I'll let you know it goes......
@Shaves! If it worked and was useful don't forget to check it!

Collectives™ on Stack Overflow

Manipulating Excel sheet as Access query results

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related