Excel in macro and connecting to a network drive question

[Mike S.]

In the Excel macro there are files located on a server so everyone has the
latest data. The developer connects to the server by hard coding the drive
say E://data/
If a new user maps the server to F:/ the program does not work.
Is there a way to connect using the server name say \\file-evo-01\ ?

 

[mudraker]

sPath = "\\file-evo-01\Data\"
workbooks.open sPath & "Myfile.xls"


--
mudraker

If my reply has assisted or failed to assist you I welcome your
Feedback.

www.thecodecage.com

 

[microsoft.public.excel.worksheet.functio]

yes, but I think it will take you right back where you are. As the name of
the Drives are assigned normally with a letter, the description of that name
it is also a choice that IT has the power to change anytime they want. So, by
changing that to the new name, this does not mean that the program will move
along to the long description name. It is very comun that when the Drive
name, lets say G, change along with the description name. So, you need to
chage the name in that case any way.

Out of, I am sure, several options you have, I know two. First, for
Microsoft, in this particular case, there is a good catch. With a little of
imagination, you can centralize the change of the name in a single part so,
this way, if the drive change its name, you can use the macro to read that
position some where in the spread sheet, and then, loaded into the macro and
use it all across.

Second, and I think you are going to love this one, but you need to confirm
if you are the developer or someone capable of understanding how to change
code in the macro and include some more that I can send. Please confirm.

Argy

 

[Argy - Arcasoft]

Hi; Better do this

Public Actual_Book As String
Public Actual_Path As String
Public Location As String

Function Location_Id()
Actual_Book = ActiveWorkbook.Name
Actual_Path = ActiveWorkbook.Path
Location = Actual_Path & "\" & Actual_Book
End Function


Location = is the variable that you change in your code. This will work. If
at any even the Drive changes name, this fuction will pick up the new name.
So no more lost drives and baby-sitting drive's names.

Suggestion, run Location_Id from each and every sheet by creating a clicking
event. This will guarantee that no matter where in the workbook you are, it
will always keep the variable updated. I have found that although is a Public
variable, if by any chance the program crash in any macro, it completly
looses the path memory in the file, resulting in errors when saving it with a
macro. Let me know if you understand.

Argy