CODEWITHSUNDEEP
excelms-accessvbainstance
Fred
Fred

Reputation: 121

Reference to Current Excel file/Sheet

I made a wizard button on a Access form where you can transfer the data of a Query to a new excel file and then the new file will automatically open. Is there a way to make a reference to this new file or Sheet, because i want to put something in this new file.

Upvotes: 1

Views: 519

Answers (1)

RubberDuck
RubberDuck

Reputation: 12768

This will get an open instance of Excel if it exists or create one if there isn't one.

Public Function GetExcelApp() As Excel.Application
' Returns open excel instance.
'   If it doesn't exist, creates one to return
On Error GoTo ErrHandler
Const PROC_NAME As String = "GetExcelApp"
Const ERR_APP_NOTRUNNING As Long = 429

    Set GetExcelApp = GetObject(, "Excel.Application")

CleanExit:
    Exit Function
ErrHandler:
    If Err.Number = ERR_APP_NOTRUNNING Then
        Set GetExcelApp = CreateObject("Excel.Application")
    Else
        Err.Raise Err.Number, GetErrorSource(PROC_NAME), Err.Description & vbNewLine & "Unable to get instance of Excel.", Err.HelpFile, Err.HelpContext
    End If
End Function

I keep this code in an XLHelper class and use it like this.

Dim helper As New XLHelper
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set xl = helper.GetExcelApp
Set wb = xl.Workbooks.Add
Set ws = wb.Worksheets.Add

To get the reference of the new sheet/workbook, you just set it equal to the results of the collection's Add method.

This works because Workbook.Add and Worksheets.Add return the object they create.

Upvotes: 1

Related Questions