Multiple query export and save as...

[beef4dinner]

Hi guys,

I'm by no means an expert in VB and spend most of my time reverse engineering and bodging previous code until it works. I need to export two queries to one excel workbook on two worksheets. I know how to export one query, but i'm lacking the basic skills to export too.

Also, when I export the query(s) I want it to ask where to save it, as opposed to specifying it myself. A bit like when you use the macro builder to export a query to excel, it will always as where you want to save it.

The code I have so far is basic, allows one query to be exported and saves it to where the string specfies. Any help would be massively useful and help me continue my slow progression through VB.

Cheers

Tom

Private Sub Command5_Click()
Dim strName As String
Dim strPath As String
strName = Form_Reporter.Combo0.Value
strPath = "T:\" & strName & " " & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2) & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report Query", strPath, True
End Sub

 

[barlowr70]

You can output multiple queries to a spreadsheet without a problem. Try it manually and then decide how best to code it (Maybe a For Loop and pass a new query name at each iteration). If you want a prompt for the file type and/or destination you can either use DoCmd.OutputTo acOutputReport, RepName, , , 0 or use the Common dialogue ActiveX. Try this...

http://support.microsoft.com/kb/303066

 

[beef4dinner]

Cheers Barlow, I will have a look at this over the weekend and let you know how i go!

Thanks,

Tom