Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Constant as network drive
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Constant as 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 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Constant as 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Constant as network drive
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Constant as 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Constant as 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Constant as 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Network drive | Excel Worksheet Functions | |||
map network drive | Excel Programming | |||
Using An Add-in On A Network Drive | Excel Programming | |||
Link workbooks-C drive to network drive | Excel Worksheet Functions | |||
Userform Local Drive & Network drive question | Excel Programming |