Saving Workbook as .pdf & .xlsm with specific path and filename, FileFormat:=51 no longer works

inBizness4u

New Member
Joined
Mar 25, 2019
Messages
1
This VBA Code below worked for several years and all of a sudden it stops at the line I highlighted in BOLD (ending with FileFormat:=51. This Purchase Order Form with VBA Code saves my PO's in .pdf & .xlsm and adds a line to my log tab, then clears the form. It works on my computer at home which has Microsoft Office 2010 but does not work at work where my computer is Office 365 with Windows 10. I am very happy to share this with anyone that can figure out why it now longer works.

Sub POReport()

Dim myFile As String, lastRow As Long

'Saves the PO-Template in .pdf format with cells (G6), (F11) and (C6) & extention .pdf

myFile = "Desktop\OCS-POs" & "OCS-POs-" & Sheets("OCS-PO-Template").Range("G6") & "-" & Sheets("OCS-PO-Template").Range("F11") & "-" & Sheets("OCS-PO-Template").Range("C6") & ".pdf"

lastRow = Sheets("OCS-PO-Log").UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1

'Transfer data to Log
'Copy the information from the cells PO number (G6), Project Name (F11) and Contractor (C6) to the log

Sheets("OCS-PO-Log").Cells(lastRow, 1) = Now
Sheets("OCS-PO-Log").Cells(lastRow, 2) = Sheets("OCS-PO-Template").Range("F6")
Sheets("OCS-PO-Log").Cells(lastRow, 3) = Sheets("OCS-PO-Template").Range("G6")
Sheets("OCS-PO-Log").Cells(lastRow, 4) = Sheets("OCS-PO-Template").Range("C14")
Sheets("OCS-PO-Log").Cells(lastRow, 5) = Sheets("OCS-PO-Template").Range("F11")
Sheets("OCS-PO-Log").Cells(lastRow, 6) = Sheets("OCS-PO-Template").Range("F10")
Sheets("OCS-PO-Log").Cells(lastRow, 7) = Sheets("OCS-PO-Template").Range("C6")
Sheets("OCS-PO-Log").Cells(lastRow, 8) = Sheets("OCS-PO-Template").Range("C7")
Sheets("OCS-PO-Log").Cells(lastRow, 9) = Sheets("OCS-PO-Template").Range("G32")

Sheets("OCS-PO-Log").Hyperlinks.Add Anchor:=Sheets("OCS-PO-Log").Cells(lastRow, 10), Address:=myFile, TextToDisplay:=myFile

'Create PO-Template in .pdf format
'When the .pdf copy is saved, it needs to select and to save only the PO-Log that are equal to the number of issues

Application.DisplayAlerts = False

ActiveWorkbook.ActiveSheet.SaveAs "Desktop\OCS-POs" & "OCS-POs-" & Sheets("OCS-PO-Template").Range("G6") & "-" & Sheets("OCS-PO-Template").Range("F11") & "-" & Sheets("OCS-PO-Template").Range("C6") & ".xlsx", FileFormat:=51

ActiveWorkbook.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFile
Application.DisplayAlerts = True

'The cells are cleared
Range("B16:F30") = ""
Range("F10:F12") = ""
Range("C7") = ""
Range("C31") = ""
Range("B32") = ""

ActiveWorkbook.SaveAs Filename:= _
"Desktop\OCS-PO-Template.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Application.DisplayAlerts = True

'This allows the PO-Template to change PO number when clicked

Range("G6").Value = Range("G6").Value + 1

ActiveWorkbook.Save

End Sub
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
Any error messages?
Turn alerts on while problem is ssolved. You are probably missing some important messages.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,328
Members
409,863
Latest member
stacy09
Top