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

 

[Zyel]

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

you mean u wan to make a OLAP or ADO Connection to the server?

 

[Hooi]

you mean u wan to make a OLAP or ADO Connection to the server?

Sorry i don't know what is the different of OLAP and ADO...but the purpose for me is just importing a text file from local connection with ip 192.168.1.18 which have username and password...the text file imported just to retrieve some data and do not need to amend it.

 

[mancubus]

perhaps;

xl2003: data - import external data - new web query

xl2007: data - get external data - from web

use macro recorder.

 

[Hooi]

perhaps;

xl2003: data - import external data - new web query

xl2007: data - get external data - from web

use macro recorder.

i know about the recorder but the recorder only provide me the path and pattern for the text file...once i power off my computer i have to manually enter the
administrator and password again to ensure that my vba is accessible.My question is to include the username and password b4 importing the external data so that i do not need to manually enter each time i using the macro

 

[Ruddles]

If you want to map a network drive, here's some code I've been using. Drop the whole lot into the sheet module:-

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:", "[URL="file://\\RemoteMachine\RemoteDirectory"]\\RemoteMachine\RemoteDirectory[/URL]", "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

Then to map a drive, use:-

  MsgBox "Map R: " & MapDrive("R:", _
    "[URL="file://servername/Sharename$/Folder/Subfolder"]\\Servername\Sharename$\Folder\Subfolder[/URL]", _
    "administrator", "549221")

or:-

  MsgBox "Map R: " & MapDrive("R:", _
    "[URL="file://\\192.168.1.18\Sharename$\Folder\Subfolder"]\\192.168.1.18\Sharename$\Folder\Subfolder[/URL]", _
    "administrator", "549221")

You may need to disconnect any previously existing mapping before you do that. Use:-

  MsgBox "Unmap R: " & UnMapDrive("R:")

In each case I'm using MsgBox to display True or False depending on whether the result of the Map/Unmap as successful or not. You will probably want to assign the values to a variable which you can then test in order to work out what do do if the operation fails (e.g. keep trying, try again up to x times, stop with a warning message, etc).

Hope this gets you some way towards what you're trying to do.

R.

 

[Hooi]

If you want to map a network drive, here's some code I've been using. Drop the whole lot into the sheet module:-

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:", "[URL="file://\\RemoteMachine\RemoteDirectory"]\\RemoteMachine\RemoteDirectory[/URL]", "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

Then to map a drive, use:-

  MsgBox "Map R: " & MapDrive("R:", _
    "[URL="file://servername/Sharename$/Folder/Subfolder"]\\Servername\Sharename$\Folder\Subfolder[/URL]", _
    "administrator", "549221")

or:-

  MsgBox "Map R: " & MapDrive("R:", _
    "[URL="file://\\192.168.1.18\Sharename$\Folder\Subfolder"]\\192.168.1.18\Sharename$\Folder\Subfolder[/URL]", _
    "administrator", "549221")

You may need to disconnect any previously existing mapping before you do that. Use:-

  MsgBox "Unmap R: " & UnMapDrive("R:")

In each case I'm using MsgBox to display True or False depending on whether the result of the Map/Unmap as successful or not. You will probably want to assign the values to a variable which you can then test in order to work out what do do if the operation fails (e.g. keep trying, try again up to x times, stop with a warning message, etc).

Hope this gets you some way towards what you're trying to do.

R.

Exactly what is the thing i want...Thanks for your help...

 

[Ruddles]

Good, I'm glad I understood your requirement. You replied so quickly I don't know if you've actually managed to try this code yet, so please come back and let us know whether you got it working. If you need any additional coding, please don't hesitate to ask.

 

[Hooi]

Good, I'm glad I understood your requirement. You replied so quickly I don't know if you've actually managed to try this code yet, so please come back and let us know whether you got it working. If you need any additional coding, please don't hesitate to ask.

Thanks Ruddles, Actually for working hour i was online for all the time that is why i notified by email that someone reply...actually i already search from internet and it's work for me just look similar like the code u provide to me...But In fact you are satisfying your time to help me so i do not want to tell u i have it before.So that is why i was so fast to replied.

 

[Ruddles]