Combining 2 values in macro

[reeser]

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.

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

 '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

 

[Norie]

Reeser

Try setting some breakpoint(s) and stepping through the code.

By the way what are you actually trying to do, I'm pretty sure there are easier ways to get the filename/path from the fullname.

 

[reeser]

Reeser

Try setting some breakpoint(s) and stepping through the code.

By the way what are you actually trying to do, I'm pretty sure there are easier ways to get the filename/path from the fullname.

I know there are easier ways for the file path but the trouble we run into is having two directories. The user might enter through the Letter drive or the actual company directory. I researched a ton but never found a simpler solution than what I came up w/ to ensure we can trace either path. I'm open to any suggestions though.

Didn't have any luck w/ the breakpoints.

 

[Norie]

Reese

What do you mean about the breakpoints?

You set them so code execution is interrupted allowing you to examine the contents of the variables.

You can also step through the code line by line using F8.