Connect to server using vba

[Hooi]

Is there any way to connect to server using vba code because i was retrieving the data from server...

IP = 192.168.1.18
Username = administrator
password = 549221

 

[wsealey]

Although I have copied and pasted the code into a new module, when I execute it with a subroutine using the example that is embedded into the code (obviously substituting appropriate / valid values for each variable), I am unable to establish a connection to the server.
I can manually map the same local drive to the same server / share using the same username / password credentials, so it's definitely not a problem associated with permissions to the server / share.
Any suggestions as to what could be going wrong?

 

[Ruddles]

Although I have copied and pasted the code into a new module, when I execute it with a subroutine using the example that is embedded into the code (obviously substituting appropriate / valid values for each variable), I am unable to establish a connection to the server.
I can manually map the same local drive to the same server / share using the same username / password credentials, so it's definitely not a problem associated with permissions to the server / share.
Any suggestions as to what could be going wrong?

Can you post the code here - all of it including the command you're using to call MapDrive? (Change the password to "*****".) Also tell us exactly which module you put it in.

 

[wsealey]

Sorry for the delay in coming back to you - I've been away for a few days.
I've pasted your code and the additional subroutine into a regular module (Module13 to be exact) within Personal.xls

I'm using Excel 2003 and attempting to map to a drive (share) on a Windows 2003 server.

One question that does come to mind regarding specifying my credentials is, should I include the domain, and if so, should it be fully qualified, or just the alias?

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

Sub map_drive_to_server()
MsgBox "Map N: " & MapDrive("N:", _
"\\Servername\D$", _
"Domain\Username", "Password")

End Sub

 

[Ruddles]

It looks okay to me. Try logging in with and without a domain qualifier. Also, try it in a general module. Apart from that all I can say is that I cribbed it from something I found via a Google search.

Does it return FALSE when you run MapDrive?

 

[Ziggy12]

Will this work on my connection to an external database that currently uses msquery to get the data.

I want to be able to refreh the table reqularly throughout the day without having the user enter passwords.

I pasted in the code as suggested how do I call the functions? and I'm not sure what mapping a drive actually does.

cheers
ziggy

 

[mbt2103]

This code looks like it would be perfect for a project I'm working on ... but it gives me a compile error saying that only comments may appear after End Sub. The Option Explicit thru Private Type section seems to be the culprit ...