Opening a file in a macro (network)

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"

--
David P.

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]HIS


"David P." <[email protected]> wrote in message
news:[email protected]...
>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"
>
> --
> 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:= _
> > "C:\Documents and Settings\"THIS COMPUTER"\My Documents\Pricing\Company
> > Price Lists\Temporary.xls"

--
David P.


"Nick Hodge" wrote:

> 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]HIS
>
>
> "David P." <[email protected]> wrote in message
> news:[email protected]...
> >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"
> >
> > --
> > David P.
>
>
>

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???????


David P. wrote:
>
> 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:= _
> > > "C:\Documents and Settings\"THIS COMPUTER"\My Documents\Pricing\Company
> > > Price Lists\Temporary.xls"
>
> --
> David P.
>
> "Nick Hodge" wrote:
>
> > 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]HIS
> >
> >
> > "David P." <[email protected]> wrote in message
> > news:[email protected]...
> > >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"
> > >
> > > --
> > > David P.
> >
> >
> >

--

Dave Peterson