The path for any given Excel spreadsheet ???

  • Thread starter Thread starter Jim Carlock
  • Start date Start date
J

Jim Carlock

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. :-)
 
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
 
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.
 
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.
 
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.
 
ThisWorkbook refers to the workbook running the code. Code contained in a
workbook_open event does not run until the workbook is opened.
 
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.
 
Back
Top