VBA Dir() command help

[TheVulcan]

I am trying to check if a .pdf exists before attaching it to an e-mail.

The location and name of the .pdf will be different each time so I can't hard code it.

Code below has been simplified for illustration purpose.



THIS WORKS

If [COLOR=blue][B]Dir(C:\Some\File\Path\my.pdf)[/B] [/COLOR]<> "" Then
 
MsgBox "FILE FOUND"

Else

MsgBox "## error - File does not exist ##" 

End If

THIS WORKS

[B][COLOR=red]myfile = "C:\Some\File\Path\my.pdf"[/COLOR][/B]

If [B][COLOR=blue]Dir(myfile)[/COLOR][/B] <> "" Then
 
MsgBox "FILE FOUND"

Else

MsgBox "## error - File does not exist ##" 

End If

But as I can't hard code it, I need to pass the Path and file Name to it.

THIS DOESN'T WORK

Path = ActiveWorkbook.Path
Name = ActiveCell.Value

[B][COLOR=red]myfile = Path & Name[/COLOR][/B]

If[COLOR=blue] [B]Dir(myfile)[/B][/COLOR] <> "" Then
  
MsgBox "FILE FOUND"
 
Else
 
MsgBox "## error - File does not exist ##" 
 
 End If

In the version above that doesn't work.
Stepping through the code shows that Dir(myfile) has the same value as the one that does work.

Dim myfile As String has no effect.


Given that Dir(myfile) has the exact same value in the one that doesn't work and the one that does work.
I don't know why it isn't working.



Vulcan.





HOW I HAVE TESTED IT

Path = ActiveWorkbook.Path
Name = ActiveCell.Value

[B][COLOR=red]myfile = Path & Name[/COLOR][/B]
[B][COLOR=red]myfile = "C:\Some\File\Path\my.pdf"[/COLOR][/B]

If[COLOR=blue] [B]Dir(myfile)[/B][/COLOR] <> "" Then
  
MsgBox "FILE FOUND" _
& Chr(10) _
& [B][COLOR=red]myfile [/COLOR][/B] _
& Chr(10) _
& [B][COLOR=red]Path & Name[/COLOR][/B]

Else
 
MsgBox "## error - File does not exist ##" 
 
 End If

Message Box for above.

FILE FOUND
"C:\Some\File\Path\my.pdf"
"C:\Some\File\Path\my.pdf"

 

[Norie]

ActiveWorkbook.Path returns the path without the back slash (path separator) at the end.

So in the code that doesn't work you are looking for a file called Pathmy.pdf, in a folder below the folder the workbook is saved in (C:\Some\File\).

 

[TheVulcan]

Thanks, as mentioned I just simplified it to make it easy to read.

As shown by my message box out put above.
Both come out exactly the same.


My real paths are:

If Dir("C:\Users\Munee\Desktop\WIP\Quote Pad v1.0\pdf\Quote Pad v1.pdf") <> "" Then

FilePath = ActiveWorkbook.Path &[B] "\pdf\"[/B]
Sheets("Quote Pad").Select
ActiveSheet.Range("AD17").Select
FileName = ActiveCell.Value

TheOne = FilePath & FileName

Both of these spit out the exact same value in a text box.

But only the hard coded one works in Dir()

 

[TheVulcan]

I haven't solved it yet; but I believe that the error is not in the code below, but in the cell it is collecting the FileName from.


Cell =R5&AN17&E15&AN17&P16&".pdf"

Even though it out put looks identical I'm not sure it is.
When I just hard coded the name in the cell it worked.


Going to play with it a bit....


In the code below you can see where I tried FileFolderExists and fso.FileExists in an atempt to get it to work.

Public Sub CommandButton1_Click()


Application.ScreenUpdating = False
     

FilePath = ActiveWorkbook.Path & "\pdf\"
Sheets("Quote Pad").Select
ActiveSheet.Range("AD17").Select
FileName = ActiveCell.Value


Dim TheOne As String
TheOne = FilePath & FileName

[COLOR=Green]' TheOne = "C:\Users\Munee\Desktop\WIP\Quote Pad v1.0\pdf\Quote Pad v1.pdf"[/COLOR]



[COLOR=Green]' If FileFolderExists("C:\Users\Munee\Desktop\WIP\Quote Pad v1.0\pdf\Quote Pad v1.pdf") Then[/COLOR]
 
[COLOR=Green]' Set fso = CreateObject("Scripting.FileSystemObject")
' If fso.FileExists("C:\Users\Munee\Desktop\WIP\Quote Pad v1.0\pdf\Quote Pad v1.pdf") Then[/COLOR]


If Dir(TheOne) <> "" Then
 
Stop

MsgBox "FILE FOUND" _
& Chr(10) _
& "C:\Users\Munee\Desktop\WIP\Quote Pad v1.0\pdf\Quote Pad v1.pdf" _
& Chr(10) _
& TheOne

Else

Stop

MsgBox "## error - File does not exist ##" _
& Chr(10) _
& "C:\Users\Munee\Desktop\WIP\Quote Pad v1.0\pdf\Quote Pad v1.pdf" _
& Chr(10) _
& TheOne

End If


Application.ScreenUpdating = True


End Sub

 

[Norie]

A problem in the data is the first place to look.

I actually assumed you had.

The 2nd place to look is the code for getting that data.

Is that actually working properly?

Is the right cell on the right worksheet being looked at?

 

[TheVulcan]

Yeah its working now thanks.......

I can't believe how much time I wasted on that one stupid line.
It was way more than I care to admit.

I still for the life of me dont know why it wasn't working.

When the value showed in VB it was what it was supposed to be.
And the message box showed identical values for the String and " ".

I even tried checking on different .pdfs changing the name in the hard code each time.

In the end I got so frustrated I was trying anything.

Dir(myfile.value)
Dir('myfile)
Dir('myfile')
Dir myfile
Dir(& myfile)
Dir(""myfile"")


I knew it had to be something simple that I was over looking.
Just didn't expect it to be that the code was right the first time.

 

[Norie]

Which line of code are you referring too?

None of your previous posts seemed to be indicating the problem was solved or mentioned a specific line of code.

Did I miss something?