pipe/export query results into excel (VB)

Hello All,

I'm using this Code to have a query pipe/export its results out to an excel spreadsheet. And it works great!


I have setup prompts in the query for different buildings, so I want to run this query for building "A", "B" ,"C" and then have the results pipe/export to separate excel file. ie A.xls, B.xls, C.xls

Any Ideas, this my first attempt at working with VB, so any help would be great..

thanks,
pete

pipe!? You must be an old DOS'r?

I was hoping that would catch someone's attention!!

BUMp Bump Bump

and then have the results pipe/export to separate excel file. ie A.xls, B.xls, C.xls

Click to expand...

Change a couple of lines of your code.


Then pass in your file name. (Which you can generate or get from a table or do just about whatever you want, really.)

Try the following in a Loop and it will do the whole lot automatically

You could try the following... it works well for me.
You will need to replace all instances of <...> with the fields/names you require.

Function Export_To_Excel()

Dim dbs As Database
Dim rst_WrkShtName
Dim WrkShtName As String
Dim qdfTemp As QueryDef

DoCmd.SetWarnings False

Set dbs = CurrentDb

'-- Use this query to find a field to be used as names for the worksheets
Set rst_WrkShtName = dbs.OpenRecordset("SELECT <Tb_Fieldname> FROM <Access_Tb> GROUP BY <Tb_Fieldname> ORDER BY <Tb_Fieldname> ASC;")

With rst_WrkShtName

.MoveFirst
Do While Not rst_WrkShtName.EOF
WrkShtName = (rst_WrkShtName("<Tbl_Fieldname>"))

With dbs

' Creates a query named as the current Tb_Fieldname, query produces results for each worksheet
' The worksheet is created with the same name as the Query
Set qdfTemp = .CreateQueryDef(WrkShtName, "SELECT * FROM <Access_Tb> WHERE <Tbl_Fieldname> = '" & WrkShtName & "';")

' Transfers this query to a spreadsheet named as WrkShtName in the same file
DoCmd.TransferSpreadsheet 1, 8, WrkShtName, "C:\temp\<Excel_Filename.xls>", True

' Deletes the query after exporting to excel
.QueryDefs.Delete WrkShtName

End With

.MoveNext
Loop

End With

End Function