How to export multiple queries to seperate worksheets in excel 3

[cantona]

I have two queries that i would like to output to excel. Query1 and Query2. So far ive created a macro which will output the results of each query to excel and prompt the user to select a location for the file and give it a name. This works fine, however, im getting a seperate excel document for each query. what i would really like is to produce something that will create one excel document with the results from query1 going into a worksheet and the results of query2 going into a seperate worksheet. Is this possible? How would i go about it? Ive not attempted anything along these lines before. Any help would be greatly appreciated!

 

[BuilderSpec]

Hi cantona

Have a look at the following functions :


Function Excel_AddQuery(xl As Object, wb As Excel.Workbook, qry As String)

Dim rs As New ADODB.Recordset
Dim fc As Integer
Dim row As Integer

wb.Sheets.Add
wb.ActiveSheet.Name = qry


rs.Open qry, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
If Not rs.EOF Then

fc = 1
While (fc < rs.Fields.Count)
xl.Cells(1, fc) = rs(fc).Name

fc = fc + 1
Wend
row = 2

While Not rs.EOF
fc = 1
While (fc < rs.Fields.Count)
xl.Cells(row, fc) = rs(fc)
fc = fc + 1
Wend
row = row + 1
rs.MoveNext
Wend


End If
rs.Close
Set rs = Nothing

End Function


Function MyExcelExport(qry As String, qry2 As String, SaveName As String)
Dim wbc As Integer
Dim wb As Excel.Workbook
Dim xl As Object

Set xl = CreateObject("Excel.Application")
xl.visible = True

Set wb = xl.Workbooks.Add

' remove all the sheets from the new book

wbc = wb.Sheets.Count
While (wb.Sheets.Count > 1)
wb.Sheets(1).Delete
Wend

Excel_AddQuery xl, wb, qry
Excel_AddQuery xl, wb, qry2

wb.SaveAs SaveName
wb.Close False
Set wb = Nothing
xl.Quit

Set xl = Nothing



End Function



MyExcelExport expects 3 arguments : query1 , query2 and the name you want to save the excel document as.

Excel_AddQuery is used by MyExcelExport and really you don't need to call it it all.

I have written as a 2 based queries but you can easily adapt it to pass 3 4 or more queries..

MyExcelExport does :

1) Opens Excel
2) Removes all existing sheets
3) For each query it passes to Excel_AddQuery
4) Saves the spreadsheet
5) Closes the spreadsheet
6) Closes excel

Excel_AddQuery

1) Adds a new sheet to excel workbook
2) opens the query recordset
3) Extracts the fieldnames of the query and creates a header row in the sheet from them
4) Loops through the recordset putting each record into the spreadsheet
5) closes the query recordset

hope that made sense..try it..

I used Access 2002 , had to make a reference to Microsoft Excel 10.0 Object Library.. should work with other versions ok..


Hope this helps!

Regards

BuilderSpec

 

[PHV]

BuilderSpec, to retrieve values faster, replace this:
row = 2
While Not rs.EOF
fc = 1
While (fc < rs.Fields.Count)
xl.Cells(row, fc) = rs(fc)
fc = fc + 1
Wend
row = row + 1
rs.MoveNext
Wend

with simply this:
xl.ActiveSheet.Range("A2").CopyFromRecordset rs

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cantona]

Hi BuilderSpec / PHV,

Thanks for your help. Im not too familiar with the code above and unsure how to amend it to work for me. If my queries are called Query1 and Query2, what would i need to replace in the code? Secondly, how would you advise calling the functions? Can it be done via a macro?

 

[BuilderSpec]

PHV

Thanks I never knew that existed!

Cantona

On a form create a button... cancel the wizard if it starts.

Suppose the button is called Button1 then..

Sub Button1_Click()
MyExcelExport "Query1","Query2","myfilename.xls"
End Sub

Make sure that the functions MyExcelExport and Excel_AddQuery are in a module so they are public, or you could copy them into the code below the Button1_Click procedure it would work either way.

Any queries get back in touch


Hope this helps!

Regards

BuilderSpec

 

[cantona]

Hi BuilderSpec,

Once again, thanks for your help. ive attempted what you suggested but when i click the button i get the following error message. Compile Error - User-defined type not defined. The first line of the function is highlighted...

