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?
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
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
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.
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.
Related Threads
?
?
?
?
?
Tech Support Guy
9.9M posts
860K members
Since 1998
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!