Accessing an Excel Workbook stored on a Remote Server with username and password protection

Andrew Carr

New Member
Joined
Sep 8, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I have a question which I thought had been answered form a post submitted back in 2010.

I am trying to access an Excel workbook stored on a remote server and extract the data from it to populate a spreadsheet that I have open in my desktop.
The spreadsheet that I currently have open is in the same location on the server as the file I am trying to access.

I thought that as both files were in the same folder on the network, that it would be a simple task. How foolish of me :)

I found this code, from an old post and gave it a whirl

Option Explicit

Private Const CONNECT_UPDATE_PROFILE = &H1
Private Const RESOURCE_CONNECTED As Long = &H1&
Private Const RESOURCE_GLOBALNET As Long = &H2&
Private Const RESOURCETYPE_DISK As Long = &H1&
Private Const RESOURCEDISPLAYTYPE_SHARE& = &H3
Private Const RESOURCEUSAGE_CONNECTABLE As Long = &H1&

Private Declare Function WNetCancelConnection2 Lib "mpr.dll" _
Alias "WNetCancelConnection2A" (ByVal lpName As String, ByVal dwFlags As Long, ByVal fForce As Long) As Long

Private Declare Function WNetAddConnection2 Lib "mpr.dll" _
Alias "WNetAddConnection2A" (lpNetResource As NETCONNECT, ByVal lpPassword As String, ByVal lpUserName As String, ByVal dwFlags As Long) As Long

Private Type NETCONNECT
dwScope As Long
dwType As Long
dwDisplayType As Long
dwUsage As Long
lpLocalName As String
lpRemoteName As String
lpComment As String
lpProvider As String
End Type

Public Function MapDrive(LocalDrive As String, _
RemoteDrive As String, Optional Username As String, _
Optional Password As String) As Boolean

' Example:
' MapDrive "Q:", "\\RemoteMachine\RemoteDirectory", "MyLoginName", "MyPassword"

Dim NetR As NETCONNECT

NetR.dwScope = RESOURCE_GLOBALNET
NetR.dwType = RESOURCETYPE_DISK
NetR.dwDisplayType = RESOURCEDISPLAYTYPE_SHARE
NetR.dwUsage = RESOURCEUSAGE_CONNECTABLE
NetR.lpLocalName = Left(LocalDrive, 1) & ":"
NetR.lpRemoteName = RemoteDrive

MapDrive = (WNetAddConnection2(NetR, Username, Password, _
CONNECT_UPDATE_PROFILE) = 0)

End Function

Public Function UnMapDrive(LocalDrive As String) As Boolean

' Example:
' MapDrive "Q:"

Dim NetR As NETCONNECT

NetR.dwScope = RESOURCE_GLOBALNET
NetR.dwType = RESOURCETYPE_DISK
NetR.dwDisplayType = RESOURCEDISPLAYTYPE_SHARE
NetR.dwUsage = RESOURCEUSAGE_CONNECTABLE
NetR.lpLocalName = Left(LocalDrive, 1) & ":"
NetR.lpRemoteName = ""

UnMapDrive = (WNetCancelConnection2(LocalDrive, _
CONNECT_UPDATE_PROFILE, False) = 0)

End Function

I updated the functions' declare statements with the PtrSafe Attribute to support 64-bit machines and mapped a drive using

MsgBox "Map R: " & MapDrive("R:", _
"\\myServerName\myShare$\Folder\Subfolder", _
"myUserName", "Password")

When I finished typing the MapDrive statement above, the word "file" was highlighted with the following compile error.

1631102062383.png


Any help (either fixing the compile error or trying some new code to access the remote server) would be greatly appreciated.
 

Attachments

  • 1631102152708.png
    1631102152708.png
    5.5 KB · Views: 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Forum statistics

Threads
1,143,677
Messages
5,720,260
Members
422,273
Latest member
linds75

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top