PDF print to
- Thread starter Gismo
- Start date
Minty
AWF VIP
- Local time
- Today, 14:14
- Joined
- Jul 26, 2013
- Messages
- 10,575
First things this can be simplified to
StrFileName = strGetFileFolderName("Defer - Registration - " & Forms![Progress Check List - Defer]![Registration], 2, "Excel")
But without seeing the function strGetFileFolderName() we can't say why it won't work.
Where is the function stored - hopefully in a separate module?
StrFileName = strGetFileFolderName("Defer - Registration - " & Forms![Progress Check List - Defer]![Registration], 2, "Excel")
But without seeing the function strGetFileFolderName() we can't say why it won't work.
Where is the function stored - hopefully in a separate module?
I did not have my GetFileFolderName in a seperate module
Thank you for that
Works perfect
I just need to change to select the report and save as PDF instead of spreadsheet
Last edited:
Gasman
Enthusiastic Amateur
- Local time
- Today, 14:14
- Joined
- Sep 21, 2011
- Messages
- 16,038
You can't just go making up names as you feel like it. 
Why not actually look and see what that command needs?
access-excel.tips
Why not actually look and see what that command needs?
Access VBA DoCmd.OutputTo Method - Access-Excel.Tips
This Access tutorial explains how to use DoCmd.OutputTo Method to export different objects to specific file types, for example, export Query to xlsx
access-excel.tips
Sorry, missed you on this one, which name is not valid?You can't just go making up names as you feel like it.
Why not actually look and see what that command needs?
Access VBA DoCmd.OutputTo Method - Access-Excel.Tips
This Access tutorial explains how to use DoCmd.OutputTo Method to export different objects to specific file types, for example, export Query to xlsx
Gasman
Enthusiastic Amateur
- Local time
- Today, 14:14
- Joined
- Sep 21, 2011
- Messages
- 16,038
The format? Did you not even look at the link?
I don't even know if the other parameters are in the correct place, as I use intellisense to get mine correct?
Yes I looked at the format you sent.The format? Did you not even look at the link?
I don't even know if the other parameters are in the correct place, as I use intellisense to get mine correct?
My format is the same as the sample, unless I am missing something here
OutputType - acOutputReport
ObjectName - "Defer"
OutputFormat - "PDFFormat(*.pdf)"
OutputFile - StrFileName
AutoStart - False
TemplateFile - ""
Encoding , ,
OutputQuality - acExportQualityPrint
But he file does not have the extention of PDF
I have the function
Public Function strGetFileFolderName(Optional strInitialDir As String = "", Optional lngType As Long = 4, Optional strPattern As String = "All Files,*.*") As String
'StrInitialDir = where the filedialog starts browsing
'lngType e.g. 'msoFileDialogFilePicker = 3, msoFileDialogFolderPicker =4, msoFileDialogOpen=1,msoFileDialogSaveAs=2
Dim fDialog As Object
Dim vFile As Variant, varEntry As Variant
strGetFileFolderName = ""
Set fDialog = Application.FileDialog(lngType)
With fDialog
.Title = "Browse for "
Select Case lngType
Case 1 'msoFileDialogOpen
.Title = .Title & "File to open"
Case 2 'msoFileDialogSaveAs
.Title = .Title & "File to SaveAs"
Case 3 'msoFileDialogFilePicker
.Title = .Title & "File"
Case 4 'msoFileDialogFolderPicker
.Title = .Title & "Folder"
End Select
Select Case strPattern
Case "Excel"
strPattern = "MS Excel,*.XLSX; *.XLSM; *.XLS"
Case "Access"
strPattern = "MS Access,*.ACCDB"
Case "PPT"
strPattern = "MS Powerpoint,*.PPTX; *.PPTM"
Case "PDF"
strPattern = "PDF,*.PDF"
End Select
If lngType <> 2 And lngType <> 4 Then
'Reset then add filter patterns separated by tildes (~) where
' multiple extensions are separated by semi-colons (and the
' description is separated from them by a comma (,).
' Example strPattern :
' "MS Access,*.ACCDB; *.MDB~MS Excel,*.XLSX; *.XLSM; *.XLS"
'.filters.Clear
For Each varEntry In Split(strPattern, "~")
'.filters.Add Split(varEntry, ",")(0), Split(varEntry, ",")(1)
Next varEntry
End If
'Set some default settings
.InitialFileName = strInitialDir
.AllowMultiSelect = False
.InitialView = 2 'msoFileDialogViewDetails
'Only return a value from the FileDialog if not cancelled.
If .Show Then strGetFileFolderName = .SelectedItems(1)
End With
ExitHere:
Exit Function
HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
Resume ExitHere
End Function
Gasman
Enthusiastic Amateur
- Local time
- Today, 14:14
- Joined
- Sep 21, 2011
- Messages
- 16,038
Yes, you are completely missing the error, even when you list them like that.
Try putting that list into a document or Excel, then alongside that put the list of the 'allowed' options and see if you can then spot the error.
As for your function, a simple debug.print will show you if that is working correctly.?
With no indentation, I am not even going to attempt it.
Try putting that list into a document or Excel, then alongside that put the list of the 'allowed' options and see if you can then spot the error.
As for your function, a simple debug.print will show you if that is working correctly.?
With no indentation, I am not even going to attempt it.
Will have a look at it nowYes, you are completely missing the error, even when you list them like that.
Try putting that list into a document or Excel, then alongside that put the list of the 'allowed' options and see if you can then spot the error.
As for your function, a simple debug.print will show you if that is working correctly.?
With no indentation, I am not even going to attempt it.
My function actually has indentation, not sure why it did not paste in that format
Public Function strGetFileFolderName(Optional strInitialDir As String = "", Optional lngType As Long = 4, Optional strPattern As String = "All Files,*.*") As String
'StrInitialDir = where the filedialog starts browsing
'lngType e.g. 'msoFileDialogFilePicker = 3, msoFileDialogFolderPicker =4, msoFileDialogOpen=1,msoFileDialogSaveAs=2
Dim fDialog As Object
Dim vFile As Variant, varEntry As Variant
strGetFileFolderName = ""
Set fDialog = Application.FileDialog(lngType)
With fDialog
.Title = "Browse for "
Select Case lngType
Case 1 'msoFileDialogOpen
.Title = .Title & "File to open"
Case 2 'msoFileDialogSaveAs
.Title = .Title & "File to SaveAs"
Case 3 'msoFileDialogFilePicker
.Title = .Title & "File"
Case 4 'msoFileDialogFolderPicker
.Title = .Title & "Folder"
End Select
Select Case strPattern
Case "Excel"
strPattern = "MS Excel,*.XLSX; *.XLSM; *.XLS"
Case "Access"
strPattern = "MS Access,*.ACCDB"
Case "PPT"
strPattern = "MS Powerpoint,*.PPTX; *.PPTM"
Case "PDF"
strPattern = "PDF,*.PDF"
End Select
If lngType <> 2 And lngType <> 4 Then
'Reset then add filter patterns separated by tildes (~) where
' multiple extensions are separated by semi-colons (
' description is separated from them by a comma (,).
' Example strPattern :
' "MS Access,*.ACCDB; *.MDB~MS Excel,*.XLSX; *.XLSM; *.XLS"
'.filters.Clear
For Each varEntry In Split(strPattern, "~")
'.filters.Add Split(varEntry, ",")(0), Split(varEntry, ",")(1)
Next varEntry
End If
'Set some default settings
.InitialFileName = strInitialDir
.AllowMultiSelect = False
.InitialView = 2 'msoFileDialogViewDetails
'Only return a value from the FileDialog if not cancelled.
If .Show Then strGetFileFolderName = .SelectedItems(1)
End With
ExitHere:
Exit Function
HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
Resume ExitHere
End Function
Let me examine my function again
I believe Case "PDF" could be in the incorrect format.
I drew up my spreadsheet but still I do not see my error.
| OutputType - acOutputReport | Allowed | ||
| ObjectName - "Defer" | Allowed | ||
| OutputFormat - "PDFFormat(*.pdf)" | Allowed | Same as per Command button builder | "acFormatPDF" As per Format |
| OutputFile - StrFileName | Allowed | Debug.Print "C:\Users\e498309\Desktop\Defer - Registration - 111" | |
| AutoStart - False | Allowed | ||
| TemplateFile - "" | Allowed | ||
| Encoding , , | Allowed | ||
| OutputQuality - acExportQualityPrint | Allowed |
Gasman
Enthusiastic Amateur
- Local time
- Today, 14:14
- Joined
- Sep 21, 2011
- Messages
- 16,038
So you think "PDFFormat(*.pdf)" is the same as "acFormatPDF"
According to your code, how on earth do you get that output filename?
Walk through your code line by line with F8 and look to see what it is doing.
According to your code, how on earth do you get that output filename?
Walk through your code line by line with F8 and look to see what it is doing.
No,So you think "PDFFormat(*.pdf)" is the same as "acFormatPDF"
According to your code, how on earth do you get that output filename?
Walk through your code line by line with F8 and look to see what it is doing.
I am saying, "PDFFormat(*.pdf)" is what command button builder coded and when I converted from macro to VB as a test, that is what was presented. But even with "acFormatPDF" I get this error. I did try both formats
With reference to my filename, i use this code for all my other excel file save as codes
With reference to my filename, i use this code for all my other excel file save as codes which seem to work well for excel
StrFileName = strGetFileFolderName("Defer" & " - Registration -" & " " & Forms![Progress Check List - Defer]![Reg], 2, "PDF")
Debug.Print
Which is correct. the file extension just does not auto populate to PDF but to All Files
I am using 2007-2016 version
Gasman
Enthusiastic Amateur
- Local time
- Today, 14:14
- Joined
- Sep 21, 2011
- Messages
- 16,038
OK, I have just created a macro to export to pdf and then converted it.
This is what I get?
and that VBA works as well.
I have hardly ever used macros and have always used what is shown in those links I gave you ie acFormatPDF
I have no idea as to whether the multiple quotes are needed. In fact removing them to get
works just as well.
So I have no clue whatsoever as to why you cannot get it to work?
This is what I get?
Code:
DoCmd.OpenReport "rptTestTransactions", acViewReport, "", "", acHidden
DoCmd.OutputTo acOutputReport, "rptTestTransactions", "PDFFormat(*.pdf)", """c:\temp\gismomac.pdf""", False, "", 0, acExportQualityPrintI have hardly ever used macros and have always used what is shown in those links I gave you ie acFormatPDF
I have no idea as to whether the multiple quotes are needed. In fact removing them to get
Code:
DoCmd.OpenReport "rptTestTransactions", acViewReport, "", "", acHidden
DoCmd.OutputTo acOutputReport, "rptTestTransactions", "PDFFormat(*.pdf)", "c:\temp\gismomac.pdf", False, "", 0, acExportQualityPrintSo I have no clue whatsoever as to why you cannot get it to work?
