Opening a file in a macro (network)

D

David P.

I have a macro that requires opening a file in My Documents. This may be a
networking question that may require onsite help but what I was wondering is
if there is a way in the code to specify to open a file in My Documents from
"the computer you're on" for example (not sure how to word that). I'm trying
to avoid having to create a macro for each computer because I will have the
same file stored in My Documents on each computer. Does that make sense?

For example, the code right now reads (for the computer I am currently on -
"Blinds etc" is how the computer is named I believe & the other folders will
be exactly the same on the other computers ):

Workbooks.Open Filename:= _
"C:\Documents and Settings\BLINDS ETC\My Documents\Pricing\Company
Price Lists\Temporary.xls"
 
N

Nick Hodge

David

Where will the file with the macro be?

If it's inside the username folder (at least) you will be able to get that
by looking at the Workbook.Fullname property and strip out the bits you
need.

You could also read and then parse the DefaultFilePath property of the
application. Some of these may be dangerous if the user moves the file or
changes the default file path

Just a couple of suggestions

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
D

David P.

Hello Nick,

The file is located on the computer that we treat as a server - which is not
the one I'm working on for example.

Is there a way to do this?(see what I have in quotes):

Workbooks.Open Filename:= _
 
D

Dave Peterson

Are you saying you want to get to the "My documents" folder for the pc that
you're on?

Option Explicit
Sub testme()

Dim WSHShell As Object
Dim myDocPath As String

Set WSHShell = CreateObject("WScript.Shell")
myDocPath = WSHShell.SpecialFolders("mydocuments")

workbooks.open _
filename:=myDocPath & "\Pricing\Company Price Lists\Temporary.xls"

End Sub

Maybe???????
 
Top