Add code to save a report as sheet or pdf

[daniels012]

OK,
What about some code that makes a copy of the current WorkOrderReport, then names it using the data in the "PhysicalCompnyfield", then saves it to a directory in my network.


Am I asking way too much?
Michael

[CreganTur]

I use the following code behind a button click on a form to save my reports to a specified folder, and then open them in Excel for viewing.

You need to replace "QueryName" with the name of the Query your report is built on, or the name of the report itself- this will tell Access what data to write into an Excel file.

You can set any filepath you want- you can even have variables in your filepath (set the variable to the value of the field you mentioned). You just have to make sure that all the folders exist- otherwise no file will be created. Example:

c:\Folder1\Folder2\Folder3\Filename

All 3 folders (Folder1, Folder2, and Folder3) must exist.

'Export report to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"QueryName", "C:\Destination Filepath, True

'Open newly created Excel file for the user
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\excel.exe " & Chr(34) _
& "C:\Destination Filepath & Chr(34), 1)

[daniels012]

Before I try this, does it matter that I am using Access 2000?

Michael

[CreganTur]

Before I try this, does it matter that I am using Access 2000?

Michael

I don't think that will be an ussue.

However, my script is for use with Excel 2003- if you have a different version of excel then you may need to change "acSpreadsheetTypeExcel9" to a different Excel type (I think the built in help file can assist you in choosing the right one).

Also the Filepath for your version of Excel may be different than ""C:\Program Files\Microsoft Office\OFFICE11\excel.exe"- just check your individual filepath and plug that in.

[daniels012]

I figured all of that out with your help, thank you!
I need the file name to become the value that is in my Report's field:
PhysicalCompnyfield
Is this possible?
Michael

[CreganTur]

I figured all of that out with your help, thank you!
I need the file name to become the value that is in my Report's field:
PhysicalCompnyfield
Is this possible?
Michael

What kind of value are we talking about- numeric, string?

Are there many different values, or only a few set values?

If you have a small # of set values: ie. Baseball, Football, Soccer; then I would suggest:

create a seperate query (or report) for each type, so that Report1 is all Baseball, Report2 is all Football, and Report3 is all Soccer. Then you would create a form with 3 buttons: Baseball Report, Football Report, Soccer Report.

Behind each button click you would hardcode in the report name. The following code would send an Excel file named "Football Report" to the May Reports folder in the Reports folder on your c:\ drive:
'Export report to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"QueryName", "C:\Reports\May Reports\Football Report, True

Hope this helps.

[daniels012]

It is a string value.
Michael

[CreganTur]

Can anyone else help Michael out? I'm not sure how to set a variable's value to the text value of a specific field.

Michael:

that's all you need to do- set a string variable to the value of your desired field, then just use that string as the name of your file.

Example:
Dim strFileName As String
strFileName = '<<<value of specified field
'Export report to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"QueryName", "C:\Reports\May Reports\" & strFileName, True

[daniels012]

Cregan,
Here is what I have so far:



On Error GoTo PrintWOMacro_Err
Dim strFileName As String
DoCmd.Echo False, ""
DoCmd.OpenReport "RptWorkOrder", acPreview, "", "[QryWorkOrder]![WorkOrderID]=[Forms]![FrmWorkOrderData]![WorkOrderID]"
If (Forms!FrmWorkOrderData!NoChargeWO = True) Then
Reports!RptWorkOrder!NoChargeWOLabel.Visible = True
End If
If (Forms!FrmWorkOrderData!NoChargeWO = True) Then
Reports!RptWorkOrder!NoChargeWO2.Visible = True
End If
DoCmd.PrintOut acPrintAll, 1, 1, acHigh, 1, True
strFileName = Forms!FrmWorkOrderData!PhysicalCompany & ".xls"
'<<<value of specified field
'Export report to Excel
' DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
' "QryWorkOrder", "\\5.200.89.159\customer (file://5.200.89.159/customer) folder" & strFileName, True DoCmd.Close acReport, "RptWorkOrder"
' " & Forms!FrmWorkOrderData!PhysicalCompanyControlName & ".xls", false
PrintWOMacro_Exit:
Exit Function
PrintWOMacro_Err:
MsgBox Error$
Resume PrintWOMacro_Exit


Any idea what I need to do based on info you gave me before. I am so bad with coded!!!! I appreciate any help in straightening this out.
Thank You,
Michael

[CreganTur]

Hmmm... need my thinking cap for this one :sleuth:

Okay, when declaring your strFileName, do not add ".xls" to the end. Just use:
strFileName = Forms!FrmWorkOrderData!PhysicalCompany

I'm guessing with : \\5.200.89.159\customer (file://5.200.89.159/customer) folder, that "customer folder" is the full name of the folder you want the report written in. If so then you need to complete the filepath with "\"

