Help exporting to multiple sheets with VB (1 Viewer)

joshandsony · Feb 22, 2009

Hello: I have created this code, but I need to change it to multiple sheets and don't know how. Can someone please offer a little assistance?

'------------------------------------------------------------
' Export_To_Excel
'
'------------------------------------------------------------
Function Export_To_Excel()
On Error GoTo Export_To_Excel_Err
DoCmd.OpenQuery "Non-Confirmed Export Query", acViewNormal, acEdit
DoCmd.OutputTo acOutputQuery, "Non-Confirmed Export Query", "ExcelWorkbook(*.xlsx)", "", False, "", 0, acExportQualityPrint
DoCmd.Close acQuery, "Non-Confirmed Export Query"
DoCmd.OpenQuery "Confirmed Export Query", acViewNormal, acEdit
DoCmd.OutputTo acOutputQuery, "Confirmed Export Query", "ExcelWorkbook(*.xlsx)", "", False, "", 0, acExportQualityPrint
DoCmd.Close acQuery, "Confirmed Export Query"
DoCmd.OpenQuery "Non-Confirmed Export Query", acViewPivotTable, acEdit
DoCmd.OutputTo acOutputQuery, "Non-Confirmed Export Query Pivot", "ExcelWorkbook(*.xlsx)", "", False, "", 0, acExportQualityPrint
DoCmd.Close acQuery, "Non-Confirmed Export Query"
DoCmd.OpenQuery "Confirmed Export Query", acViewPivotTable, acEdit
DoCmd.OutputTo acOutputQuery, "Confirmed Export Query Pivot", "ExcelWorkbook(*.xlsx)", "", False, "", 0, acExportQualityPrint
DoCmd.Close acQuery, "Confirmed Export Query"

Export_To_Excel_Exit:
Exit Function
Export_To_Excel_Err:
MsgBox Error$
Resume Export_To_Excel_Exit
End Function

ByteMyzer · Feb 22, 2009

The following code should do what you are looking for:

Code:

[COLOR="Navy"]Public Sub[/COLOR] MultiQueryExportToExcel( _
    [COLOR="navy"]ByVal[/COLOR] sFilename [COLOR="navy"]As String[/COLOR], _
    [COLOR="navy"]ParamArray[/COLOR] arrQueryName() [COLOR="navy"]As Variant[/COLOR])

[COLOR="navy"]Dim[/COLOR] objExcelApp [COLOR="navy"]As Object
Dim[/COLOR] objExcelBook [COLOR="navy"]As Object
Dim[/COLOR] objExcelSheet [COLOR="navy"]As Object
Dim[/COLOR] rs [COLOR="navy"]As[/COLOR] ADODB.Recordset
[COLOR="navy"]Dim[/COLOR] vQueryName [COLOR="navy"]As Variant
Dim[/COLOR] X [COLOR="navy"]As Long

If UBound[/COLOR](arrQueryName) = -1 [COLOR="navy"]Then Exit Sub

Set[/COLOR] objExcelApp = CreateObject("Excel.Application")

[COLOR="navy"]Set[/COLOR] objExcelBook = objExcelApp.Workbooks.Add

[COLOR="navy"]With[/COLOR] objExcelBook
    [COLOR="navy"]For Each[/COLOR] vQueryName [COLOR="navy"]In[/COLOR] arrQueryName
        [COLOR="navy"]Set[/COLOR] rs = [COLOR="navy"]New[/COLOR] ADODB.Recordset
        rs.Open vQueryName, CurrentProject.Connection
        [COLOR="navy"]Set[/COLOR] objExcelSheet = .Worksheets.Add(, _
            .Worksheets(.Worksheets.Count))
        [COLOR="navy"]For[/COLOR] X = 0 [COLOR="navy"]To[/COLOR] rs.Fields.Count - 1
            objExcelSheet.Cells(1, X + 1) = rs.Fields(X).Name
        [COLOR="navy"]Next[/COLOR] X
        objExcelSheet.Range("A2").CopyFromRecordset rs
        objExcelSheet.Name = vQueryName
        rs.Close
        [COLOR="navy"]Set[/COLOR] rs = [COLOR="navy"]Nothing
    Next[/COLOR] vQueryName
    [COLOR="navy"]Set[/COLOR] objExcelSheet = [COLOR="navy"]Nothing

    Do While[/COLOR] .Sheets.Count > [COLOR="navy"]UBound[/COLOR](arrQueryName) + 1
        .Sheets(1).Delete
    [COLOR="navy"]Loop[/COLOR]

    .SaveAs sFilename
[COLOR="navy"]End With

Set[/COLOR] objExcelBook = [COLOR="navy"]Nothing[/COLOR]

objExcelApp.Quit
[COLOR="navy"]Set[/COLOR] objExcelApp = [COLOR="navy"]Nothing

End Sub[/COLOR]

Usage:
MultiQueryExportToExcel MyFileName, MyFirstQuery, MySecondQuery, MyThirdQuery, ...

Example:
MultiQueryExportToExcel "C:\MyFolder\MyExcelExport.xls", "Non-Confirmed Export Query", "Confirmed Export Query"

joshandsony · Feb 23, 2009

Thank you very much for your assistance.

joshandsony · Mar 3, 2009

Hi Byte,

I finally got my laptop back and I was working on this code.

Public Sub MultiQueryExportToExcel ("C:\Documents and Settings\Windows User\Desktop\Expedite Access.xlsx"
ByVal sFilename As String, _
ParamArray arrQueryName() As Variant)

This portion of the code is giving me problems. It keeps saying that I have a complile error: Expedited: Identifier

It only does it when I add the file name to the code.

ByteMyzer · Mar 3, 2009

