| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro Help (Uppercase multiple ranges?) | Excel Discussion (Misc queries) | |||
| copy and use vba code | New Users to Excel | |||
| Event (BeforeSave) - How to test VBA code? Dave P. can you hear me now? | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Linear Mode
