Constant as network drive

[Merlynsdad]

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?

 

[JP]

Use the UNC path instead.

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

--JP

 

[Dave Peterson]

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.

 

[Gary Brown]

Try
Application.Workbooks.Open(constant)

 

[Merlynsdad]

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?

 

[Adam]

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?

 

[Gary Brown]

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