Dir function does nothing

[vincentchrisnata]

Hello,
I have a problem on the dir function, as i run the code, it does nothing. I want to prompt a message asking to overwrite file if the file already exists in the directory.

Thanks before.

Sub SavePDF_Cost()
    Dim DocPath As String
    Dim DefName As String
    Dim EmpID As String
    Dim DocName As String
    
    DocPath = "X:\Cost"
    DefName = "Cost."
    EmpID = Range("A2").Value
    DocName = DefName & EmpID

    If Dir(DocPath & DocName) <> vbNullString Then
        If MsgBox(DocName & " already exists!" & vbNewLine & "Do you want to overwrite it?", vbExclamation + vbYesNo) = vbYes Then
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=DocPath & DocName, Quality:=xlQualityStandard, _
                IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True
            MsgBox "The File has been saved!"
        End If
    End If
End Sub

 

[gordsky]

think you need another "\" at the end of your DocPath statement

 

[vincentchrisnata]

think you need another "\" at the end of your DocPath statement

I added "\", but still does nothing

Sub SavePDF_Cost()
    Dim DocPath As String
    Dim DefName As String
    Dim EmpID As String
    Dim DocName As String
    
    DocPath = "X:\Cost\"
    DefName = "Cost."
    EmpID = Range("A2").Value
    DocName = DefName & EmpID

    If Dir(DocPath & DocName) <> vbNullString Then
        If MsgBox(DocName & " already exists!" & vbNewLine & "Do you want to overwrite it?", vbExclamation + vbYesNo) = vbYes Then
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=DocPath & DocName, Quality:=xlQualityStandard, _
                IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True
            MsgBox "The File has been saved!"
        End If
    End If
End Sub

 

[gordsky]

Ive tested this twice with the additional "\"and it works fine. The only issue that it can be is where you reference range("A2"). What is the info stored in range("A2")

 

[vincentchrisnata]

Ive tested this twice with the additional "\"and it works fine. The only issue that it can be is where you reference range("A2"). What is the info stored in range("A2")

If Dir(DocPath & DocName) <> vbNullString Then

When i change "<>" to "=", the code can run. Maybe is it because it can't compare them?

 

[gordsky]

If Dir(DocPath & DocName) <> vbNullString Then

When i change "<>" to "=", the code can run. Maybe is it because it can't compare them?

You haven't answered what is in your cell a2.
In your first post the code finds existing files perfectly so the only reason changing <> to = would work if the file didn't exist in the first place

 

[vincentchrisnata]

M

You haven't answered what is in your cell a2.
In your first post the code finds existing files perfectly so the only reason changing <> to = would work if the file didn't exist in the first place

Cell A2 is correct, the employee ID
I tried to debug.print If Dir(DocPath & DocName) <> vbNullString . It always returns FALSE either the file exists or not

 

[RoryA]

Do you have a file extension on the end of docname?

 

[vincentchrisnata]

Do you have a file extension on the end of docname?

Yes actually I just found the solution as you mentioned
I added the file extensions ".pdf"

DocName = DefName & EmpID & ".pdf"

Thanks gordsky and RoryA for the responses

 

[gordsky]

Yes actually I just found the solution as you mentioned
I added the file extensions ".pdf"

DocName = DefName & EmpID & ".pdf"

Thanks gordsky and RoryA for the responses

Glad you solved it, thats why i asked in post#4 what was in A2. If it wasnt a file ext it was never going to find the file