Function Excel_AddQuery(xl As Object, wb As Excel.Workbook, qry As String

Is there something obvious that i am doing wrong? sorry for testing your patience!!

 

[BuilderSpec]

hi

Sorry my fault should have mentioned it...

Go to "Tool" then "References" ..

Make sure you have "Microsoft Excel 10.0 Object Library" ticked ( or whichever Excel version is in the list )



Hope this helps!

Regards

BuilderSpec

 

[PHV]

menu Tools -> References ...
Tick the Microsoft Excel x.y Object Library

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cantona]

Sorry to be a pain, but im now getting the same error but with the following part of the code highlighted;


Dim rs As New ADODB.Recordset

Is there something else that i would need to tick under references?

 

[BuilderSpec]

Hi

Tick "Microsoft ActiveX Data Objects 2.1 Library"



Hope this helps!

Regards

BuilderSpec

 

[cantona]

Thanks for all your help builderspec. That now works perfectly! However, once the spreadsheet has been built it closes. Im not sure where it is being saved. Can this be specified?

 

[PHV]

Im not sure where it is being saved
wb.SaveAs SaveName

Can this be specified
MyExcelExport "Query1","Query2",[!]"myfilename.xls"[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BuilderSpec]

Hi

When you use the function

MyExcelExport(qry As String, qry2 As String, SaveName As String)

the SaveName can be a fully qualified path..for example "c:\mystuff\xl\qry.xls" ..if you specify just a filename then chances are they are being saved in your "My Documents" folder.

If you want Excel to stay open after it has been created then you could try commenting out the lines to close the sheet and excel like below in function MyExcelExport

wb.SaveAs SaveName
' wb.Close False
Set wb = Nothing
' xl.Quit







Hope this helps!

Regards

BuilderSpec

 

[cantona]

Thanks for all your help! I now have it working exactly how i wanted!

 

[SalGal]

This was a very helpful post, as I was attempting to do the same thing. However, the queries that I'm exporting to Excel are crosstab queries, and the first column of data is not appearing once the export to Excel has been made. I've read through the code (as best I can!), and I'm not sure where it's being specified to exclude that column. FYI, I used PHV's shorter method of retrieving values, but the longer version doesn't produce the column either.

Thanks for suggestions!

 

[BuilderSpec]

Hi

Well spotted !

There is a bug in the code that starts causes it to omit the first column. In short the Fields collection has a zero index base but the Excel Cells collection has a 1 index base. I was saying :

Cell(1,1) = Field(1) thinking that it would be the first field when it should have been :
Cell(1,1) = Field(0)

Anyway I have modified the functions below. if you use them then it should output your columns to exel as expected. let me know if it doesn't...





Function Excel_AddQuery(xl As Object, wb As Excel.Workbook, qry As String)

Dim rs As New ADODB.Recordset
Dim fc As Integer
Dim row As Integer

wb.Sheets.Add
wb.ActiveSheet.Name = qry


rs.Open qry, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
If Not rs.EOF Then

fc = 0
While (fc < rs.Fields.Count)
xl.Cells(1, fc + 1) = rs(fc).Name

fc = fc + 1
Wend
row = 2

While Not rs.EOF
fc = 0
While (fc < rs.Fields.Count)
xl.Cells(row, fc + 1) = rs(fc)
fc = fc + 1
Wend
row = row + 1
rs.MoveNext
Wend

End If
rs.Close
Set rs = Nothing

End Function


Function MyExcelExport(qry As String, SaveName As String)
Dim wbc As Integer
Dim wb As Excel.Workbook
Dim xl As Object

Set xl = CreateObject("Excel.Application")
xl.Visible = True

Set wb = xl.Workbooks.Add

' remove all the sheets from the new book

wbc = wb.Sheets.Count
While (wb.Sheets.Count > 1)
wb.Sheets(1).Delete
Wend

Excel_AddQuery xl, wb, qry

wb.SaveAs SaveName
' wb.Close False
' Set wb = Nothing
' xl.Quit
Set xl = Nothing
End Function


Hope this helps!

Regards

BuilderSpec

 

[PHV]

So, BuilderSpec, you still use the slow way ?

 

[BuilderSpec]

PHV

I wrote it as a one off for cantona..I rarely use excel in my live applications. But yes you are right... salgal you should try and implement PHV's method, mine will still work but PHV's is faster and more optimized.



Hope this helps!

Regards

BuilderSpec

 

[SalGal]

OK, I hate to gush, but you guys are fabulous!

Thanks a bunch for explaining this so well - it's going to be very helpful in many situations.

skb