Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy folder using IP address as destination path
Hi
I am using Ron de Bruin's code to copy a folder, which works fine when I specify a drive letter as below. As some of our users have the destination mapped as various other letters, I'd like to use the IP address of the destination drive (10.11.70.190) instead. How is this done - my attempts have only led to error 76, Path not found? Sub Copy_Folder() Dim FSO As Object Dim FromPath As String Dim ToPath As String FromPath = "X:\ACCOUNTS-EAST\New MA Project" ToPath = "S:\TEMA-01" If Right(FromPath, 1) = "\" Then FromPath = Left(FromPath, Len(FromPath) - 1) End If If Right(ToPath, 1) = "\" Then ToPath = Left(ToPath, Len(ToPath) - 1) End If Set FSO = CreateObject("scripting.filesystemobject") If FSO.FolderExists(FromPath) = False Then MsgBox FromPath & " doesn't exist" Exit Sub End If FSO.CopyFolder Source:=FromPath, Destination:=ToPath MsgBox "You can find the files and subfolders from " & FromPath & " in " & ToPath End Sub Thanks Martyn -- Excel 2000, Windows 2003 over Citrix PS4 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy folder using IP address as destination path
Take a look at this:
http://www.uwplatt.edu/oit/terms/uncpath.html Of course, there are specific to certain specific, yours will certainly be a little different... Try running this code in an Excel module: 'Option Compare Database '************ Code Start ************** 'This code was originally written by Terry Kreft 'and Dev Ashish. 'It is not to be altered or distributed, 'except as part of an application. 'You are free to use it in any application, 'provided the copyright notice is left unchanged. ' ' Original Code by Terry Kreft ' Modified by Dev Ashish ' 'Drive Types Private Const DRIVE_UNKNOWN = 0 Private Const DRIVE_ABSENT = 1 Private Const DRIVE_REMOVABLE = 2 Private Const DRIVE_FIXED = 3 Private Const DRIVE_REMOTE = 4 Private Const DRIVE_CDROM = 5 Private Const DRIVE_RAMDISK = 6 ' returns errors for UNC Path Private Const ERROR_BAD_DEVICE = 1200& Private Const ERROR_CONNECTION_UNAVAIL = 1201& Private Const ERROR_EXTENDED_ERROR = 1208& Private Const ERROR_MORE_DATA = 234 Private Const ERROR_NOT_SUPPORTED = 50& Private Const ERROR_NO_NET_OR_BAD_PATH = 1203& Private Const ERROR_NO_NETWORK = 1222& Private Const ERROR_NOT_CONNECTED = 2250& Private Const NO_ERROR = 0 Private Declare Function WNetGetConnection Lib "mpr.dll" Alias _ "WNetGetConnectionA" (ByVal lpszLocalName As String, _ ByVal lpszRemoteName As String, cbRemoteName As Long) As Long Private Declare Function GetLogicalDriveStrings Lib "kernel32" Alias _ "GetLogicalDriveStringsA" (ByVal nBufferLength As Long, _ ByVal lpBuffer As String) As Long Private Declare Function GetDriveType Lib "kernel32" Alias "GetDriveTypeA" _ (ByVal nDrive As String) As Long Private Function fGetDrives() As String 'Returns all mapped drives Dim lngRet As Long Dim strDrives As String * 255 Dim lngTmp As Long lngTmp = Len(strDrives) lngRet = GetLogicalDriveStrings(lngTmp, strDrives) fGetDrives = Left(strDrives, lngRet) End Function Private Function fGetUNCPath(strDriveLetter As String) As String On Local Error GoTo fGetUNCPath_Err Dim Msg As String, lngReturn As Long Dim lpszLocalName As String Dim lpszRemoteName As String Dim cbRemoteName As Long lpszLocalName = strDriveLetter lpszRemoteName = String$(255, Chr$(32)) cbRemoteName = Len(lpszRemoteName) lngReturn = WNetGetConnection(lpszLocalName, lpszRemoteName, _ cbRemoteName) Select Case lngReturn Case ERROR_BAD_DEVICE Msg = "Error: Bad Device" Case ERROR_CONNECTION_UNAVAIL Msg = "Error: Connection Un-Available" Case ERROR_EXTENDED_ERROR Msg = "Error: Extended Error" Case ERROR_MORE_DATA Msg = "Error: More Data" Case ERROR_NOT_SUPPORTED Msg = "Error: Feature not Supported" Case ERROR_NO_NET_OR_BAD_PATH Msg = "Error: No Network Available or Bad Path" Case ERROR_NO_NETWORK Msg = "Error: No Network Available" Case ERROR_NOT_CONNECTED Msg = "Error: Not Connected" Case NO_ERROR ' all is successful... End Select If Len(Msg) Then MsgBox Msg, vbInformation Else fGetUNCPath = Left$(lpszRemoteName, cbRemoteName) End If fGetUNCPath_End: Exit Function fGetUNCPath_Err: MsgBox Err.Description, vbInformation Resume fGetUNCPath_End End Function Private Function fDriveType(strDriveName As String) As String Dim lngRet As Long Dim strDrive As String lngRet = GetDriveType(strDriveName) Select Case lngRet Case DRIVE_UNKNOWN 'The drive type cannot be determined. strDrive = "Unknown Drive Type" Case DRIVE_ABSENT 'The root directory does not exist. strDrive = "Drive does not exist" Case DRIVE_REMOVABLE 'The drive can be removed from the drive. strDrive = "Removable Media" Case DRIVE_FIXED 'The disk cannot be removed from the drive. strDrive = "Fixed Drive" Case DRIVE_REMOTE 'The drive is a remote (network) drive. strDrive = "Network Drive" Case DRIVE_CDROM 'The drive is a CD-ROM drive. strDrive = "CD Rom" Case DRIVE_RAMDISK 'The drive is a RAM disk. strDrive = "Ram Disk" End Select fDriveType = strDrive End Function Sub sListAllDrives() Dim strAllDrives As String Dim strTmp As String strAllDrives = fGetDrives If strAllDrives < "" Then Do strTmp = Mid$(strAllDrives, 1, InStr(strAllDrives, vbNullChar) - 1) strAllDrives = Mid$(strAllDrives, InStr(strAllDrives, vbNullChar) + 1) Select Case fDriveType(strTmp) Case "Removable Media": Debug.Print "Removable drive : " & strTmp Case "CD ROM": Debug.Print " CD Rom drive : " & strTmp Case "Fixed Drive": Debug.Print " Local drive : " & strTmp Case "Network Drive": Debug.Print " Network drive : " & strTmp Debug.Print " UNC Path : " & _ fGetUNCPath(Left$(strTmp, Len(strTmp) - 1)) End Select Loop While strAllDrives < "" End If End Sub '**************** Code End ****************** View the results in the 'Immediate Window' That should give you some idea of your specific UNC path. Anyways, I hope that gives you some ideas. Regards, Ryan-- -- RyGuy "WembleyBear" wrote: Hi I am using Ron de Bruin's code to copy a folder, which works fine when I specify a drive letter as below. As some of our users have the destination mapped as various other letters, I'd like to use the IP address of the destination drive (10.11.70.190) instead. How is this done - my attempts have only led to error 76, Path not found? Sub Copy_Folder() Dim FSO As Object Dim FromPath As String Dim ToPath As String FromPath = "X:\ACCOUNTS-EAST\New MA Project" ToPath = "S:\TEMA-01" If Right(FromPath, 1) = "\" Then FromPath = Left(FromPath, Len(FromPath) - 1) End If If Right(ToPath, 1) = "\" Then ToPath = Left(ToPath, Len(ToPath) - 1) End If Set FSO = CreateObject("scripting.filesystemobject") If FSO.FolderExists(FromPath) = False Then MsgBox FromPath & " doesn't exist" Exit Sub End If FSO.CopyFolder Source:=FromPath, Destination:=ToPath MsgBox "You can find the files and subfolders from " & FromPath & " in " & ToPath End Sub Thanks Martyn -- Excel 2000, Windows 2003 over Citrix PS4 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy folder using IP address as destination path
I run on Citrix at work. Try typing in the server address like this:
\\server\folder You can find the server address on My Computer. Copy the part in between the ' ' after typin in the \\. Hopefully that works. -- Please rate posts so we know when we have answered your questions. Thanks. "ryguy7272" wrote: Take a look at this: http://www.uwplatt.edu/oit/terms/uncpath.html Of course, there are specific to certain specific, yours will certainly be a little different... Try running this code in an Excel module: 'Option Compare Database '************ Code Start ************** 'This code was originally written by Terry Kreft 'and Dev Ashish. 'It is not to be altered or distributed, 'except as part of an application. 'You are free to use it in any application, 'provided the copyright notice is left unchanged. ' ' Original Code by Terry Kreft ' Modified by Dev Ashish ' 'Drive Types Private Const DRIVE_UNKNOWN = 0 Private Const DRIVE_ABSENT = 1 Private Const DRIVE_REMOVABLE = 2 Private Const DRIVE_FIXED = 3 Private Const DRIVE_REMOTE = 4 Private Const DRIVE_CDROM = 5 Private Const DRIVE_RAMDISK = 6 ' returns errors for UNC Path Private Const ERROR_BAD_DEVICE = 1200& Private Const ERROR_CONNECTION_UNAVAIL = 1201& Private Const ERROR_EXTENDED_ERROR = 1208& Private Const ERROR_MORE_DATA = 234 Private Const ERROR_NOT_SUPPORTED = 50& Private Const ERROR_NO_NET_OR_BAD_PATH = 1203& Private Const ERROR_NO_NETWORK = 1222& Private Const ERROR_NOT_CONNECTED = 2250& Private Const NO_ERROR = 0 Private Declare Function WNetGetConnection Lib "mpr.dll" Alias _ "WNetGetConnectionA" (ByVal lpszLocalName As String, _ ByVal lpszRemoteName As String, cbRemoteName As Long) As Long Private Declare Function GetLogicalDriveStrings Lib "kernel32" Alias _ "GetLogicalDriveStringsA" (ByVal nBufferLength As Long, _ ByVal lpBuffer As String) As Long Private Declare Function GetDriveType Lib "kernel32" Alias "GetDriveTypeA" _ (ByVal nDrive As String) As Long Private Function fGetDrives() As String 'Returns all mapped drives Dim lngRet As Long Dim strDrives As String * 255 Dim lngTmp As Long lngTmp = Len(strDrives) lngRet = GetLogicalDriveStrings(lngTmp, strDrives) fGetDrives = Left(strDrives, lngRet) End Function Private Function fGetUNCPath(strDriveLetter As String) As String On Local Error GoTo fGetUNCPath_Err Dim Msg As String, lngReturn As Long Dim lpszLocalName As String Dim lpszRemoteName As String Dim cbRemoteName As Long lpszLocalName = strDriveLetter lpszRemoteName = String$(255, Chr$(32)) cbRemoteName = Len(lpszRemoteName) lngReturn = WNetGetConnection(lpszLocalName, lpszRemoteName, _ cbRemoteName) Select Case lngReturn Case ERROR_BAD_DEVICE Msg = "Error: Bad Device" Case ERROR_CONNECTION_UNAVAIL Msg = "Error: Connection Un-Available" Case ERROR_EXTENDED_ERROR Msg = "Error: Extended Error" Case ERROR_MORE_DATA Msg = "Error: More Data" Case ERROR_NOT_SUPPORTED Msg = "Error: Feature not Supported" Case ERROR_NO_NET_OR_BAD_PATH Msg = "Error: No Network Available or Bad Path" Case ERROR_NO_NETWORK Msg = "Error: No Network Available" Case ERROR_NOT_CONNECTED Msg = "Error: Not Connected" Case NO_ERROR ' all is successful... End Select If Len(Msg) Then MsgBox Msg, vbInformation Else fGetUNCPath = Left$(lpszRemoteName, cbRemoteName) End If fGetUNCPath_End: Exit Function fGetUNCPath_Err: MsgBox Err.Description, vbInformation Resume fGetUNCPath_End End Function Private Function fDriveType(strDriveName As String) As String Dim lngRet As Long Dim strDrive As String lngRet = GetDriveType(strDriveName) Select Case lngRet Case DRIVE_UNKNOWN 'The drive type cannot be determined. strDrive = "Unknown Drive Type" Case DRIVE_ABSENT 'The root directory does not exist. strDrive = "Drive does not exist" Case DRIVE_REMOVABLE 'The drive can be removed from the drive. strDrive = "Removable Media" Case DRIVE_FIXED 'The disk cannot be removed from the drive. strDrive = "Fixed Drive" Case DRIVE_REMOTE 'The drive is a remote (network) drive. strDrive = "Network Drive" Case DRIVE_CDROM 'The drive is a CD-ROM drive. strDrive = "CD Rom" Case DRIVE_RAMDISK 'The drive is a RAM disk. strDrive = "Ram Disk" End Select fDriveType = strDrive End Function Sub sListAllDrives() Dim strAllDrives As String Dim strTmp As String strAllDrives = fGetDrives If strAllDrives < "" Then Do strTmp = Mid$(strAllDrives, 1, InStr(strAllDrives, vbNullChar) - 1) strAllDrives = Mid$(strAllDrives, InStr(strAllDrives, vbNullChar) + 1) Select Case fDriveType(strTmp) Case "Removable Media": Debug.Print "Removable drive : " & strTmp Case "CD ROM": Debug.Print " CD Rom drive : " & strTmp Case "Fixed Drive": Debug.Print " Local drive : " & strTmp Case "Network Drive": Debug.Print " Network drive : " & strTmp Debug.Print " UNC Path : " & _ fGetUNCPath(Left$(strTmp, Len(strTmp) - 1)) End Select Loop While strAllDrives < "" End If End Sub '**************** Code End ****************** View the results in the 'Immediate Window' That should give you some idea of your specific UNC path. Anyways, I hope that gives you some ideas. Regards, Ryan-- -- RyGuy "WembleyBear" wrote: Hi I am using Ron de Bruin's code to copy a folder, which works fine when I specify a drive letter as below. As some of our users have the destination mapped as various other letters, I'd like to use the IP address of the destination drive (10.11.70.190) instead. How is this done - my attempts have only led to error 76, Path not found? Sub Copy_Folder() Dim FSO As Object Dim FromPath As String Dim ToPath As String FromPath = "X:\ACCOUNTS-EAST\New MA Project" ToPath = "S:\TEMA-01" If Right(FromPath, 1) = "\" Then FromPath = Left(FromPath, Len(FromPath) - 1) End If If Right(ToPath, 1) = "\" Then ToPath = Left(ToPath, Len(ToPath) - 1) End If Set FSO = CreateObject("scripting.filesystemobject") If FSO.FolderExists(FromPath) = False Then MsgBox FromPath & " doesn't exist" Exit Sub End If FSO.CopyFolder Source:=FromPath, Destination:=ToPath MsgBox "You can find the files and subfolders from " & FromPath & " in " & ToPath End Sub Thanks Martyn -- Excel 2000, Windows 2003 over Citrix PS4 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change link path when open destination file | Excel Worksheet Functions | |||
Remove end folder from path found with ThisWorkbook.Path command ? | Excel Programming | |||
How to List the names of the subfolders present in the folder (path of folder is given in the textbox by user ) | Excel Programming | |||
Change Hyperlink Destination Folder | Excel Discussion (Misc queries) | |||
Get Folder Path | Excel Programming |