Tech Support Guy banner
Status
Not open for further replies.

Telling excel to go to a specific page in PDF on a FLASH drive

1K views 4 replies 3 participants last post by  Rollin_Again 
#1 ·
Hi everyone:

I am new to VB. I have a code that goes from Excel to a specific page in Adobe (which ends up opening it in IE). Problem is that I need it to be able to go on different computers so I am trying to figure out how to tell VB to get the file from the USB flash drive. Every flash drive comes up different on every computer.

Here is my code:


Sub TestPDFlink()
Dim IE As Object, strFile As String, iPageNum As Long
strFile = "F:\folder\nameoffile.pdf"
iPageNum = 125
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate strFile & "#Page=" & iPageNum
IE.Visible = True
End Sub


So, where do I tell it to go to whichever drive the flash drive comes up as (E:, F:, etc)? Any ideas?

Thanks!
 
See less See more
#2 ·
This should work

Code:
Sub TestPDFlink()

On Error GoTo ErrorHandle

Dim IE As Object, strFile As String, iPageNum As Long
strFile = "\folder\nameoffile.pdf"

For i = 97 To 122
If Dir(UCase(Chr(i)) & ":" & strFile) <> "" Then
vFound = True
Exit For
End If
Next i

If vFound <> True Then
MsgBox ("FILE NOT FOUND")
End
Else
iPageNum = 125
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate UCase(Chr(i)) & ":" & strFile & "#Page=" & iPageNum
IE.Visible = True
End If
End

ErrorHandle:
If Err.Number = 52 Then
i = i + 1
Resume
End If

End Sub
Regards,
Rollin
 
#3 ·
If the code I posted above doesn't work you should also be able to do this by evaluating the flash drive's volume name. When I plug in my flash drive to my PC it appears in Windows Explorer with a drive volume name of "KINGSTON" so you should be able to use code to loop through all the PC's drives until it find the one with your specific drive name before executing the code. Just change the portions in red to reflect your correct file name and drive volume name

Code:
Private Declare Function GetVolumeInformation Lib "kernel32" _
Alias "GetVolumeInformationA" (ByVal lpRootPathName As String, _
ByVal lpVolumeNameBuffer As String, ByVal nVolumeNameSize As _
Long, lpVolumeSerialNumber As Long, lpMaximumComponentLength _
As Long, lpFileSystemFlags As Long, ByVal _
lpFileSystemNameBuffer As String, ByVal nFileSystemNameSize _
As Long) As Long

Sub TestPDFlink()

Dim IE As Object, strFile As String, _
iPageNum As Long, sBuffer As String
sBuffer = Space$(255)

strFile = "[COLOR="Red"]\folder\nameoffile.pdf[/COLOR]"

For j = 97 To 122

vTest = VolumeName(UCase(Chr(j)) & ":")

If VolumeName(UCase(Chr(j)) & ":") = "[COLOR="Red"]KINGSTON[/COLOR]" Then
vFound = True
Exit For
End If

Next j

If vFound = True Then
iPageNum = 125
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate UCase(Chr(j)) & ":" & strFile & "#Page=" & iPageNum
IE.Visible = True
Else
MsgBox ("FILE NOT FOUND")

End If
End Sub

Public Function VolumeName(Drive As String)
Dim sBuffer As String
sBuffer = Space$(255)

If GetVolumeInformation(Drive, sBuffer, Len(sBuffer), 0, 0, 0, Space$(255), 255) <> 0 Then
    VolumeName = Left$(sBuffer, InStr(sBuffer, Chr$(0)) - 1)
End If

End Function
Regards,
Rollin
 
#4 ·
Potential problems with the code: what about mapped drives? What if there are more than one drive with the same name? I received an email from Rachel on this. I was thinking an API to pick the drive, especially since, if used on multiple computers, you'll probably have a different drive letter assigned to it. Dunno, an option.
 
#5 ·
Who is Rachel? Is that the original poster? I agree that the solutions that I posted are not the most ideal but I still think that the first bit of code I posted would work most of the time since it uses only the drive letter and file path/name when trying to locate and open the file. The only potential problem that I see with it is if the same file/directory structure exists on multiple drives on the same machine. If this happened, the code would have no way of knowing which is the correct file to open and would open the first file it finds with a maching name and directory path regardless of it's drive letter. As far as the second bit of code that I posted, I think the chances are pretty slim that the average user would coincidently already have a drive with the same volume name that Windows would assign to the flash drive on it's own. I agree that the second solution would not work on mapped drives but this shouldn't be an issue since the original poster has indicated that the file would reside on the flash drive and not a mapped network drive.


Regards,
Rollin
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top