VBA to export several queries into a new excel document ?

[hudaz]

Hi Everyone,

I'm looking to export several queries into separate excel sheets (within the same document) on the click of a button within Access.

I'm struggling because the Docmd.outputTo and Docmd.outputTo commands will only let me export one at a time. This is a problem because i new file is created every time as the file names are timestamped.

Is there a way around this ?


Thanks!

Andy

 

[Ranman256]

Make a macro,
Put in all your queries to export ...
Transferspreadsheet query

The last parameter is the sheet name, so each query is on a different tab.

Run the macro.

 

[hudaz]

Hi Ranman256,

Thanks for the reply. So i create a macro, add each query i want to export (exportwithformatting), could you explain the last parameter bit please ? i'm looking at the macro but i'm confused as to where you mean.

Thanks for your help so far!

 

[sneuberg]

What I've found when using DoCmd.TransferSpreadsheet is that if you run it multiple times using the same output file name but varying table name (it your case query name) it plops the output nicely into different sheets with the sheet name being the table name. At least that's what happened with the code below which outputted all of the tables in the database to one spreadsheet.

Sub ExportTablesLB()

Dim AccessApp As Object
Dim tdef As Object
Const acExport = 1
Const acSpreadsheetTypeExcel8 = 8
Set AccessApp = CreateObject("Access.Application")
AccessApp.OpenCurrentDatabase ("C:\tracker\Naveen.accdb")
For Each tdef In AccessApp.CurrentDb.TableDefs
    If Left(tdef.Name, 4) <> "MSys" Then
        AccessApp.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, tdef.Name, "C:\tracker\Naveen.xls"
    End If
Next tdef
AccessApp.CloseCurrentDatabase

End Sub

 

[Frothingslosh]

OP just needs to make sure that the table names aren't greater than 31 characters in length, and that they don't include any of the 7 banned characters: \,/,*,[,],:, and ?

Then again, that's good advice no matter what.

 

[hudaz]

Is there a way to do this but send the query into a excel template instead ?

I'm putting a reporting feature into an access database, however visually the data can be displayed better within excel so i'm planning on creating a template that i can export the data from Access then save it with a unique file name. there will be a dashboard front page with formulas looking for specific data and tabs that will contain the queries.

Anybody had any experience with this ? I've managed to get the database to send the queries over but that's where i hit a brick wall.

thanks!

Andy