Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The path for any given Excel spreadsheet ???
I've tried to use a number of items to get the proper path
to the Excel spreadsheet that is opening but I keep missing. Sometimes the Excel spreadsheet takes it's own path from a previously opened spreadsheet. Sometimes it takes it from a parameter at: Tools, Options..., General tab, Default File Location: Specifically I am using VBA and trying to use: ThisWorkbook.Path but that is giving the results above. I need the correct path for the workbook / spreadsheet file that is opening up. How do I get that path? Thank you very much in advance. :-) -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
The path for any given Excel spreadsheet ???
Workbooks.open "C:\Myfolder\Myfiles.xls"
sPath = ActiveWorkbook.Path sPath would then equal "C:\MyFolder" or Dim bk as Workbook, sPath as String set bk = Workbooks.Open("C:\Myfolder\Myfiles.xls") sPath = bk.Path -- Regards, Tom Ogilvy Jim Carlock <anon@localhost wrote in message ... I've tried to use a number of items to get the proper path to the Excel spreadsheet that is opening but I keep missing. Sometimes the Excel spreadsheet takes it's own path from a previously opened spreadsheet. Sometimes it takes it from a parameter at: Tools, Options..., General tab, Default File Location: Specifically I am using VBA and trying to use: ThisWorkbook.Path but that is giving the results above. I need the correct path for the workbook / spreadsheet file that is opening up. How do I get that path? Thank you very much in advance. :-) -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
The path for any given Excel spreadsheet ???
Okay, I'm not opening a spreadsheet from a
spreadsheet. I'm looking for the path before the spreadsheet actually opens. I might be using the wrong method. I've got code placed within the ThisWorkbook.Open method... Workbook_Open() method I've got the code placed there... sPath = ThisWorkbook.Path ThisWorkbook.Path returns a variety of information that is related to other workbooks (spreadsheets) that are opened up within Excel, information that is set by the variable (which I think is read from the registry) File, Options..., General tab, Default file location: I'm opening the spreadsheet by double-clicking on it from Windows Explorer. If another spreadsheet is open inside of Excel, ThisWorkbook.Path returns the path to the spread- sheet that is already open. I want the path to the spreadsheet that isn't open yet. The spreadsheet that is opening up and I'm wanting to obtain this path inside of the Workbook_Open() method. Hope that explains what's happening a little better. I'm writing code that will be used within Excel 2000 and Excel XP spread- sheets. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Tom Ogilvy" wrote in message ... Workbooks.open "C:\Myfolder\Myfiles.xls" sPath = ActiveWorkbook.Path sPath would then equal "C:\MyFolder" or Dim bk as Workbook, sPath as String set bk = Workbooks.Open("C:\Myfolder\Myfiles.xls") sPath = bk.Path -- Regards, Tom Ogilvy Jim Carlock <anon@localhost wrote in message ... I've tried to use a number of items to get the proper path to the Excel spreadsheet that is opening but I keep missing. Sometimes the Excel spreadsheet takes it's own path from a previously opened spreadsheet. Sometimes it takes it from a parameter at: Tools, Options..., General tab, Default File Location: Specifically I am using VBA and trying to use: ThisWorkbook.Path but that is giving the results above. I need the correct path for the workbook / spreadsheet file that is opening up. How do I get that path? Thank you very much in advance. :-) -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
The path for any given Excel spreadsheet ???
Another way to describe the problem... I'm opening an
Access database that resides inside the same path as the spreadsheet itself. I'm running into problems where ThisWorkbook inside of Workbook_Open() is seeing another workbook that is already open instead of seeing itself. I can see what's happening... The workbook isn't open yet inside of the Workbook_Open() method so ThisWorkbook refers to whatever workbook is currently open because Excel is doing a late binding to the spreadsheet. So there is some obscure thing available that points to the correct path of the xls file that is opening but it doesn't get put into the .Path variable until the spreadsheet is already open. But the spreadsheet already knows where it exists because it doesn't get confused and use the late binding ..Path because it's using a variable located somewhere else. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" <anon@localhost wrote in message ... Okay, I'm not opening a spreadsheet from a spreadsheet. I'm looking for the path before the spreadsheet actually opens. I might be using the wrong method. I've got code placed within the ThisWorkbook.Open method... Workbook_Open() method I've got the code placed there... sPath = ThisWorkbook.Path ThisWorkbook.Path returns a variety of information that is related to other workbooks (spreadsheets) that are opened up within Excel, information that is set by the variable (which I think is read from the registry) File, Options..., General tab, Default file location: I'm opening the spreadsheet by double-clicking on it from Windows Explorer. If another spreadsheet is open inside of Excel, ThisWorkbook.Path returns the path to the spread- sheet that is already open. I want the path to the spreadsheet that isn't open yet. The spreadsheet that is opening up and I'm wanting to obtain this path inside of the Workbook_Open() method. Hope that explains what's happening a little better. I'm writing code that will be used within Excel 2000 and Excel XP spread- sheets. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Tom Ogilvy" wrote in message ... Workbooks.open "C:\Myfolder\Myfiles.xls" sPath = ActiveWorkbook.Path sPath would then equal "C:\MyFolder" or Dim bk as Workbook, sPath as String set bk = Workbooks.Open("C:\Myfolder\Myfiles.xls") sPath = bk.Path -- Regards, Tom Ogilvy Jim Carlock <anon@localhost wrote in message ... I've tried to use a number of items to get the proper path to the Excel spreadsheet that is opening but I keep missing. Sometimes the Excel spreadsheet takes it's own path from a previously opened spreadsheet. Sometimes it takes it from a parameter at: Tools, Options..., General tab, Default File Location: Specifically I am using VBA and trying to use: ThisWorkbook.Path but that is giving the results above. I need the correct path for the workbook / spreadsheet file that is opening up. How do I get that path? Thank you very much in advance. :-) -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
The path for any given Excel spreadsheet ???
I can't get it to duplicate on my home machine at the moment. Oof.
:-( -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" <anon@localhost wrote in message ... Another way to describe the problem... I'm opening an Access database that resides inside the same path as the spreadsheet itself. I'm running into problems where ThisWorkbook inside of Workbook_Open() is seeing another workbook that is already open instead of seeing itself. I can see what's happening... The workbook isn't open yet inside of the Workbook_Open() method so ThisWorkbook refers to whatever workbook is currently open because Excel is doing a late binding to the spreadsheet. So there is some obscure thing available that points to the correct path of the xls file that is opening but it doesn't get put into the .Path variable until the spreadsheet is already open. But the spreadsheet already knows where it exists because it doesn't get confused and use the late binding .Path because it's using a variable located somewhere else. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" <anon@localhost wrote in message ... Okay, I'm not opening a spreadsheet from a spreadsheet. I'm looking for the path before the spreadsheet actually opens. I might be using the wrong method. I've got code placed within the ThisWorkbook.Open method... Workbook_Open() method I've got the code placed there... sPath = ThisWorkbook.Path ThisWorkbook.Path returns a variety of information that is related to other workbooks (spreadsheets) that are opened up within Excel, information that is set by the variable (which I think is read from the registry) File, Options..., General tab, Default file location: I'm opening the spreadsheet by double-clicking on it from Windows Explorer. If another spreadsheet is open inside of Excel, ThisWorkbook.Path returns the path to the spread- sheet that is already open. I want the path to the spreadsheet that isn't open yet. The spreadsheet that is opening up and I'm wanting to obtain this path inside of the Workbook_Open() method. Hope that explains what's happening a little better. I'm writing code that will be used within Excel 2000 and Excel XP spread- sheets. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Tom Ogilvy" wrote in message ... Workbooks.open "C:\Myfolder\Myfiles.xls" sPath = ActiveWorkbook.Path sPath would then equal "C:\MyFolder" or Dim bk as Workbook, sPath as String set bk = Workbooks.Open("C:\Myfolder\Myfiles.xls") sPath = bk.Path -- Regards, Tom Ogilvy Jim Carlock <anon@localhost wrote in message ... I've tried to use a number of items to get the proper path to the Excel spreadsheet that is opening but I keep missing. Sometimes the Excel spreadsheet takes it's own path from a previously opened spreadsheet. Sometimes it takes it from a parameter at: Tools, Options..., General tab, Default File Location: Specifically I am using VBA and trying to use: ThisWorkbook.Path but that is giving the results above. I need the correct path for the workbook / spreadsheet file that is opening up. How do I get that path? Thank you very much in advance. :-) -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
The path for any given Excel spreadsheet ???
The problem is occuring on an Office 2k machine running
Office 2K SP3 and it's occuring only when another spreadsheet is already open inside of Excel. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" <anon@localhost wrote in message ... I can't get it to duplicate on my home machine at the moment. Oof. :-( -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" <anon@localhost wrote in message ... Another way to describe the problem... I'm opening an Access database that resides inside the same path as the spreadsheet itself. I'm running into problems where ThisWorkbook inside of Workbook_Open() is seeing another workbook that is already open instead of seeing itself. I can see what's happening... The workbook isn't open yet inside of the Workbook_Open() method so ThisWorkbook refers to whatever workbook is currently open because Excel is doing a late binding to the spreadsheet. So there is some obscure thing available that points to the correct path of the xls file that is opening but it doesn't get put into the .Path variable until the spreadsheet is already open. But the spreadsheet already knows where it exists because it doesn't get confused and use the late binding .Path because it's using a variable located somewhere else. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" <anon@localhost wrote in message ... Okay, I'm not opening a spreadsheet from a spreadsheet. I'm looking for the path before the spreadsheet actually opens. I might be using the wrong method. I've got code placed within the ThisWorkbook.Open method... Workbook_Open() method I've got the code placed there... sPath = ThisWorkbook.Path ThisWorkbook.Path returns a variety of information that is related to other workbooks (spreadsheets) that are opened up within Excel, information that is set by the variable (which I think is read from the registry) File, Options..., General tab, Default file location: I'm opening the spreadsheet by double-clicking on it from Windows Explorer. If another spreadsheet is open inside of Excel, ThisWorkbook.Path returns the path to the spread- sheet that is already open. I want the path to the spreadsheet that isn't open yet. The spreadsheet that is opening up and I'm wanting to obtain this path inside of the Workbook_Open() method. Hope that explains what's happening a little better. I'm writing code that will be used within Excel 2000 and Excel XP spread- sheets. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Tom Ogilvy" wrote in message ... Workbooks.open "C:\Myfolder\Myfiles.xls" sPath = ActiveWorkbook.Path sPath would then equal "C:\MyFolder" or Dim bk as Workbook, sPath as String set bk = Workbooks.Open("C:\Myfolder\Myfiles.xls") sPath = bk.Path -- Regards, Tom Ogilvy Jim Carlock <anon@localhost wrote in message ... I've tried to use a number of items to get the proper path to the Excel spreadsheet that is opening but I keep missing. Sometimes the Excel spreadsheet takes it's own path from a previously opened spreadsheet. Sometimes it takes it from a parameter at: Tools, Options..., General tab, Default File Location: Specifically I am using VBA and trying to use: ThisWorkbook.Path but that is giving the results above. I need the correct path for the workbook / spreadsheet file that is opening up. How do I get that path? Thank you very much in advance. :-) -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
The path for any given Excel spreadsheet ???
ThisWorkbook refers to the workbook running the code. Code contained in a
workbook_open event does not run until the workbook is opened. -- Regards, Tom Ogilvy Jim Carlock <anon@localhost wrote in message ... The problem is occuring on an Office 2k machine running Office 2K SP3 and it's occuring only when another spreadsheet is already open inside of Excel. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" <anon@localhost wrote in message ... I can't get it to duplicate on my home machine at the moment. Oof. :-( -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" <anon@localhost wrote in message ... Another way to describe the problem... I'm opening an Access database that resides inside the same path as the spreadsheet itself. I'm running into problems where ThisWorkbook inside of Workbook_Open() is seeing another workbook that is already open instead of seeing itself. I can see what's happening... The workbook isn't open yet inside of the Workbook_Open() method so ThisWorkbook refers to whatever workbook is currently open because Excel is doing a late binding to the spreadsheet. So there is some obscure thing available that points to the correct path of the xls file that is opening but it doesn't get put into the .Path variable until the spreadsheet is already open. But the spreadsheet already knows where it exists because it doesn't get confused and use the late binding .Path because it's using a variable located somewhere else. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" <anon@localhost wrote in message ... Okay, I'm not opening a spreadsheet from a spreadsheet. I'm looking for the path before the spreadsheet actually opens. I might be using the wrong method. I've got code placed within the ThisWorkbook.Open method... Workbook_Open() method I've got the code placed there... sPath = ThisWorkbook.Path ThisWorkbook.Path returns a variety of information that is related to other workbooks (spreadsheets) that are opened up within Excel, information that is set by the variable (which I think is read from the registry) File, Options..., General tab, Default file location: I'm opening the spreadsheet by double-clicking on it from Windows Explorer. If another spreadsheet is open inside of Excel, ThisWorkbook.Path returns the path to the spread- sheet that is already open. I want the path to the spreadsheet that isn't open yet. The spreadsheet that is opening up and I'm wanting to obtain this path inside of the Workbook_Open() method. Hope that explains what's happening a little better. I'm writing code that will be used within Excel 2000 and Excel XP spread- sheets. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Tom Ogilvy" wrote in message ... Workbooks.open "C:\Myfolder\Myfiles.xls" sPath = ActiveWorkbook.Path sPath would then equal "C:\MyFolder" or Dim bk as Workbook, sPath as String set bk = Workbooks.Open("C:\Myfolder\Myfiles.xls") sPath = bk.Path -- Regards, Tom Ogilvy Jim Carlock <anon@localhost wrote in message ... I've tried to use a number of items to get the proper path to the Excel spreadsheet that is opening but I keep missing. Sometimes the Excel spreadsheet takes it's own path from a previously opened spreadsheet. Sometimes it takes it from a parameter at: Tools, Options..., General tab, Default File Location: Specifically I am using VBA and trying to use: ThisWorkbook.Path but that is giving the results above. I need the correct path for the workbook / spreadsheet file that is opening up. How do I get that path? Thank you very much in advance. :-) -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
The path for any given Excel spreadsheet ???
I found out it was the query to the database that was
causing the problems. I thought the database would default to current directory that the spreadsheet was in, but if another sheet was opened, the current folder no longer applied and it actually changed to the network path of the other spreadsheet that was already open within Excel. I simply grabbed the absolute path to the .xls file and applied that to the front of the database name within the query. Thanks much Tom. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Tom Ogilvy" wrote in message ... ThisWorkbook refers to the workbook running the code. Code contained in a workbook_open event does not run until the workbook is opened. -- Regards, Tom Ogilvy Jim Carlock <anon@localhost wrote in message ... The problem is occuring on an Office 2k machine running Office 2K SP3 and it's occuring only when another spreadsheet is already open inside of Excel. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" <anon@localhost wrote in message ... I can't get it to duplicate on my home machine at the moment. Oof. :-( -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" <anon@localhost wrote in message ... Another way to describe the problem... I'm opening an Access database that resides inside the same path as the spreadsheet itself. I'm running into problems where ThisWorkbook inside of Workbook_Open() is seeing another workbook that is already open instead of seeing itself. I can see what's happening... The workbook isn't open yet inside of the Workbook_Open() method so ThisWorkbook refers to whatever workbook is currently open because Excel is doing a late binding to the spreadsheet. So there is some obscure thing available that points to the correct path of the xls file that is opening but it doesn't get put into the .Path variable until the spreadsheet is already open. But the spreadsheet already knows where it exists because it doesn't get confused and use the late binding .Path because it's using a variable located somewhere else. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Jim Carlock" <anon@localhost wrote in message ... Okay, I'm not opening a spreadsheet from a spreadsheet. I'm looking for the path before the spreadsheet actually opens. I might be using the wrong method. I've got code placed within the ThisWorkbook.Open method... Workbook_Open() method I've got the code placed there... sPath = ThisWorkbook.Path ThisWorkbook.Path returns a variety of information that is related to other workbooks (spreadsheets) that are opened up within Excel, information that is set by the variable (which I think is read from the registry) File, Options..., General tab, Default file location: I'm opening the spreadsheet by double-clicking on it from Windows Explorer. If another spreadsheet is open inside of Excel, ThisWorkbook.Path returns the path to the spread- sheet that is already open. I want the path to the spreadsheet that isn't open yet. The spreadsheet that is opening up and I'm wanting to obtain this path inside of the Workbook_Open() method. Hope that explains what's happening a little better. I'm writing code that will be used within Excel 2000 and Excel XP spread- sheets. -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! "Tom Ogilvy" wrote in message ... Workbooks.open "C:\Myfolder\Myfiles.xls" sPath = ActiveWorkbook.Path sPath would then equal "C:\MyFolder" or Dim bk as Workbook, sPath as String set bk = Workbooks.Open("C:\Myfolder\Myfiles.xls") sPath = bk.Path -- Regards, Tom Ogilvy Jim Carlock <anon@localhost wrote in message ... I've tried to use a number of items to get the proper path to the Excel spreadsheet that is opening but I keep missing. Sometimes the Excel spreadsheet takes it's own path from a previously opened spreadsheet. Sometimes it takes it from a parameter at: Tools, Options..., General tab, Default File Location: Specifically I am using VBA and trying to use: ThisWorkbook.Path but that is giving the results above. I need the correct path for the workbook / spreadsheet file that is opening up. How do I get that path? Thank you very much in advance. :-) -- Jim Carlock http://www.microcosmotalk.com Feel free to post back to the newsgroup! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moved spreadsheet changed link path | Excel Discussion (Misc queries) | |||
Is there a function to print file name/path on a spreadsheet? | Excel Worksheet Functions | |||
Why would hyperlinks in a spreadsheet change to an invalid path? | Excel Discussion (Misc queries) | |||
hyperlink navigation path path wrong in Excel 2003 | Excel Discussion (Misc queries) | |||
How can I replace the path of the linked spreadsheet in a cell? | Excel Worksheet Functions |