Hi everybody! My problem is this: I acces MS Excel from Visual Basic.
First I create Excel.Application object, and then I prompt user to open
a file. I do this with FindFile method. Upon execution of that method,
open dialog box is displayed which points to MyDocuments folder.
However, I would like to set the default path programatically.I tried
to do it by setting DefaultFilePath (and by doing chdir) before
executing FindFile, but it appears excel writes the default path to the
registry upon opening of the file, and only looks for that value.

For example: I set default path to c:\temp and open excel. Open dialog
points to c:\My Documents. I navigate to c:\temp and open any file from
there. Then I start my program again, I set default path to c:\work,
open dialog box now points to c:\temp because that is what excel has
written to registry
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\E xcel\Options the last
time.

I have tried using GetOpenFilename but same thing happens. Is there a
workaround for this other than using CommonDialog control or writing my
path dinamically to the registry because none of that seems very
appealing...

opi,
Depending the current drive, you may also need ChDrive. e.g.

Private Sub CommandButton1_Click()
Dim RetVal As Variant
MsgBox CurDir
ChDrive "C"
MsgBox CurDir
ChDrive "F"
ChDir "F:\Installs\"
RetVal = Application.GetOpenFilename()
MsgBox CurDir
End Sub

You obviously need valid drive and folder values for this to work.
This behaviour is why just get sharing violation if you try to delete the
folder pointed to by Application.GetOpenFilename(); because that folder is
set to the current directory.

NickHK

"opi" wrote in message
oups.com...
Hi everybody! My problem is this: I acces MS Excel from Visual Basic.
First I create Excel.Application object, and then I prompt user to open
a file. I do this with FindFile method. Upon execution of that method,
open dialog box is displayed which points to MyDocuments folder.
However, I would like to set the default path programatically.I tried
to do it by setting DefaultFilePath (and by doing chdir) before
executing FindFile, but it appears excel writes the default path to the
registry upon opening of the file, and only looks for that value.

For example: I set default path to c:\temp and open excel. Open dialog
points to c:\My Documents. I navigate to c:\temp and open any file from
there. Then I start my program again, I set default path to c:\work,
open dialog box now points to c:\temp because that is what excel has
written to registry
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\E xcel\Options the last
time.

I have tried using GetOpenFilename but same thing happens. Is there a
workaround for this other than using CommonDialog control or writing my
path dinamically to the registry because none of that seems very
appealing...

NickHK,
Thanks for your reply. I forgot to mention that I do use chdrive and
chdir, and I check for validity of the path with dir (not ideal, but
let's say that my path will always be valid). Problem is that no matter
what I set as DefaultFilePath, Excel uses the path set in the registry,
and after I navigate to desired folder and open a file there Excel
writes that path to the registry.

opi,
I seems like we are talking different things.
You're referring to Application.defaultpath, which determines the folder
when you click FileOpen, or a similar method.
I would imagine this is set when excel opens and even if changed would be
seen until the next time Excel is opened.
I am referring to application.getOpenFileName, which honours the CurDir
value.

NickHK

"opi" wrote in message
oups.com...
NickHK,
Thanks for your reply. I forgot to mention that I do use chdrive and
chdir, and I check for validity of the path with dir (not ideal, but
let's say that my path will always be valid). Problem is that no matter
what I set as DefaultFilePath, Excel uses the path set in the registry,
and after I navigate to desired folder and open a file there Excel
writes that path to the registry.

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!

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!

NickHK,
Thanks for your answer. I am glad we finally understand each other :)

It seems that what I need is GetOpenFileName API, as I don't like the
idea of writing to registry (possible permission issues) or having
custom Excel files. Thanks for all your help!