use Shell "Explorer.exe " to replace HyperlinkAdress in code (1 Viewer)

urjudo

Member
Local time
Today, 14:39
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:

theDBguy

I’m here to help
Staff member
Local time
Today, 12:39
Joined
Oct 29, 2018
Messages
14,939
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.
Is that because you're using a loop?
 

Isaac

Lifelong Learner
Local time
Today, 12:39
Joined
Mar 14, 2017
Messages
4,825
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,

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:

arnelgp

error reading drive A:
Local time
Tomorrow, 03:39
Joined
May 7, 2009
Messages
12,380
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))
 

urjudo

Member
Local time
Today, 14:39
Joined
Oct 9, 2020
Messages
67
@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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:39
Joined
Feb 19, 2002
Messages
31,764
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.
 

Isaac

Lifelong Learner
Local time
Today, 12:39
Joined
Mar 14, 2017
Messages
4,825
@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
To use Redim Preserve, you can't have already specified explicit bounds in the earlier Dim declaration.
 
Last edited:

arnelgp

error reading drive A:
Local time
Tomorrow, 03:39
Joined
May 7, 2009
Messages
12,380
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
 

Users who are viewing this thread

Top Bottom