I have an Excel file which needs to access another Excel file on a network. I
want to write the filename as a constant. This works fine when the constant
refers to a file on my local drive, but if the constant refers to a network
drive, when the code gets to "Windows (constant).Activate" it fails. How do I
get this to refer to a file on a network drive?

Use the UNC path instead.

See http://www.dailydoseofexcel.com/arch...6/21/unc-path/ and
scroll down to the first comment for sample code.

--JP

On Oct 16, 11:49*am, Merlynsdad <Merlyns...@discussions.microsoft.com>
wrote:
> I have an Excel file which needs to access another Excel file on a network. I
> want to write the filename as a constant. This works fine when the constant
> refers to a file *on my local drive, but if the constant refers to a network
> drive, when the code gets to "Windows (constant).Activate" it fails. How do I
> get this to refer to a file on a network drive?

Windows(something).activate
doesn't depend on the path--mapped or UNC.

You only use the filename (Book1.xls, not c:\my documents\excel\book1.xls).

Ps.

I try my best to not use the windows collection. I'll go through the workbooks
collection. And if my code opens the workbook:

Dim wkbk as workbook
set wkbk = workbooks.open(filename:=somefilenamehere)
....
I can use:
wkbk.activate

But be aware that most things can be done without activating or selecting.

Merlynsdad wrote:
>
> I have an Excel file which needs to access another Excel file on a network. I
> want to write the filename as a constant. This works fine when the constant
> refers to a file on my local drive, but if the constant refers to a network
> drive, when the code gets to "Windows (constant).Activate" it fails. How do I
> get this to refer to a file on a network drive?

--

Dave Peterson

Try
Application.Workbooks.Open(constant)

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Merlynsdad" wrote:

> I have an Excel file which needs to access another Excel file on a network. I
> want to write the filename as a constant. This works fine when the constant
> refers to a file on my local drive, but if the constant refers to a network
> drive, when the code gets to "Windows (constant).Activate" it fails. How do I
> get this to refer to a file on a network drive?

It helps in a way but it brings up another problem. In designing this project
the spreadsheet that I'm using as a database has always been open first. The
Application.Workbooks.Open(constant) does open that database, but my list
boxes in the current workbook that are based on that database don't populate
when it opens. The AutoLoad property is set to True. How do I get them to
populate?

"Gary Brown" wrote:

> Try
> Application.Workbooks.Open(constant)
>
> --
> Hope this helps.
> If it does, please click the Yes button.
> Thanks in advance for your feedback.
> Gary Brown
>
>
>
> "Merlynsdad" wrote:
>
> > I have an Excel file which needs to access another Excel file on a network. I
> > want to write the filename as a constant. This works fine when the constant
> > refers to a file on my local drive, but if the constant refers to a network
> > drive, when the code gets to "Windows (constant).Activate" it fails. How do I
> > get this to refer to a file on a network drive?

Maybe try this command before the open command:

ActiveWorkbook.UpdateLink Name:= _
"(database address.xls)", Type:=xlExcelLinks

Not quite sure how excel dropdown boxes obtain their data, i.e. from another
spreadsheet, but maybe that would work?



"Merlynsdad" wrote:

> It helps in a way but it brings up another problem. In designing this project
> the spreadsheet that I'm using as a database has always been open first. The
> Application.Workbooks.Open(constant) does open that database, but my list
> boxes in the current workbook that are based on that database don't populate
> when it opens. The AutoLoad property is set to True. How do I get them to
> populate?
>
> "Gary Brown" wrote:
>
> > Try
> > Application.Workbooks.Open(constant)
> >
> > --
> > Hope this helps.
> > If it does, please click the Yes button.
> > Thanks in advance for your feedback.
> > Gary Brown
> >
> >
> >
> > "Merlynsdad" wrote:
> >
> > > I have an Excel file which needs to access another Excel file on a network. I
> > > want to write the filename as a constant. This works fine when the constant
> > > refers to a file on my local drive, but if the constant refers to a network
> > > drive, when the code gets to "Windows (constant).Activate" it fails. How do I
> > > get this to refer to a file on a network drive?

If the Excel workbook on the network is already open, simply refer to it by
it's workbook name.
Example:
constant = "MyWorkbookOnTheNetwork.xls"
Windows(constant).Activate

It just worked for me in a quick test.

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Merlynsdad" wrote:

> It helps in a way but it brings up another problem. In designing this project
> the spreadsheet that I'm using as a database has always been open first. The
> Application.Workbooks.Open(constant) does open that database, but my list
> boxes in the current workbook that are based on that database don't populate
> when it opens. The AutoLoad property is set to True. How do I get them to
> populate?
>
> "Gary Brown" wrote:
>
> > Try
> > Application.Workbooks.Open(constant)
> >
> > --
> > Hope this helps.
> > If it does, please click the Yes button.
> > Thanks in advance for your feedback.
> > Gary Brown
> >
> >
> >
> > "Merlynsdad" wrote:
> >
> > > I have an Excel file which needs to access another Excel file on a network. I
> > > want to write the filename as a constant. This works fine when the constant
> > > refers to a file on my local drive, but if the constant refers to a network
> > > drive, when the code gets to "Windows (constant).Activate" it fails. How do I
> > > get this to refer to a file on a network drive?