Open Access Query in Excel Format

[arkres]

Hi,

I apologize if I've posted this twice, but I don't think the first one processed. I am a newbie to vba. I have a button on a form. I just need the proper code to have the button open a query in Excel format. If someone can help, I'd really appreciate it. Thanks so much.

 

[pr2-eugin]

arkres, do you wish to "Export" the Query into Excel on the click of the button??

 

[DavidAtWork]

Not quite clear what you want to do, do you mean export a query in xls format and then open the resultant Excel file to view.
David

 

[arkres]

Yes, please. Export and then Open.

 

[DavidAtWork]

If you copy the Sub name DisplayExcel into the click event of your button, edit the hard coded query name and export destination, this should work fine.
NB the True argument relates to the field names as column headers
The Sub will check to see if Excel is running, if it's not running then it starts Excel

Sub DisplayExcel()
Dim exFile As String
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryName", "C:\someFolder\qryName.xls", True

exFile = "C:\someFolder\qryName.xls"

Dim xlApp As Excel.Application
Dim ExcelRunning As Boolean
ExcelRunning = IsExcelRunning()
If ExcelRunning Then
Set xlApp = GetObject(, "Excel.Application")
Else
Set xlApp = CreateObject("Excel.Application")
End If
xlApp.Visible = True

xlApp.Workbooks.Open exFile

xlApp.Parent.ActiveWindow.Visible = True
End Sub

Function IsExcelRunning() As Boolean
Dim xlApp As Excel.Application
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
IsExcelRunning = (Err.Number = 0)
Set xlApp = Nothing
Err.Clear
End Function

David

 

[arkres]

Thanks, David. I'm getting the message that my DB can't find the object DisplayExcel. Any ideas?

 

[pr2-eugin]

So do you have the LOC that David gave under DisplayExcel inside the button Click?? Like..

Private Sub [COLOR=Blue]yourButtonName[/COLOR]_Click()
    Dim exFile As String
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryName", "C:\someFolder\qryName.xls", True

    exFile = "C:\someFolder\qryName.xls"

    Dim xlApp As Excel.Application
    Dim ExcelRunning As Boolean
    ExcelRunning = IsExcelRunning()
    If ExcelRunning Then
        Set xlApp = GetObject(, "Excel.Application")
    Else
        Set xlApp = CreateObject("Excel.Application")
    End If
    xlApp.Visible = True

    xlApp.Workbooks.Open exFile

    xlApp.Parent.ActiveWindow.Visible = True
End Sub

Where yourButtonName being the name of your button; and the following in a module?

Function IsExcelRunning() As Boolean
    Dim xlApp As Excel.Application
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    IsExcelRunning = (Err.Number = 0)
    Set xlApp = Nothing
    Err.Clear
End Function

 

[arkres]

Thanks to both of you. That did the trick!!