VB Code for a directory

[Marie Bayes]

Hi
Is there any code I can put into my macro (which opens a file) that will
always look for the file in the 'current' directory, ie, the directory that
the original file was opened in, so that if I move the directory it will
always find it?
Thanks in advance

 

[RichardSchollar]

Hi Marie

You need the following where you have the workbook open statement:

Workbooks.Open ThisWorkbook.Path & "\" & YourWorkbookName

Then this will always open the file named "YourWorkbookName" in the
same directory as the workbook that holds the macro code.

Hope this helps!

Richard

 

[Bob Phillips]

Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & Workbook.Name

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Marie Bayes]

So, will this statement need to be at the start of each of the macros, the
macros open several files so will I need to type this in for each file that
will be opened? And then in the line of code that opens the workbook, will
it look like this;

Workbooks.Open Filename:= _
"\myfile.xls"

Thanks

 

[Marie Bayes]

Thanks Richard

Hi Marie

You need the following where you have the workbook open statement:

Workbooks.Open ThisWorkbook.Path & "\" & YourWorkbookName

Then this will always open the file named "YourWorkbookName" in the
same directory as the workbook that holds the macro code.

Hope this helps!

Richard

 

[Marie Bayes]

That's great, thanks Bob. I presume this will work for Saveas. too?

 

[RichardSchollar]

Yes it will.

Richard

That's great, thanks Bob. I presume this will work for Saveas. too?

 

[Marie Bayes]

Thanks Richard, that has all worked a treat, much appreciated.

 

[Bob Phillips]

No, just make sure that you precede each file open with the code as shown
earlier. You could change the directory, but I personally prefer to be
explicit, not rely on being in any particular directory.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[JLatham]

Marie,
This should not affect you since you're opening an existing file. But when
you start a NEW file (File | New) it has no path, since it hasn't been saved
to disk yet. Saving such a file in code results in a save to the current
directory (usually your My Documents folder).

Just thought I'd mention that.