VBA to Save As PDF Tweak

[rodl66]

Hello,

I have the following code that does almost what I want it to except it won't save in the location I've specified, instead it prompts for you to search for the folder to save the file in. If possible, I'd like it to save where I thought I had it going to (C:\PIO\Daily Files\ECEAT\EntryCL\). I appreciate your time and expertise, I don't want the user to have to figure out where to save the file, I just want them to be able to click OK to save it.

Thank you.

Sub PDFActiveSheetCkIn()

'for Excel 2010 and later
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant

On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "ddmmmyyyy\_hhmmss")

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
strPath = "C:\PIO\Daily Files\ECEAT\EntryCL\"
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile

'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")

'export to PDF if a folder was selected
If myFile <> "False" Then
wsA.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& myFile
End If

exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler

End Sub

 

[rodl66]

Good morning,
John,

Forgive me for being a pain, I am still learning. My modified code is below. Now it is not saving anything; however, it is displaying the confirmation box that a file was created but is not displaying the name of the file nor is it actually creating the file. The folder is empty.

Thank you kindly!

Sub PDFActiveSheetCkIn()

'for Excel 2010 and later
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant

On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "ddmmmyyyy-hhmmss")

'get active workbook folder, if saved
strPath = "C:\PIO\ECEAT\Daily Files\EntryCL\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile

wsA.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

'confirmation message with file info
MsgBox "PDF file has been created: " _
& vbCrLf _
& myFile

exitHandler:
Exit Sub

errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler

End Sub

 

[rodl66]

Well, I am not sure what I did, but it is now working correctly. Here's the code that works as I wanted it.

Sub PDFActiveSheetCkIn()

'for Excel 2010 and later

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant

On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd_hhmmss")

'get active workbook folder, if saved

strPath = "C:\PIO\ECEAT\Daily Files\EntryCL\"

'replace spaces and periods in sheet name

strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file

strFile = strName & "_" & strTime & ".pdf"
strPathFile = strPath & strFile

'use can enter name and select folder for file

myFile = Application.GetSaveAsFilename _
(InitialFileName:=strPathFile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and FileName to save")

'export to PDF if a folder was selected

If myFile <> "False" Then
wsA.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=myFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

'confirmation message with file info

MsgBox "PDF file has been created: " _
& vbCrLf _
& myFile
End If

exitHandler:
Exit Sub
errHandler:
MsgBox "Could not create PDF file"
Resume exitHandler

End Sub

 

[John_w]

Now it is not saving anything; however, it is displaying the confirmation box that a file was created but is not displaying the name of the file nor is it actually creating the file. The folder is empty.

Because this line is expecting the full file name to be in the variable myFile:

wsA.ExportAsFixedFormat Type:=xlTypePDF, _
           Filename:=myFile, _

To fix, change the prior line:

strPathFile = strPath & strFile

to:

myFile = strPath & strFile

Well, I am not sure what I did, but it is now working correctly. Here's the code that works as I wanted it.

I thought you didn't want it to prompt for a file name, which this line is doing:

myFile = Application.GetSaveAsFilename _

 

[rodl66]

Because this line is expecting the full file name to be in the variable myFile:

wsA.ExportAsFixedFormat Type:=xlTypePDF, _
           Filename:=myFile, _

To fix, change the prior line:

strPathFile = strPath & strFile

to:

myFile = strPath & strFile

I thought you didn't want it to prompt for a file name, which this line is doing:

myFile = Application.GetSaveAsFilename _

John,

True, but it's been decided to leave it in. Thank you for the other info as well.