HOW TO CHANGE RESULT WITH SAME MACRO CODE

K

K

Hi all, I got macro in which I have code (see below)
Range("A1").Value = ThisWorkbook.FullName
Above macro code brings "F:\Kas\Documents\Profit.xlsx" text in cell
"A1"
Is there way that by putting "ThisWorkbook.FullName" just bring "\Kas
\Documents\Profit.xlsx" and exclud the word "F:" and add "\\Home\ABC"
in the line
so it should be coming in cell "A1" "\\Home\ABC\Kas\Documents
\Profit.xlsx". I hope I was able to explain my question. Please can
any friend help?
 
J

Jim Thomlinson

Here is an API to retrun the UNC name from a drive letter. I assume this is
what you are looking for?

Declare Function WNetGetConnection Lib "mpr.dll" Alias "WNetGetConnectionA"
( _
ByVal lpszLocalName As String, _
ByVal lpszRemoteName As String, _
ByRef cbRemoteName As Long) As Long

Sub Test()
MsgBox UNCfromLocalDriveName("G")
End Sub

Function UNCfromLocalDriveName(strLocalDrive) As String
'Find UNC from Local path
'i.e. Local drive "F:" = "\\RdaServer3\sys1"
' example of usage: UNCfromLocalDriveName("P") <-Actual Drive Letter
' or UNCfromLocalDriveName(A2) <-Cell reference
'
Dim sLocal As String
Dim sRemote As String * 255
Dim lLen As Long

Application.Volatile

sRemote = String$(255, Chr$(32))

lLen = 255
sLocal = strLocalDrive & ":"

WNetGetConnection sLocal, sRemote, lLen

UNCfromLocalDriveName = Trim(sRemote)

End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top