VB Code for a directory
 

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

VB Code for a directory
 

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




On 25 Jan, 08:13, Marie Bayes
wrote:
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

VB Code for a directory
 

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

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"RichardSchollar" wrote in message
ups.com...
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




On 25 Jan, 08:13, Marie Bayes
wrote:
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

VB Code for a directory
 

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

"RichardSchollar" wrote:

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




On 25 Jan, 08:13, Marie Bayes
wrote:
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

VB Code for a directory
 

Thanks Richard

"RichardSchollar" wrote:

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




On 25 Jan, 08:13, Marie Bayes
wrote:
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

VB Code for a directory
 

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

"Bob Phillips" wrote:

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

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"RichardSchollar" wrote in message
ups.com...
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




On 25 Jan, 08:13, Marie Bayes
wrote:
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

VB Code for a directory
 

Yes it will.

Richard

On 25 Jan, 10:41, Marie Bayes
wrote:
That's great, thanks Bob. I presume this will work for Saveas. too?



"Bob Phillips" wrote:
Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & Workbook.Name

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"RichardSchollar" wrote in message
oups.com...
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

On 25 Jan, 08:13, Marie Bayes
wrote:
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- Hide quoted text -- Show quoted text -

VB Code for a directory
 

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

"RichardSchollar" wrote:

Yes it will.

Richard

On 25 Jan, 10:41, Marie Bayes
wrote:
That's great, thanks Bob. I presume this will work for Saveas. too?



"Bob Phillips" wrote:
Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & Workbook.Name

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

"RichardSchollar" wrote in message
oups.com...
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

On 25 Jan, 08:13, Marie Bayes
wrote:
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- Hide quoted text -- Show quoted text -

VB Code for a directory
 

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)


"Marie Bayes" wrote in message
...
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

"RichardSchollar" wrote:

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




On 25 Jan, 08:13, Marie Bayes
wrote:
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

VB Code for a directory
 

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.

"Marie Bayes" wrote:

Thanks Richard

"RichardSchollar" wrote:

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




On 25 Jan, 08:13, Marie Bayes
wrote:
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