I'm trying to combine a couple of values at the end of this code but having no luck. It will only place in "lpszRemoteName" and not "x". If I place both values into cells and then have the code combine the cell values it works fine, but that is not a step I want to implement for the permanent code. I can't seem to make them actual values. Any suggestions?
The code is in 2 parts but what I'm after is at the very end of code 2 in bold. Just wanted to include all so you could follow.
The code is in 2 parts but what I'm after is at the very end of code 2 in bold. Just wanted to include all so you could follow.
Rich (BB code):
Option Explicit
'Enumeration for what to return
Public Enum PathReturn
GetPath = 1
GetFile = 0
End Enum
Public Function StripFileOrPath(fullpath As String, _
ReturnType As PathReturn) As String
' =====================================================================
' Returns either the FileName or the Path from a given Full FileName
' 1st Arg = Pass a files full name (C:\Example\MyFile.xls)
' 2nd Arg = What to return (either the file name or the path
' Enumerated for easier selection
' =====================================================================
Dim szPathSep As String
szPathSep = Application.PathSeparator
Dim szCut As String
szCut = CStr(Empty)
Dim i As Long
i = Len(fullpath)
Dim szPath As String
Dim szFile As String
If i > 0 Then
Do While szCut <> szPathSep
szCut = Mid$(fullpath, i, 1)
If szCut = szPathSep Then
szPath = Left$(fullpath, i)
szFile = Right$(fullpath, Len(fullpath) - i)
End If
i = i - 1
Loop
Select Case ReturnType
Case GetPath
StripFileOrPath = szPath
Case GetFile
StripFileOrPath = szFile
End Select
Else
StripFileOrPath = CStr(Empty)
End If
End Function
Rich (BB code):
'Function getfilename()
' 32-bit Function version.
' Enter this declaration on a single line.
Declare Function WNetGetConnection32 Lib "MPR.DLL" Alias _
"WNetGetConnectionA" (ByVal lpszLocalName As String, ByVal _
lpszRemoteName As String, lSize As Long) As Long
' 32-bit declarations:
Dim lpszRemoteName As String
Dim lSize As Long
' Use for the return value of WNetGetConnection() API.
Const NO_ERROR As Long = 0
' The size used for the string buffer. Adjust this if you
' need a larger buffer.
Const lBUFFER_SIZE As Long = 255
'End Function
Sub GetNetPath()
Dim fullpath As String, x As String, ii As String, z As String
'fullpath name for file
fullpath = StripFileOrPath(ThisWorkbook.FullName, GetPath) & StripFileOrPath(ThisWorkbook.FullName, GetFile)
Dim rng As Range
Set rng = Sheets("Sheet1").Range("G4")
Dim xt As String
' Prompt the user to type the mapped drive letter.
'DriveLetter = UCase(InputBox("Enter Drive Letter of Your Network" & _
"Connection." & Chr(10) & "i.e. F (do not enter a colon)"))
xt = Left(StripFileOrPath(ThisWorkbook.FullName, GetPath) & StripFileOrPath(ThisWorkbook.FullName, GetFile), 2)
DriveLetter = xt
''' Add a colon to the drive letter entered.
'DriveLetter = DriveLetter & ":"
' Specifies the size in characters of the buffer.
cbRemoteName = lBUFFER_SIZE
' Prepare a string variable by padding spaces.
lpszRemoteName = lpszRemoteName & Space(lBUFFER_SIZE)
' Return the UNC path (\\Server\Share).
lStatus& = WNetGetConnection32(DriveLetter, lpszRemoteName, _
cbRemoteName)
' Verify that the WNetGetConnection() succeeded. WNetGetConnection()
' returns 0 (NO_ERROR) if it successfully retrieves the UNC path.
If lStatus& = NO_ERROR Then
' Display the UNC path.
'MsgBox lpszRemoteName, vbInformation
'trim to get only folder and file names
x = Right(fullpath, Len(fullpath) - InStr(fullpath, ":"))
Cells(9, 5) = lpszRemoteName & x
'If path doesn't equal static address then hide all run code to show ONLY error sheet---hide all other sheets
'''INSERT HERE: If Cells(2, 7) <> z Then MsgBox ("Not same path") Else MsgBox ("Same Path")
Else
' Unable to obtain the UNC path.
'MsgBox "Unable to obtain the UNC path.", vbInformation
End If