Question Exporting Report to new Excel document using Database field as filename.

[pturco]

I have been looking for a solution to this for some time and cannot find anything. I know I'll have to do this in code, but do not know what to write or where. I have a report generated from queries which I need to export to a NEW excel file, I need the file to export to a specific file location and use a field from the report as part of the file name. Currently I have a macro set up so the user clicks the macro which runs a series of queries (I need these to run before the report can be generated), then it exports the report (the user has to choose a path and filename), then it runs an SQL statement and closes. I would like the user to not have to choose a path and filename. Please keep in mind I am new to VB code. Any help is much appreciated!

 

[pturco]

I figured it out. Actually easier then I thought, now that I know how to do it.

Cheers,
Pam

 

[Rabbie]

I figured it out. Actually easier then I thought, now that I know how to do it.

Cheers,
Pam

It would be helpful if you post your solution.

 

[pturco]

Sorry about that.

Here is the code which I used in a command button. It exports the excel file to a specific location, with a database field as the filename (references a form). I converted my macro to code. So what you are seeing is a few queries running then the actual export of the report (I bolded this). This is the code I had trouble with, actually turns out is wasn't that hard.


Private Sub Command38_Click()
On Error GoTo Err_Command38_Click

Dim strPath As String
strPath = "C:\Documents and Settings\Administrator\My Documents\" 'Enter your path here
Beep
MsgBox "Report will now be exported to " & strPath & ""
Dim strDocName As String
strDocName = "SampleDispatch_Export" 'Enter your filename here

DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from Printout;", -1 'Clears data from a temp table
DoCmd.OpenQuery "UpdatePrintout_Sample", acViewNormal, acEdit 'runs a query to populate table
DoCmd.OpenQuery "UpdatePrintout_QA", acViewNormal, acEdit 'runs a query to populate table

DoCmd.OutputTo acOutputReport, strDocName, acFormatXLS, strPath & "SampleDispatch_" & [Forms]![Entry Form]![collar1].[Form]![hole_id] & ".xls"
MsgBox "Export Complete!", vbOKOnly, ""
DoCmd.RunSQL "delete * from Printout;", -1 'Clears data from a temp table
DoCmd.SetWarnings True
Exit_Command38_Click:
Exit Sub
Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub