PDF print to (1 Viewer)

Gismo

Registered User.
Local time
Today, 18:26
Joined
Jun 12, 2017
Messages
1,298
Hi all,

I have below code to save excel file to selected location

I now need to do the same with a PDF file but for some reason I am getting error message on a code I use often

Please could you advise



1634204233718.png


Private Sub DeferBtn_Click()
On Error GoTo DeferBtn_Click_Err

Dim StrFileName As String
Dim StrQryName As String
Dim StrSaveFile As String

'DoCmd.SetWarnings False
'DoCmd.SelectObject acModule, "OpenOutlook", False
'DoCmd.OpenQuery "Update Daw Status - Rectification", acViewNormal, acEdit
'DoCmd.OpenQuery "Update Daw Status - Defer", acViewNormal, acEdit
'DoCmd.OpenQuery "Update Defer with Reg", acViewNormal, acEdit

StrFileName = strGetFileFolderName("Defer" & " - Registration -" & " " & Forms![Progress Check List - Defer]![Registration], 2, "Excel")

'Debug.Print StrFileName
StrQryName = "Defer QRY"


If Len(StrFileName & "") < 1 Then
StrFileName = Application.CurrentProject.Path & "\Defer" & Format(Date, "yyyymmdd") & ".xls"
End If

DoCmd.TransferSpreadsheet acExport, 10, StrQryName, StrFileName, True, , True

DeferBtn_Click_Exit:
Exit Sub

DeferBtn_Click_Err:
MsgBox Error$
Resume DeferBtn_Click_Exit



'DoCmd.Close acForm, "Progress Check List - Defer"
'DoCmd.Close acForm, "Menu"
'DoCmd.OpenForm "Menu", acNormal, "", "", , acNormal
'DoCmd.SetWarnings True


End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:26
Joined
May 7, 2009
Messages
19,169
is the form open?
 

Minty

AWF VIP
Local time
Today, 16:26
Joined
Jul 26, 2013
Messages
10,354
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?
 

Gismo

Registered User.
Local time
Today, 18:26
Joined
Jun 12, 2017
Messages
1,298
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?
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

Select Case strPattern
Case "Excel"
strPattern = "MS Excel,*.XLSX; *.XLSM; *.XLS"
Case "Access"
strPattern = "MS Access,*.ACCDB"
Case "PPT"
strPattern = "MS Powerpoint,*.PPTX; *.PPTM"

'DoCmd.TransferSpreadsheet acExport, 10, StrQryName, StrFileName, True, , True
 
Last edited:

Gismo

Registered User.
Local time
Today, 18:26
Joined
Jun 12, 2017
Messages
1,298
Hi,

Please could you advise how to convert the excel print to file to PDF print to file
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:26
Joined
May 7, 2009
Messages
19,169
you use DoCmd.OutputTo (you google it).
 

Gismo

Registered User.
Local time
Today, 18:26
Joined
Jun 12, 2017
Messages
1,298
you use DoCmd.OutputTo (you google it).
Hi,

I tried OutputTo but my output file remains as no extention

It needs to be .PDF

Private Sub DeferBtn_Click()
On Error GoTo DeferBtn_Click_Err

Dim StrFileName As String
Dim StrQryName As String
Dim StrSaveFile As String

DoCmd.SetWarnings False
'DoCmd.SelectObject acModule, "OpenOutlook", False
DoCmd.OpenQuery "Update Daw Status - Rectification", acViewNormal, acEdit
DoCmd.OpenQuery "Update Daw Status - Defer", acViewNormal, acEdit
DoCmd.OpenQuery "Update Defer with Reg", acViewNormal, acEdit

StrFileName = ("Defer" & " - Registration -" & " " & Forms![Progress Check List - Defer]![Reg])
'StrFileName = strGetFileFolderName("Defer" & " - Registration -" & " " & Forms![Progress Check List - Defer]![Reg], 2, "PDF")

Debug.Print StrFileName
StrQryName = "Defer QRY"


If Len(StrFileName & "") < 1 Then
StrFileName = Application.CurrentProject.Path & "\Defer" & Format(Date, "yyyymmdd") & ".PDF"
End If


DoCmd.OutputTo acOutputReport, "Defer", "PDFFormat(*.pdf)", StrFileName, False, "", , acExportQualityPrint
'DoCmd.TransferSpreadsheet acExport, 10, StrQryName, StrFileName, True, , True
'DoCmd.OutputTo acOutputReport, "Defer", "PDFFormat(*.pdf)", "", False, "", , acExportQualityPrint
'DoCmd.OutputTo acOutputReport, "Defer", "PDFFormat(*.pdf)", "c:\", False, "", , acExportQualityPrint


'DoCmd.Close acForm, "Progress Check List - Defer"
'DoCmd.Close acForm, "Menu"
'DoCmd.OpenForm "Menu", acNormal, "", "", , acNormal
'DoCmd.SetWarnings True


DeferBtn_Click_Exit:
Exit Sub

DeferBtn_Click_Err:
MsgBox Error$
Resume DeferBtn_Click_Exit





End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:26
Joined
Sep 21, 2011
Messages
14,044
You can't just go making up names as you feel like it. :(

Why not actually look and see what that command needs? :(

 

Gismo

Registered User.
Local time
Today, 18:26
Joined
Jun 12, 2017
Messages
1,298
You can't just go making up names as you feel like it. :(

Why not actually look and see what that command needs? :(

Sorry, missed you on this one, which name is not valid?
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:26
Joined
Sep 21, 2011
Messages
14,044
Sorry, missed you on this one, which name is not valid?
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?
 

Gismo

Registered User.
Local time
Today, 18:26
Joined
Jun 12, 2017
Messages
1,298
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.
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

StrFileName = strGetFileFolderName("Defer" & " - Registration -" & " " & Forms![Progress Check List - Defer]![Reg], 2, "PDF")

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, 16:26
Joined
Sep 21, 2011
Messages
14,044
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. :(
 

Gismo

Registered User.
Local time
Today, 18:26
Joined
Jun 12, 2017
Messages
1,298
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. :(
Will have a look at it now

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 (;) 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, 16:26
Joined
Sep 21, 2011
Messages
14,044
Well if you are talking about not just seeing PDF files in the file dialog, examine your code in that function to see why you get to see ALL types of files to select, for the options you are passing.
 

Gismo

Registered User.
Local time
Today, 18:26
Joined
Jun 12, 2017
Messages
1,298
Well if you are talking about not just seeing PDF files in the file dialog, examine your code in that function to see why you get to see ALL types of files to select, for the options you are passing.
Let me examine my function again
I believe Case "PDF" could be in the incorrect format.
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


I drew up my spreadsheet but still I do not see my error.

OutputType - acOutputReportAllowed
ObjectName - "Defer"Allowed
OutputFormat - "PDFFormat(*.pdf)"AllowedSame as per Command button builder"acFormatPDF" As per Format
OutputFile - StrFileNameAllowedDebug.Print "C:\Users\e498309\Desktop\Defer - Registration - 111"
AutoStart - FalseAllowed
TemplateFile - ""Allowed
Encoding , ,Allowed
OutputQuality - acExportQualityPrintAllowed
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:26
Joined
Sep 21, 2011
Messages
14,044
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.
 

Gismo

Registered User.
Local time
Today, 18:26
Joined
Jun 12, 2017
Messages
1,298
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.
No,

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

1634559143998.png



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")
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:26
Joined
Sep 21, 2011
Messages
14,044
So what do you get for strfilename after that function?
What version of Access are you using?
 

Gismo

Registered User.
Local time
Today, 18:26
Joined
Jun 12, 2017
Messages
1,298
So what do you get for strfilename after that function?
What version of Access are you using?
Debug.Print
C:\Users\e498309\Desktop\Defer - Registration - 111

Which is correct. the file extension just does not auto populate to PDF but to All Files

1634562707349.png



I am using 2007-2016 version
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:26
Joined
Sep 21, 2011
Messages
14,044
OK, I have just created a macro to export to pdf and then converted it.
This is what I get?
Code:
    DoCmd.OpenReport "rptTestTransactions", acViewReport, "", "", acHidden
    DoCmd.OutputTo acOutputReport, "rptTestTransactions", "PDFFormat(*.pdf)", """c:\temp\gismomac.pdf""", False, "", 0, acExportQualityPrint
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
Code:
    DoCmd.OpenReport "rptTestTransactions", acViewReport, "", "", acHidden
    DoCmd.OutputTo acOutputReport, "rptTestTransactions", "PDFFormat(*.pdf)", "c:\temp\gismomac.pdf", False, "", 0, acExportQualityPrint
works just as well.
So I have no clue whatsoever as to why you cannot get it to work? :(
 

Users who are viewing this thread

Top Bottom