Access to excel report

[Super Suarez]

Hi All,

I'm trying to write a VBA function to write a table to excel but with filters. I'm using the Docmd functionality but can't find where I filter. Here's what I have so far:-

Private Sub Command344_Click()

DoCmd.OutputTo acOutputTable, "Joblog", acFormatXLS, "C:\Temp\temp1.xls", True

End Sub

I really only want certain fields and eventually to filter on certain records. What is the syntax or am I in completely the wrong area?

Thanks in advance

 

[pbaldy]

Create a query with the fields and criteria you want, and output that.

 

[Super Suarez]

As in DoCmd.OutputTo acOutputQuery method?

 

[pbaldy]

Yes indeed.

 

[Super Suarez]

You obvously have to create the query and force into the acOutputQuery method, but this isnt working. Some syntax help would be appreciated

Set tempSql = CurrentDb.OpenRecordset("SELECT * FROM Joblog")

DoCmd.OutputTo acOutputQuery, "tempSql", acFormatXLS, "C:\Temp\temp1.xls", True

 

[pbaldy]

I believe it has to be a saved query to use OutputTo. You could use automation and use your recordset:

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

 

[Super Suarez]

I managed to output an excel file by the file by the following piece of code I found and tinkered with. It outputs an excel file based upon your query.

My only problem is - if you press the button again, it errors with a file already open msg, so i need to check for open and close it if allready open. How do I do that?

Private Sub Command344_Click()
tempSql = "SELECT [Record Num] FROM Joblog WHERE [Contianed Date] Is Null ORDER BY Engineer,[Open Date],[Record Num]"

If IsNull(DLookup("name", "msysobjects", "name='query1'")) Then
CurrentDb.CreateQueryDef "Query1", tempSql
Else
CurrentDb.QueryDefs("Query1").SQL = tempSql
End If

DoCmd.OutputTo acOutputQuery, "Query1", acFormatXLS, "C:\Temp\temp1.xls", True
End Sub

 

[pbaldy]

A quick Google found this among others:

http://www.access-programmers.co.uk/forums/showthread.php?t=241538