File Open Dialog

**tommy_gtr** · 10-25-2007, 04:14 AM

Hello, I would like to do a macro which will open another excel file through Open Dialog, so I use

Please Login or Register  to view this content.

.
It's ok, but I want to open this "Open Dialog" in a location where my ActiveWorkbook is saved. How can I do this?

I tried something like this ...

Please Login or Register  to view this content.

but this gives current workbook path into filetype box.

Can anyone help, please???

Thanks

Tomas

**broro183** · 10-25-2007, 05:47 AM

HI Tomas,

Try this:

Please Login or Register  to view this content.

There are ways of checking what the current directory is before changing it & then resetting this after you have run your macro but I'm not sure if you actually need/want that level of detail - let us know if you do...

Note, I just happened to try & test this on a file (activeworkbook) which was opened from a zipped folder first & it doesn't seem to like that but it looks ok otherwise.

hth
Rob

**dominicb** · 10-25-2007, 06:27 AM

Good morning

I was watching this post, to see what cropped up as I was trying something last week using GetOpenFilename and wanted it to open at a predefined location. I too used ChDir but that didn't work - broro183's code didn't do anything for me either.

The annoying thing is that the GetSaveAsFilename has an "InitialFileName" parameter that can be used for this - GetOpenFilename doesn't have this option and the help file doesn't mention it.

I don't like to say "never" with Excel 'cos there's nearly always a workaround. Anyone else know...

DominicB

**tommy_gtr** · 10-25-2007, 07:29 AM

Hello,

at first THANK YOU both for your answers.

I tried broro183 code and it's work, but only if I have saved my excel file on local computer. If I have my file on server, it doesn't work. It always open dialog in local computer location "C:\Documents and Settings\...".

And I work with my file, which is saved on server :-(

Any other idea, please???

Tommy

**broro183** · 10-25-2007, 07:34 AM

good morning/night here

(it's working for me tonight but it may be just b/c I'm on a stand alone home PC at the moment)

I'm getting a bit dopy - so I'm off to bed. Two parting questions though...

Has the active file been saved?
I don't think chdir will work before a file's saved.

Is the desired default folder on another network drive?
If so, Andy Pope gives an example of the use of chdrive in the below link (which can be modified to use the activeworkbook):
http://www.ozgrid.com/forum/showthread.php?t=57617
& this is discussed more in:
http://www.ozgrid.com/forum/showthread.php?t=41488

Also, I've glanced at the Helpfiles for "dir" & am sure we should be to tie this in but don't understand quite how...

The annoying thing is that the GetSaveAsFilename has an "InitialFileName" parameter that can be used for this - GetOpenFilename doesn't have this option and the help file doesn't mention it.

I don't like to say "never" with Excel 'cos there's nearly always a workaround. Anyone else know...

Dominic, I agree, so many options - we just need to identify them. Speaking of workarounds, what happens if you open a GetSaveAsFilename dialog box & use the InitialFilename & then cancel out of it. Does this change the directory, which should then stay changed for the GetOpenFileName dialog?

goodluck to you both!

hth
Rob

**tommy_gtr** · 10-25-2007, 08:23 AM

Dear broro183,

Thank you for links, I check it :-)

I don't understand how it works...I have saved my file on server and chdir doesn't work. Only thing I want, is open the dialog in a same location as my file is saved (no need to click through a lot of folders to find it).

But if I have saved my file on local machine then chdir works.

Is this possible??? :-(

tommy

**olieviep** · 10-26-2007, 03:30 AM

I had a simular problem.
I solved it with

drive = Left(ActiveWorkbook.Path, 1)
ChDrive drive
ChDir ActiveWorkbook.Path

kind regards

**tommy_gtr** · 10-26-2007, 04:28 AM

Dear olieviep,

thank you for your reply.

It really works ... great :-)

Have a nice day.

tommy

**mudraker** · 10-26-2007, 07:36 AM

olieviep

Please read forum rules & wrap your VBA code

A thread with the rules is available at the top of each forum or see links below

If you do not understand the VBA code wrap instructions have a look at my last reply in this thread
http://www.excelforum.com/showthread.php?t=583950