use Shell "Explorer.exe " to replace HyperlinkAdress in code

U

urjudo

Member
Local time
Today, 05:25
Joined
Oct 9, 2020
Messages
67
Hello,
Would like to use Shell "Explorer.exe " to replace HyperlinkAddress in order to open the document in File Explorer, but don't know how to use it in my coding because the HyperlinkAddress would pop up a security warning box and the Shell "Explorer.exe " will just open the document directly. Any help would be very appreciated!
Attached is the screen shot of the form, when users click on the View Order(s) then opens the form list all the pdf associate with that OrderNo , so that users know how many orders under this orderNo and they can select which one they want to view.
Here is my code
Code: 
Private Sub Form_Open(cancel As Integer)
    Dim Holddate as string
    Dim HoldYear as String
    Dim stFileName as String
    Dim ControlCount as Integer
    Dim GetControlName As String


Holddate = Format(Form_frmOrder.Orderdate,"mm") & "-" & Format(Form_frmOrder.Orderdate,"dd") & "-" & format(Form_frmOrder.Orderdate, "yy")
HoldYear = format(Form_frmOrder.Orderdate, "yyyy")

stFileName = Dir("Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & Form_frmOrder.OrderNo & "*.pdf")

Me(1).Caption = stFileName
Me(1).HyperlinkAddress = "Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName

ControlCount = 2

stFileName = Dir
Do Until stFileName = ""
Me(ControlCount).Caption = stFileName
Me(ControlCount).HyperlinkAddress = "Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName

ControlCount = ControlCount+1
stFileName = Dir
Loop

End Sub


Screenshot1.jpg


My main point is to avoid to click "Yes" every time in order to open the document. But can't figure where to place the Shell "Explorer.exe " with Me(1).HyperlinkAddress also use Shell "Explorer.exe " replace Me(ControlCount).HyperlinkAddress . I did try Shell "Explorer.exe " & Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName but it opens all the pdf s at the same time which is not what I want.

Thank you,
 
Last edited:
urjudo said:
I did try Shell "Explorer.exe " & Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName but it opens all the pdf s at the same time which is not what I want.
Click to expand...
Is that because you're using a loop?
 
urjudo said:
Hello,
Would like to use Shell "Explorer.exe " to replace HyperlinkAddress in order to open the document in File Explorer, but don't know how to use it in my coding because the HyperlinkAddress would pop up a security warning box and the Shell "Explorer.exe " will just open the document directly. Any help would be very appreciated!
Attached is the screen shot of the form, when users click on the View Order(s) then opens the form list all the pdf associate with that OrderNo , so that users know how many orders under this orderNo and they can select which one they want to view.
Here is my code
Code: 
Private Sub Form_Open(cancel As Integer)
    Dim Holddate as string
    Dim HoldYear as String
    Dim stFileName as String
    Dim ControlCount as Integer
    Dim GetControlName As String


Holddate = Format(Form_frmOrder.Orderdate,"mm") & "-" & Format(Form_frmOrder.Orderdate,"dd") & "-" & format(Form_frmOrder.Orderdate, "yy")
HoldYear = format(Form_frmOrder.Orderdate, "yyyy")

stFileName = Dir("Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & Form_frmOrder.OrderNo & "*.pdf")

Me(1).Caption = stFileName
Me(1).HyperlinkAddress = "Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName

ControlCount = 2

stFileName = Dir
Do Until stFileName = ""
Me(ControlCount).Caption = stFileName
Me(ControlCount).HyperlinkAddress = "Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName

ControlCount = ControlCount+1
stFileName = Dir
Loop

End Sub


View attachment 86358

My main point is to avoid to click "Yes" every time in order to open the document. But can't figure where to place the Shell "Explorer.exe " with Me(1).HyperlinkAddress also use Shell "Explorer.exe " replace Me(ControlCount).HyperlinkAddress . I did try Shell "Explorer.exe " & Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName but it opens all the pdf s at the same time which is not what I want.

Thank you,
Click to expand...

Your strfilename:
stFileName = Dir("Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & Form_frmOrder.OrderNo & "*.pdf")

.... is designed to include all PDFs at once. You should change that to open just the correct file.
 
Last edited:
save the path+filename to an Array variable.
Code: 
Option Compare Database
Option Explicit

Dim arrFiles(1 To 500) As String

Private Sub Form_Open(cancel As Integer)
    Dim Holddate As String
    Dim HoldYear As String
    Dim stFileName As String
    Dim ControlCount As Integer
    Dim GetControlName As String


Holddate = Format(Form_frmOrder.Orderdate, "mm") & "-" & Format(Form_frmOrder.Orderdate, "dd") & "-" & Format(Form_frmOrder.Orderdate, "yy")
HoldYear = Format(Form_frmOrder.Orderdate, "yyyy")

stFileName = Dir(Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & Form_frmOrder.OrderNo & "*.pdf")

Me(1).Caption = stFileName
'Me(1).HyperlinkAddress = "Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName
arrFiles(1) = Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName

ControlCount = 2

stFileName = Dir
Do Until stFileName = ""
Me(ControlCount).Caption = stFileName
'Me(ControlCount).HyperlinkAddress = "Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName
arrFiles(ControlCount) = Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName
ControlCount = ControlCount + 1
stFileName = Dir
Loop

ReDim Preserve arrFiles(1 To ControlCount - 1)

End Sub

to open file:
Code: 
Application.FollowHyperlink arrFiles(Val(Screen.ActiveControl.Name))
 
@arnelgp,
I added the code as your replied, I got an error "Compile error: Array already dimensioned" on the
ReDim Preserve arrFiles(1 To ControlCount - 1) line. also where I place the "Application.FollowHyperlink arrFiles(Val(Screen.ActiveControl.Name))"

Thanks
 
I don't use the hyperlink data type since it is not compatible with SQL Server. Instead I just use the path to the document. but that shouldn't affect my suggestion. I think this is what arne is suggesting also.

I use the FollowHyperlink method
Code: 
Application.FollowHyperlink Me.txtFullDocName, , True

This method does not require you to know the type of document or to know the path to the software that will open it. It opens web pages, word, excel, powerpoint, etc. It does however require that the associated application be defined in Windows.
 
urjudo said:
@arnelgp,
I added the code as your replied, I got an error "Compile error: Array already dimensioned" on the
ReDim Preserve arrFiles(1 To ControlCount - 1) line. also where I place the "Application.FollowHyperlink arrFiles(Val(Screen.ActiveControl.Name))"

Thanks
Click to expand...
To use Redim Preserve, you can't have already specified explicit bounds in the earlier Dim declaration.
 
Last edited:
here is a new code:
Code: 
Dim arrFiles() As String

Private Sub Form_Open(cancel As Integer)
    Dim Holddate As String
    Dim HoldYear As String
    Dim stFileName As String
    Dim ControlCount As Integer
    Dim GetControlName As String

ReDim arrFiles(1 To 500)

Holddate = Format(Form_frmOrder.Orderdate, "mm") & "-" & Format(Form_frmOrder.Orderdate, "dd") & "-" & Format(Form_frmOrder.Orderdate, "yy")
HoldYear = Format(Form_frmOrder.Orderdate, "yyyy")

stFileName = Dir(Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & Form_frmOrder.OrderNo & "*.pdf")

Me(1).Caption = stFileName
'Me(1).HyperlinkAddress = "Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName
arrFiles(1) = Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName

ControlCount = 2

stFileName = Dir
Do Until stFileName = ""
Me(ControlCount).Caption = stFileName
'Me(ControlCount).HyperlinkAddress = "Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName
arrFiles(ControlCount) = Environ("Userprofile") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName
ControlCount = ControlCount + 1
stFileName = Dir
Loop

ReDim Preserve arrFiles(1 To ControlCount - 1)

End Sub
 
You must log in or register to reply here.

Similar threads

T
Solved Attache files from Subform in main form to Outlook
Replies
11
Views
820
theinviter
T
D
Convert acrobat 9.0 PDF to Image .PNG Using VBA Code
2
Replies
22
Views
1,112
D_Walla
D
Gasman
Solved Open Excel workbook, but have WorkBook_Open code run
Replies
19
Views
1,493
Gasman
Gasman
U
Solved How do I open file store in SharePoint from Access using VBA
Replies
19
Views
5,342
Isaac
Isaac
K
How can I add this code to the storage location of my file before scanning and then convert it to a PDF file?
2
Replies
25
Views
2,385
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom