Solved How do I open file store in SharePoint from Access using VBA (1 Viewer)

urjudo

Member
Local time
Today, 06:50
Joined
Oct 9, 2020
Messages
67
Hi,
I have a Button named btnViewOrders in a frmOrder that is visible base on if there any document(s) associate with the OrderDate and OrderNo of the record. We used to saved those documents as PDF format in a network drive (Z drive). below is my code that works fine when the documents are save in the Z drive:
(in the Z drive, I have a folder named Orders, inside the Order folder I have a Current Year sub folder, inside the current year folder, I have date folders)

Code:
FormCurrent
Dim Holddate as string
Dim HoldYear as string
Dim stFileName as String

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

stFileName = Dir("Z:\Orders\" & HoldYear & "\" & Holddate & "\" & Me.OrderNo & "*pdf")

If Isnull(stFileName) or stFileName = "" then
   btnViewOrders.visible = False
Else
  btnViewOrders.visible = True
End if

But now, we want to move these documents from the Network drive (Z) to SharePoint. I changed the path to:
Code:
stFileName = "sharepointaddress/documents/Orders/" & HoldYear & "/" & Holddate & "/" & Me.OrderNo & "*.pdf"

**sharepoint address - - > it's the https (for some reason it's not allow me to type the link)

the problem is the If statement is not doing what it suppose to do and the btnViewOrders shows on all records. It seems doesn't check on the parameters. I couldn't figure out what I missed. I know "Dir" is for the system file, but since the SharePoint is not a system file so I can't use "Dir", does anyone know what I should use for the hyperlink to make it works or any better suggestion?

Thank you for your time
Code:
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 04:50
Joined
Oct 29, 2018
Messages
21,357
Also, try mapping a drive to the doc library and continue to use your code.
 

urjudo

Member
Local time
Today, 06:50
Joined
Oct 9, 2020
Messages
67
Also, try mapping a drive to the doc library and continue to use your code.
What do you mean mapping a drive to the doc library and how? we going to move everything to the cloud instead network drive. I can sync the the whole Order Folder from SharePoint to the file Explorer but how do I called it in my code? I did test my existing code, if I removed the "Dir" then the the code doesn't check the parameter, it seems with the "Dir" is the code runs correctly. Thanks
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 04:50
Joined
Mar 14, 2017
Messages
8,738
Go to your doc library online. Go to the files or browse tab near the upper left. Click open in explorer. Copy the address in the address bar after your regular Windows file Explorer window opens. You can then map that to a drive letter. If you can get all your users to do that and use a persistent mapping then you can just refer to that drive letter in your code like you would any other drive letter. If you can't get all your users to map it to the same letter, then you may have to go with either different users have a different mapping, or, create code that maps a drive letter for them behind the scenes on the fly and disconnects it at the end of the code run. Or use the link I provided if that works for you.
 

urjudo

Member
Local time
Today, 06:50
Joined
Oct 9, 2020
Messages
67
So I copy the Z drive to the shoarepoint and use the same name then sync it to the file explorer, could I be leave the code as it? Can the "Dir" for the file explorer too? Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:50
Joined
Oct 29, 2018
Messages
21,357
So I copy the Z drive to the shoarepoint and use the same name then sync it to the file explorer, could I be leave the code as it? Can the "Dir" for the file explorer too? Thanks
Did you try it? What happened?
 

Isaac

Lifelong Learner
Local time
Today, 04:50
Joined
Mar 14, 2017
Messages
8,738
I'm sorry, I'm having a little trouble following you. Go to your SharePoint document library online in the browser. Click the library tab, and then click open in explorer. At that point your Windows file Explorer should open. if you put your cursor in the address bar at the top, you can see what the full path is. you can put that information on copy, and then go to create a mapped drive as you normally would on your computer, pasting that which you have on "copy" into the address, when appropriate. after that point you can refer to the map to drive with a letter like you would anything else.
 

urjudo

Member
Local time
Today, 06:50
Joined
Oct 9, 2020
Messages
67
@Issac & the DBGuy.
I did it, it seems working but the only problem is when I open try to open the PDF, it pop up an security notice "A potential security concern has been identified" . Is anyway I can turn it off and just open the pdf directly?

Thanks bunch for both of your great advise!
 

urjudo