You are not supposed to add the file to the code. You are supposed to copy and save the code, exactly as I supplied it, and then call the code with the file name as a parameter, like the following example:

MultiQueryExportToExcel "C:\Documents and Settings\Windows User\Desktop\Expedite Access.xlsx", "Non-Confirmed Export Query", "Confirmed Export Query"

joshandsony · Mar 3, 2009

WEWT!!!!

That worked!!

Thank you soooo much for you help on this.

I went to the library and got a book on vba so I can learn about what it is doing.

Thanks again for your help.

ByteMyzer · Mar 3, 2009

You're very welcome, I'm glad it worked for you

hokiewalrus · Jun 11, 2009

Bringing this topic back from the dead.

I tried using the above function as stated however I'm getting an error that I don't get when I just export a single Query to a single sheet.

On the line

Code:

rs.Open vQueryName, CurrentProject.Connection

I get error -2147217900
"Invalid SQL Statement; expected 'DELETE', 'INSERT'...etc"

The query runs fine on its own, and I can even export it via:

Code:

DoCmd.OutputTo acQuery, "ExcelLaborQuery", "MicrosoftExcel(*.xls)", "\\Plumb012\Public Documents\ProfitLoss Exports\" & TextTicketNumber & ".xls", False, ""

I'm not an ADO guy, so I'm a little over my head here. Any help would be great.

Also, the query in question is:

Code:

SELECT tJobsLabor.TicketNumber, tJobsLabor.Technician, tJobsLabor.UsedDate, tJobsLabor.Hours, tJobsLabor.Rate, tJobsLabor.RateCode, tJobsLabor.TotalCost, tJobsLabor.MarkUp
FROM tJobsLabor
WHERE (((tJobsLabor.ControlNumber)=[Forms]![ProfitLossForm]![ControlNumber]));

ByteMyzer · Jun 11, 2009

The ADODB.Recordset Open method will not work on a query with a Form-Control Parameter reference (ex. [Forms]![ProfitLossForm]![ControlNumber]).

joshandsony · Jun 12, 2009

Byte....Will you help me again? After using the above code, I didn't get any errors while putting it in vb, but I am now trying to run the code using a command button on my switchboard and it doesn't work. Confusion and frustration has occurred. Can you help me again? I can post a copy of the database if you need to take a look at it.

ByteMyzer · Jun 12, 2009

Yes, it would help if you attached a copy of the database (MDB, not ACCDB, please).

joshandsony · Jun 12, 2009

Byte, I only have accdb. I didn't think it was backwards compatible and I don't have the old version.

hokiewalrus · Jun 12, 2009

ByteMyzer said:
The ADODB.Recordset Open method will not work on a query with a Form-Control Parameter reference (ex. [Forms]![ProfitLossForm]![ControlNumber]).

Ah...crap.

Since I know what the ControlNumber is at runtime I could assign the parameter through VBA...if I had any idea how to do that of course.

I wish you could call a query with a "where condition" like you can with forms.

hokiewalrus · Jun 12, 2009

Got it, turns out you can pass parameter values.

Thanks for the function, it works great!

kelvin78 · Jun 29, 2009

Hi ByteMyzer,

I could not get the sub procedure to work correctly as it keeps giving me the below pop up:

"Expected variable or procedure, not module."

Sub procedure was placed in a standard module.

Even testing the procedure in the Immediate Window with the below parameter, I keep getting the same pop up message.

MultiQueryExportToExcel "C:\Documents and Settings\Windows User\Desktop\Expedite Access.xlsx", "Non-Confirmed Export Query"

Did I miss a step or something?

Would appreciate the help

Regards,
Kelvin

ByteMyzer said:
You are not supposed to add the file to the code. You are supposed to copy and save the code, exactly as I supplied it, and then call the code with the file name as a parameter, like the following example:

MultiQueryExportToExcel "C:\Documents and Settings\Windows User\Desktop\Expedite Access.xlsx", "Non-Confirmed Export Query", "Confirmed Export Query"

ByteMyzer · Jun 29, 2009

kelvin78 said:
Hi ByteMyzer,

I could not get the sub procedure to work correctly as it keeps giving me the below pop up:

"Expected variable or procedure, not module."

Sub procedure was placed in a standard module.

Even testing the procedure in the Immediate Window with the below parameter, I keep getting the same pop up message.

MultiQueryExportToExcel "C:\Documents and Settings\Windows User\Desktop\Expedite Access.xlsx", "Non-Confirmed Export Query"

Did I miss a step or something?

Would appreciate the help

Regards,
Kelvin

kelvin78,

Did you name your Module MultiQueryExportToExcel?

If you did, then there's your problem. You can not give the Module the same name as the Procedure and expect to call the Procedure by its name.

In that case, try renaming the Module to something like mdlMultiQueryExportToExcel. That way, when you call Sub MultiQueryExportToExcel, Access will know that you are referring to the Sub, and not the Module.

kelvin78 · Jun 30, 2009

So that's what the pop up meant. Thanks again!

Another day...another new thing to learn.

Help exporting to multiple sheets with VB (1 Viewer)

joshandsony

Registered User.

ByteMyzer

AWF VIP

joshandsony

Registered User.

joshandsony

Registered User.

ByteMyzer

AWF VIP

joshandsony

Registered User.

ByteMyzer

AWF VIP

hokiewalrus

Registered User.

ByteMyzer

AWF VIP

joshandsony

Registered User.

ByteMyzer

AWF VIP

joshandsony

Registered User.

hokiewalrus

Registered User.

hokiewalrus

Registered User.

kelvin78

New member

ByteMyzer

AWF VIP

kelvin78

New member

Similar threads

Users who are viewing this thread