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

[Andrew Carr]

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.

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

 

[Andrew Carr]

Update to above - it seems a bit of the text went awry during the post process.
The MapDrive statement should read

MsgBox "Map R: " & MapDrive("R:", _
"\\MyServer\MyShare$\Folder\Subfolder", _
"MyUserName", "MyPassword")

 

[Andrew Carr]

Update to above - it seems a bit of the text went awry during the post process.
The MapDrive statement should read

MsgBox "Map R: " & MapDrive("R:", _
"\\MyServer\MyShare$\Folder\Subfolder", _
"MyUserName", "MyPassword")

Well it did it again -
I'll attach an image