Member
Local time
Today, 06:50
Joined
Oct 9, 2020
Messages
67
I copied the Orderfolder to onedrive for test and it works. After the btnViewOrder visible , when click on it then it will open another form frmPDFview to open the pdf so on the frmPDFView, I place the code on FormOpen, below is the 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("C:\Users\" & Environ("UserName") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & Form_frmOrder.OrderNo & "*.pdf")

Me(1).Caption = stFileName
Me(1).HyperlinkAddress = "C:\Users\" & Environ("UserName") & "\OneDrive\Orders\" & HoldYear & "\" & Holddate & "\" & stFileName

ControlCount = 2

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

ControlCount = ControlCount+1
stFileName = Dir
Loop

End Sub

This is my code, I was able to click on it but it pop up the security warning as I mentioned on the last post. How do I turn it off?

Thanks
 

Isaac

Lifelong Learner
Local time
Today, 04:50
Joined
Mar 14, 2017
Messages
8,738
I don't use Hyperlinks at all, with the exception of the hack of putting a single space in a control's Hyperlink property, in order to result in a mouse hover-over hand. You just don't have enough control over the interface with them.

Check out:

 

urjudo

Member
Local time
Today, 06:50
Joined
Oct 9, 2020
Messages
67
The problem is when I was using the Network Drive, it was fine. once I move to OneDrive, that's when the security warning pop up. I'm using the same code as it was for the Network drive just change the location from Z drive to oneDrive
 

Isaac

Lifelong Learner
Local time
Today, 04:50
Joined
Mar 14, 2017
Messages
8,738
Possibly it's connected to an Internet Explorer security setting. (That may seem odd, but there are Internet Explorer security settings that have everything to do with how files behave--even just files that you open having nothing to do with IE).
 

urjudo

Member
Local time
Today, 06:50
Joined
Oct 9, 2020
Messages
67
That's what I don't understand, like I said, I use the same code which was working perfectly with the the network drive but once I changed the location from the network drive to the OneDrive then the warning message pop up. It's annoying that users have to click "Yes" every time to open the pdf and I don't want to mess up in the registry in the system and I have to changed on each PC, this is not the way I would like to do either.
 

Isaac

Lifelong Learner
Local time
Today, 04:50
Joined
Mar 14, 2017
Messages
8,738
That's what I don't understand, like I said, I use the same code which was working perfectly with the the network drive but once I changed the location from the network drive to the OneDrive then the warning message pop up. It's annoying that users have to click "Yes" every time to open the pdf and I don't want to mess up in the registry in the system and I have to changed on each PC, this is not the way I would like to do either.
But my point is, IE security settings might be coming into play because your computer views a Network drive, as different than it views a Onedrive (cloud-based) system. You have switched from one to the other. Different rules may apply and different settings may be acting.
 

urjudo

Member
Local time
Today, 06:50
Joined
Oct 9, 2020
Messages
67
Good point. but what shall I use to replace the HyperLink since it's cause the warning message?
 

Isaac

Lifelong Learner
Local time
Today, 04:50
Joined
Mar 14, 2017
Messages
8,738
Sorry I wasn't more clear.
1) Perhaps research the Internet Security setting that need to be changed
2) Perhaps use a different method, other than a Hyperlink. I already posted a link.
 

urjudo

Member
Local time
Today, 06:50
Joined
Oct 9, 2020
Messages
67
I added this to a module from a website that you provide, it still doesn't working

Code:
Option Explicit

Function killHyperlinkWarning()
    Dim oShell As Object
    Dim strReg As String

    strReg = "Software\Microsoft\Office\11.0\Common\Security\Dis ableHyperlinkWarning"

    Set oShell = CreateObject("Wscript.Shell")
    oShell.RegWrite "HKCU\" & strReg, 1, "REG_DWORD"
End Function
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 04:50
Joined
Mar 14, 2017
Messages
8,738
I very rarely execute code designed to edit a registry, so I should make it clear that I'm completely neutral as to whether you should or shouldn't run that code.

Having said that:
- You might have a space between Dis and able
- You probably would only want this to run once for each user+machine combination
- For your own purposes, or on any single machine, you could view the Immediate window in vba project, type killHyperlinkWarning and press enter
 

Users who are viewing this thread

Top Bottom