HOW TO CHANGE RESULT WITH SAME MACRO CODE
 

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?

HOW TO CHANGE RESULT WITH SAME MACRO CODE
 

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
--
HTH...

Jim Thomlinson


"K" wrote:

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?