This code starts with a purchase order already existing in the workbook. The workbook is copied to a new the workbook with a new name to .xlsx and .pdf format.
Everything is working except the DIR function where it checks to see if the new workbook already exists. I tested it where I had a file where a workbook did exist. The code is supposed to then end, but instead VBA code overwrites the existing file and I can't figure out why. Help! (see part of code that starts with "Check if file already exists....")
I am attaching the workbook without the code, and pasting the code below.
CODE
Sub CreatePO_PDF()
'What this routine does
'1) Create a copy of the PO sheet in a new workbook
'2) Save the new workbook to the specified PO folder with the proper filename
'3) Close the new PO file
'4) Display a message box with the next purchase order (PO) no
'5) Update the PO number on the template
'6) Save updates to PO template
Dim path As String
Dim PO_No As Long
Dim fname As String
Dim printAreaAddress As String
Dim printArea As Range
Dim originalSheet As Worksheet
Dim RKI As String
Dim fnameExists 'use this to store the filename if it already exists
path = "C:\Users\Susan\Work\vba\" ' MAKE SURE TO INCLUDE the last backslash prior to end quotes
PO_No = Range("D3") ' D3 contains the Purchase Order number
RKI = "RKI"
fname = PO_No & " - " & Range("D5") & "-" & RKI
' D5 is a company name. The new filename will be a concatenation of the PO no and company name
Application.DisplayAlerts = False ' This suppresses the question that pops up asking whether or not the user wants to save as xlsx rather than xslm causing it to default to "Yes," save as xlsx file.
' Store the reference to the original sheet
Set originalSheet = Sheet1
' Create a copy of the original sheet to a new workbook
originalSheet.Copy
' Save the reference to the newly created workbook
Dim newWorkbook As Workbook
Set newWorkbook = ActiveWorkbook
' Delete the command button from the newly created sheet
' newWorkbook.ActiveSheet.Shapes("CommandButton1").Delete
'
' Check if the file already exists.
' The Dir() function returns an empty string if the file doesn't exist and returns the file name if it does
fnameExists = path & fname
fnameExists = Dir(fnameExists)
If fnameExists <> "" Then
MsgBox "A file with the same name already exists in the specified directory."
Exit Sub
Else
'Save the new workbook as .xlsx
newWorkbook.SaveAs Filename:=path & fname, FileFormat:=51
MsgBox "Workbook saved successfully."
End If
' Set the print area to the original sheet
printAreaAddress = originalSheet.PageSetup.printArea
' Debug statements to check print area
If Not printAreaAddress = "" Then
Debug.Print "PrintArea is set: " & printAreaAddress
' Get the print area as a Range object
Set printArea = originalSheet.Range(printAreaAddress)
' Make the PDF from the pre-defined print area.
printArea.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=path & fname & ".pdf", _
Quality:=xlQualityStandard, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Else
Debug.Print "PrintArea is not set."
End If
' Close the new workbook
newWorkbook.Close
MsgBox "Your next PO number is " & PO_No + 1
Range("D3") = PO_No + 1
ThisWorkbook.Save ' saves the original .xlsm file with the incremental PO number
Application.DisplayAlerts = True
End Sub
CODE