Help with calling my code

[joshandsony]

Hello All. I am not very good at this whole VB thing, so please bear with me. The code I have listed below I am trying to run off my switchboard. I have tried calling the code through a button that I have placed on the switchboard, and I have also tried making a "run code" macro and neither seem to work. Can someone please point me in the right direction?

Public Sub MultiQueryExportToExcel( _
ByVal sFilename As String, _
ParamArray arrQueryName() As Variant)
Dim objExcelApp As Object
Dim objExcelBook As Object
Dim objExcelSheet As Object
Dim rs As ADODB.Recordset
Dim vQueryName As Variant
Dim X As Long
If UBound(arrQueryName) = -1 Then Exit Sub
Set objExcelApp = CreateObject("Excel.Application")
Set objExcelBook = objExcelApp.Workbooks.Add
With objExcelBook
For Each vQueryName In arrQueryName
Set rs = New ADODB.Recordset
rs.Open vQueryName, CurrentProject.Connection
Set objExcelSheet = .Worksheets.Add(, _
.Worksheets(.Worksheets.Count))
For X = 0 To rs.Fields.Count - 1
objExcelSheet.Cells(1, X + 1) = rs.Fields(X).Name
Next X
objExcelSheet.Range("A2").CopyFromRecordset rs
objExcelSheet.Name = vQueryName
rs.Close
Set rs = Nothing
Next vQueryName
Set objExcelSheet = Nothing
Do While .Sheets.Count > UBound(arrQueryName) + 1
.Sheets(1).Delete
Loop
.SaveAs sFilename
End With
Set objExcelBook = Nothing
objExcelApp.Quit
Set objExcelApp = Nothing
End Sub

 

[pbaldy]

Does this help?

http://www.baldyweb.com/Function.htm

 

[joshandsony]

Should I call it through a form button or through a macro?

 

[pbaldy]

Personally I don't use macros, so I'd simply call it from a button.

 

[joshandsony]

I think maybe it is my code that is wrong. When I play it, it takes me to the macros box and asks me to choose a macro. I am not very good with VB so I would venture to guess that this is the issue. I am trying to export multiple queries to one excel workbook with multiple sheets.

 

[pbaldy]

Have you tried calling it from a button?

 

[joshandsony]

I tried calling it with a button but it does not do anything. That is what leads me to believe that the code is wrong. Please help!

 

[pbaldy]

What is the code now? Can you post the db?

 

[joshandsony]

Hi Pbaldy. I have attached my DB. I am trying to get "Confirmed Export Query" and "Non-Confirmed Export Query" to go into 1 excel file with multiple sheets.

Let me know what you think.

 

[pbaldy]

There is no code behind the button. How would you call the function?

 

[joshandsony]

If you go to the design view of the switchboard and click on the event tab in the properties menu, in the "on click" part of the menu click on the 3 little dots and it will bring up the code behind the button.