Finding out pathname
 

Hi I have a spreadsheet that uses a server path-name in a custom menu
link. I now have a process that backs the file up over our network
for other users to view. The problem being that these users view the
file on a different server and the link no longer works. I would
therefore like to be able to determine the location of the file, D:
or G: drive so I can set an alternate link up.

The current VBA is as below

Sub Link()
On Error Resume Next
Dim stFullName As String
Dim stFileName As String
Dim Wkb As Workbook
stFullName = Sheets("PathSheet").Range("E38")
stFileName = GetFileName(stFullName)
If IsWorkbookOpen(stFileName) Then
Set Wkb = Workbooks(stFileName)
Wkb.Activate
Else
Set Wkb = Workbooks.Open(Filename:=stFullName)
End If
End Sub

Any help would be great.

Don

Finding out pathname
 

With ActiveWorkbook

ThisDrive = Left(.Path, InStr(":", .Path))
OtherDrive = IIf(ThisDrive = "D", "G", "D")
.Save
.SaveCopyAs Replace(.fullnmae, ThisDrive & ":", OtherDrive & ":")
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"don" wrote in message
...
Hi I have a spreadsheet that uses a server path-name in a custom menu
link. I now have a process that backs the file up over our network
for other users to view. The problem being that these users view the
file on a different server and the link no longer works. I would
therefore like to be able to determine the location of the file, D:
or G: drive so I can set an alternate link up.

The current VBA is as below

Sub Link()
On Error Resume Next
Dim stFullName As String
Dim stFileName As String
Dim Wkb As Workbook
stFullName = Sheets("PathSheet").Range("E38")
stFileName = GetFileName(stFullName)
If IsWorkbookOpen(stFileName) Then
Set Wkb = Workbooks(stFileName)
Wkb.Activate
Else
Set Wkb = Workbooks.Open(Filename:=stFullName)
End If
End Sub

Any help would be great.

Don