Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

DIR function not working as expected

0

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

Answer
Discuss

Answers

0
Selected Answer

Hello Susan,

Your code was close and only needed a couple of small tweaks. I downloaded your file and saved it as " .xlsm " and added a module with the revised code. I also include comments to point out the changes and what can be removed/deleted. I tested it twice and it worked as expected.

Updated Aug. 17 (to show changes)

My recommended changes:

' +++++++  REMOVE THE FOLLOWING BLOCK  ++++++++++++++++++++++++++
'   the following is not required as the print area of the original sheet is retained when copied
'    ' 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.
        
'   the following throws an error
        printArea.ExportAsFixedFormat _
 
            Type:=xlTypePDF, _
 
            Filename:=path & fname & ".pdf", _
 
            Quality:=xlQualityStandard, _
 
            IgnorePrintAreas:=False, _
 
            OpenAfterPublish:=False
' +++++++  REMOVE THE ABOVE BLOCK  ++++++++++++++++++++++++++
 
 
'   use the following to save as PDF
fname = PO_No & " - " & Range("D5") & "-" & RKI
Dim saveLocation As String
saveLocation = path & fname
 
Dim rng As Range
Set rng = Sheets("Sheet1").Range("A1:D22")
 
rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation
 
 
 
' +++++++  REMOVE THE FOLLOWING BLOCK  ++++++++++++++++++++++++++
'    Else
 
'        Debug.Print "PrintArea is not set."
 
'    End If
' +++++++  REMOVE THE ABOVE BLOCK  ++++++++++++++++++++++++++

If this solves your issue, please mark the answer as selected.

Discuss

Discussion

@Willie - good work, I think! Given the aim of this site is to Teach Excel, I think it's better if you show the code corrections on your Answer (not just in a file), better yet with some explanation. It's more effort for us I know but better for others users I think. 
John_Ru (rep: 6297) Aug 17, '23 at 6:30 pm
@Willie  thank you for the time, however your code changes did not address my question.
1.  My code was working as submitted and I don't believe the changes you provided are necessary.
2.  The code still OVERWRITES an existing file.  The problem seems to lie in the following lines of code where the DIR function is not finding the existing file.  That is where my problem lies.  That is where I need help and learning.

CODE
......

  ' 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 .....

CODE
SusanUser (rep: 20) Aug 17, '23 at 6:58 pm
@Susan,

This is puzzling because I did not experience that problem. I am also puzzled how your code runs even though it throws an error.

As for the changes recommended, these were necessary because it would not run due to the line beginning "printArea.ExportAsFixedFormat _" throws the error:  "Compile error:   Arguement not optional" (as per comments in code)
And in the first block of code to remove -- "the following is not required as the print area of the original sheet is retained when copied" (as per comments in the code)

I don't have the time at the moment, but give em a couple of days and I will play around with it to see if I can find anything.

Cheers   :-)
WillieD24 (rep: 567) Aug 17, '23 at 9:56 pm
@John

I have added the changes to my original answer.
WillieD24 (rep: 567) Aug 17, '23 at 10:06 pm
@WillieD24

This is indeed perplexing that is runs on my end.  I wish I could send your original file.  I am running on Windows 11, Microsoft Office Home and Business 2021.

Can you address the DIR problem?  Did you try on your end, with your changes, to see if the code overwrites an existing file?  Thanks in advance.
SusanUser (rep: 20) Aug 18, '23 at 12:09 pm
@Susan,
My systems are Win10 - Excel 2007 and Win10 Excel 2016. I have my VB Editor configured as "Option Explicit" which forces all variables to be declared and flags any errors.
The overwriting is fixed by changing:
fname = PO_No & " - " & Range("D5") & "-" & RKI

to the following: (addition of file extension)
fname = PO_No & " - " & Range("D5") & "-" & RKI & ".xlsx"


Also add the following after the line "use the following to save as PDF"
fname = PO_No & " - " & Range("D5") & "-" & RKI

Without this line the PDF file will end in ".xlsx.pdf"

I still stand by my previous recommended changes.

Cheers   :-)
WillieD24 (rep: 567) Aug 18, '23 at 12:22 pm
@ Willie-  thanks for showing the code. Your file works good for new pdfs but I think it needs these two changes (in bold in the extract below) to fix the issue:
    ' 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
    ' but must include the file extension
    fnameExists = path & fname & ".xlsx"
 
    fnameExists = Dir(fnameExists)
 
    If fnameExists <> "" Then
        'close the file
        newWorkbook.Close
 
        MsgBox "A file with the same name already exists in the specified directory."
 
        Exit Sub
 
    Else
 

Otherwise it doesn't specify the original workbook and starts creating the next copy workbook / pdf.

If so, please correct your answer and file.

@Sue - hope this fixes your problem and allows you to mark Willie's Answer as Selected (when working) from the copy workbook (still active).
John_Ru (rep: 6297) Aug 18, '23 at 12:22 pm
Looks like our posts were made at much the same time!
John_Ru (rep: 6297) Aug 18, '23 at 12:25 pm
@Susan, @John

I made these latest changes to the file in my answer (above).
And, Yes, I agree we were posting at the same time.   ;-)

Cheers   :-)
WillieD24 (rep: 567) Aug 18, '23 at 12:36 pm
@Willie - great; haven't opened but better if it's called Rev2 really. 
John_Ru (rep: 6297) Aug 18, '23 at 1:02 pm
@John

Done
WillieD24 (rep: 567) Aug 18, '23 at 2:16 pm
Ahhh, @WillieD24, I should have trusted you yesterday!!!  I used all of your suggested revisions and wouldn't you know, they worked!!!  Thank you very much.  I have so much to learn and a lot of it has to do with typos and syntax and missing periods and commas, etc.!!!  Thank you again.
SusanUser (rep: 20) Aug 18, '23 at 4:15 pm
Glad I was able to help you and thank you for selecting my answer. VBA is very much black & white - the syntax is either correct, or it's not. Even to this day I am sometimes tripped up by minor syntax errors that have me pulling my hair out (what's left LOL)
WillieD24 (rep: 567) Aug 19, '23 at 12:44 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login