Opening Excel Spreadsheet from Access Macro

[herbc0704]

I created a macro that runs a query and transfers the information to an excel file. How can I have this macro open the excel file once the transfer has ben completed.

 

[andiemac]

This is a pretty basic example, but you can fill it out as you need.

Function OpenExcelFromAccess()
Dim MyXL As Object

Set MyXL = CreateObject("Excel.Application")
MyXL.Workbooks.Open "C:\SomeFolder\SomeFile.xls"

End Function

 

[herbc0704]

Andiemac,

Thanks. However when I run this. The excel file does not open. If I manually open the file it says its in use. What am I missing. I am using Excel 2007 and Access 2007. The excel file to be opened is in .xlsx format.

 

[andiemac]

You will need to change the path and file name to exactly where and what to open. The C:\SomeFolder\SomeFile.xls" was a sample to go by. Since you have in your Access a function that transfers your information to an excel file, use that same path and name, and end it with xlsx since that the file type.

 

[Jon von der Heyden]

I believe the application instance you have created with CreateObject is hidden.

Function OpenExcelFromAccess()
    Dim MyXL As Object

    Set MyXL = CreateObject("Excel.Application")
    With MyXL
        [B].Application.Visible = True[/B]
        .Workbooks.Open "C:\SomeFolder\SomeFile.xls"
    End With
End Function

 

[Jon von der Heyden]

Another way could be to use Shell function:

Public Function(byVal strFile as String)
    VBA.Shell "EXCEL.EXE " & Chr$(34) & strFile & Chr$(34), vbNormalFocus
End Function