Without that you're actually trying to create a new folder of "customer folder strFileName". Since that filepath doesn't exist (the folders don't already exist) then it won't create anything. VBA will not create folders that don't exist, unless it is explicitly told to do so (which is a completely separate function). While you're using this export method you need to manually create the folders to make your filepath valid.

Your whole export function should look like:
'Export report to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"QryWorkOrder", "\\5.200.89.159\customer (file://\\5.200.89.159\customer) folder\" & strFileName, True



What are you trying to accomplish with this? :
DoCmd.Close acReport, "RptWorkOrder"
' " & Forms!FrmWorkOrderData!PhysicalCompanyControlName & ".xls", false

[daniels012]

'Export report to Excel
' DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
' "QryWorkOrder", "\\5.200.89.159\customer (file://5.200.89.159/customer) folder\" & strFileName, True

This worked only making a copy of my query QryWorkOrder.
I would like it to show as My report : RptWorkOrder

Michael

[CreganTur]

This worked only making a copy of my query QryWorkOrder.


That's what you told it to do.


'Export report to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"QryWorkOrder", "\\5.200.89.159\customer (file://5.200.89.159/customer) folder\" & strFileName, True


It's going to export to Excel whatever the declared Query (or table- you could declare a table name here too) contains.



I would like it to show as My report : RptWorkOrder

Please clarify: do you want the name of the file to be "My Report: RptWorkOrder"? If so then you would get rid of strFileName and change your export to:


'Export report to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"QryWorkOrder", "\\5.200.89.159\customer (file://5.200.89.159/customer) folder\My Report: RptWorkOrder", True


Or am I misunderstanding your question?

[daniels012]

I am so sorry I am unclear.
I am really green when it comes to this stuff.
What i want;
We print our work order which is RptWorkOrder
Just as it is printed I would like a copy to go to a file. Like a pdf or anything. This what I want. I want to be able to view the Work Order by opening (in your case) excel.

Michael

[CreganTur]

I am so sorry I am unclear.
I am really green when it comes to this stuff.
What i want;
We print our work order which is RptWorkOrder
Just as it is printed I would like a copy to go to a file. Like a pdf or anything. This what I want. I want to be able to view the Work Order by opening (in your case) excel.

Michael

Is there a query feeding this report?

How do you want the name of the report to look?

Please be very clear- give as much detail as you think is neccessary so the problem can be fully understood.

[daniels012]

Is there a query feeding this report?
Yes, it is: QryWorkOrder



How do you want the name of the report to look?


The name of the report is fine when I did this:


Dim strFileName As String
strFileName = Forms!FrmWorkOrderData!PhysicalCompany & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"QryWorkOrder", "\\5.200.89.159\customer (file://5.200.89.159/customer) folder\" & strFileName, True


So the NAME is turning out just fine!


I guess what I want is when I print a Work Order, and it is in my hand, I want to be able to look at an image of that exact Work Order. It can be .pdf, excel, image file, what ever.
Is this what you are asking? Thank you for all your help so far!!
Michael

[Ken Puls]

OK,
What about some code that makes a copy of the current WorkOrderReport, then names it using the data in the "PhysicalCompnyfield", then saves it to a directory in my network.

Am I asking way too much?
Michael
If you are interested in going the PDF route still, you can download a copy of PDFCreator for nothing, and use a variant of some code at my site (http://www.excelguru.ca/node/68) to automate printing the report to a PDF file. (Use PDFCreator 0.9.3, not the latest version, as they changed some of the OM.)

I haven't tested this, as I don't have your database to play with, no your direcotry structure (You'd need to update the PDF Path portion.) Hopefully it will help though.

Sub PrintAccessReportToPDF_Early()
'Author : Ken Puls ( www.excelguru.ca (http://www.excelguru.ca))
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from http://sourceforge.net/projects/pdfcreator/ )
' Designed for early bind, set reference to PDFCreator

Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
Dim sPrinterName As String
Dim sReportName As String
Dim lPrinters As Long
Dim lPrinterCurrent As Long
Dim lPrinterPDF As Long
Dim prtDefault As Printer
Dim bRestart As Boolean

On Error GoTo EarlyExit
'Resolve index number of printers to allow changing and preserving
sPrinterName = Application.Printer.DeviceName
For lPrinters = 0 To Application.Printers.Count
Set Application.Printer = Application.Printers(lPrinters)

Set prtDefault = Application.Printer
Select Case prtDefault.DeviceName
Case Is = sPrinterName
lPrinterCurrent = lPrinters
Case Is = "PDFCreator"
lPrinterPDF = lPrinters
Case Else
'do nothing
End Select
Next lPrinters

'/// Change the report and output file name here! ///
sReportName = "RptWorkOrder"
sPDFName = Forms!FrmWorkOrderData!PhysicalCompany & ".pdf"
sPDFPath = Application.CurrentProject.Path & "\"

'Check if PDFCreator is already running and attempt to kill the process if so
Do
bRestart = False
Set pdfjob = New PDFCreator.clsPDFCreator
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
'PDF Creator is already running. Kill the existing process
Shell "taskkill /f /im PDFCreator.exe", vbHide
DoEvents
Set pdfjob = Nothing
bRestart = True
End If
Loop Until bRestart = False

'Change the default printer
Set Application.Printer = Application.Printers(lPrinterPDF)
Set prtDefault = Application.Printer

'Start PFF Creator
Set pdfjob = New PDFCreator.clsPDFCreator
With pdfjob
If .cStart("/NoProcessingAtStartup") = False Then
MsgBox "Can't initialize PDFCreator.", vbCritical + _
vbOKOnly, "PrtPDFCreator"
Exit Sub
End If
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With

'Print the document to PDF
DoCmd.OpenReport (sReportName)

'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False

'Wait until the file shows up before closing PDF Creator
Do
DoEvents
Loop Until Dir(sPDFPath & sPDFName) = sPDFName

Cleanup:
On Error Resume Next
'Release objects and terminate PDFCreator
pdfjob.cClose
Set pdfjob = Nothing
Shell "taskkill /f /im PDFCreator.exe", vbHide

'Reset the (original) default printer
Set Application.Printer = Application.Printers(lPrinterCurrent)
On Error GoTo 0
Exit Sub

EarlyExit:
'Inform user of error, and go to cleanup section
MsgBox "There was an error encountered. PDFCreator has" & vbCrLf & _
"has been terminated. Please try again.", _
vbCritical + vbOKOnly, "Error"
Resume Cleanup
End Sub