VBA to open a PDF in another location.

[IannW]

Hi All

I have the following code that searches a network folder for a PDF doc based on the number in a cell and opens it if one exists:

Sub OpenPDF_3()

Dim strPath As String
Dim strFileName As String

strPath = "\\172.27.154.13\D$\Excel Dashboard\Inventory"
If Right(strPath, 1) <> "" Then
strPath = strPath & ""
End If

strFileName = Worksheets("Home").Range("AW15").Value & " New Store Inventory" & ".pdf"

If Len(Dir(strPath & strFileName)) > 0 Then
ActiveWorkbook.FollowHyperlink strPath & strFileName
Else
MsgBox "There is no Inventory available for this store!", vbExclamation
End If

End Sub

This works great if the pdf is named '333 New Store Inventory', 333 being the number that i enter in cell AW15 before clicking the button that runs this code.

However, if the doc is saved as '0333 New Store Inventory' because we often use 4 digits, it wont open.

How can I ignore any leading zeros and have it open weather its called 333 or 0333.

Probably simple to a vba genius but not a newbie like me.

Thanks in advance.

 

[Gerald Higgins]

I'm not a code expert, there may be better ways of doing this, and this might not work correctly for all your different scenarios, but it seems to work for the scenario you have described . . .

strFileName = Worksheets("Home").Range("AW15").Value + 0 & " New Store Inventory" & ".pdf"

 

[IannW]

Hi

Thanks for quick response. Would this work if there was more than 1 zero, ie 0023?

 

[IannW]

Just tried it, doesn't seem to work.

Sub OpenPDF_3()


Dim strPath As String
Dim strFileName As String

strPath = "\\172.27.154.13\D$\Excel Dashboard\Inventory"
If Right(strPath, 1) <> "" Then
strPath = strPath & ""
End If

strFileName = Worksheets("Home").Range("AW15").Value + 0 & " New Store Inventory" & ".pdf"

If Len(Dir(strPath & strFileName)) > 0 Then
ActiveWorkbook.FollowHyperlink strPath & strFileName
Else
MsgBox "There is no Inventory available for this store!", vbExclamation
End If

End Sub

 

[Gerald Higgins]

Sorry, I did it the wrong way round, I was assuming the file would always be called "333 New Store . . ."

But you mean something different don't you ?

The file may be called "0333 New Store . . . "
and the user may input 333 into AW15.

Leave it with me.

 

[Gerald Higgins]

Sorry, this one is beyond my limited VBA skills.

I can think of how the code would be structured, but can't quite get the exact syntax right.

Any code experts out there who can help ?

 

[IannW]

333 New Store.... works great with my original code, but really we have always used a 4 digit code as some stores are named as 9453 for example. People complete an excel spreadsheet and click a button which converts to file to a pdf and saves it to the folder mentioned in the strpath. The cell with the store number is formatted to 0000, so always saves as a 4 digit, even if they enter 333, it saves as 0333, but my code above that calls the pdf doesn't like the 0. I could go through and change them all to 3 digits but would really like to leave at 4.