Problem with default file path
opi,
I didn't fully take in this was from VB6.
Changing the .DefaultFilePath will not affect Excel until the next it
starts.
Changing CurDir from VBIDE/compiled app does not change its value for Excel.
So I guess one way would be to set that Reg value before you start Excel.
Or open your own Excel file which has a WB_Open rutine that sets the CurDir
value.
Or use the GetOpenFileName API, which has an InitialDir option.
NickHK
"opi" wrote in message
oups.com...
NickHk,
Thanks for your reply.
This is the code snippet:
' Excel objects
Dim oApp As Excel.Application
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range
Dim Status As Boolean
Dim fname As Variant
' Start Excel and get Application object.
Set oApp = CreateObject("Excel.Application")
oApp.Visible = False
If In_Directory < "" And Dir(In_Directory, vbDirectory) < "" Then
oApp.DefaultFilePath = In_Directory
fname = oApp.GetOpenFilename(filefilter:="Excel Files (*.xls),
*.xls")
End If
oApp.Workbooks.Open (fname)
Status = True
In_Directory is always a valid path.
This does not work, because it seems when I create Excel object, it
reads default path from registry, and ignores what I've set in
DefaultFilePath.
Now, I've tried this too:
' Excel objects
Dim oApp As Excel.Application
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range
Dim Status As Boolean
Dim fname As Variant
If In_Directory < "" And Dir(In_Directory, vbDirectory) < "" Then
ChDir In_Directory 'lets say this is always valid and does not
need chdrive
End If
' Start Excel and get Application object.
Set oApp = CreateObject("Excel.Application")
oApp.Visible = False
fname = oApp.GetOpenFilename(filefilter:="Excel Files (*.xls),
*.xls")
oApp.Workbooks.Open (fname)
Status = True
This also does not work, as GetOpenFilename completely disregards the
chdir, and always uses the path from the registry.
This is what I think happens in the first example: when I start Excel,
it reads path from registry and holds it somewhere. Then I set
DefaultFilePath and Excel writes this value to registry, but still uses
the old value in GetOpenFilename.When I start Excel for the second
time, Excel again reads the path from the registry, and now it is the
path I've set in the first go.
In the second example, Excel constantly uses the path from the
registry, and completely ignores ChDir.
What I need is to start Excel, and when I execute GetOpenFilename the
open dialog box that opens points to the path that I specify as an
argument somewhere in the procedure.
I thought this would be an easy thing, but I've been stuck in here for
two days now so any help is greatly appreciated!
|