Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change directory based on cell content
Hi guys,
I am new to Excel and have created a document that has a drop down list at the top to change the header from 'Bank' to 'Savings'. I read in the discussion group how to automatically save a file using a cell reference. I must change directories, before saving and so far what I have (and it works) is: ChDir "G:\SAVINGS\Pending WRO" ActiveWorkbook.SaveAs Filename:=Range("b8").Value What I need to accomplish is in the active workbook, if Cell A1 = 'Savings', then ChDir "G:\SAVINGS\Pending WRO" ActiveWorkbook.SaveAs Filename:=Range("b8").Value But if Cell A1 = 'Bank' then ChDir "G:\Bank\Pending WRO" ActiveWorkbook.SaveAs Filename:=Range("b8").Value Thanks for any assistance that can be provided. -- Linda |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change directory based on cell content
I am new to Excel and have created a document that has a drop down list at
the top to change the header from 'Bank' to 'Savings'. I read in the discussion group how to automatically save a file using a cell reference. I must change directories, before saving and so far what I have (and it works) is: ChDir "G:\SAVINGS\Pending WRO" ActiveWorkbook.SaveAs Filename:=Range("b8").Value What I need to accomplish is in the active workbook, if Cell A1 = 'Savings', then ChDir "G:\SAVINGS\Pending WRO" ActiveWorkbook.SaveAs Filename:=Range("b8").Value But if Cell A1 = 'Bank' then ChDir "G:\Bank\Pending WRO" ActiveWorkbook.SaveAs Filename:=Range("b8").Value Maybe this line of code does what you want? ChDir "G:\" & Range("A1").Value & "\Pending WRO" Personally, I wouldn't bother with setting the ChDir at all; I would eliminate the ChDir statement completely and just prepend the path onto the value from B8... ActiveWorkbook.SaveAs Filename:="G:\" & Range("A1").Value & _ "\Pending WRO\" & Range("B8").Value Rick |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change directory based on cell content
I should have mentioned in my post the reasons I need to change the directory
is: 1 - multiple users of the form who will usually save the original document to their own drive, and 2 - their could be a file with the same name and the only distinction is the sub directory 'Bank' or 'Savings' I will try what you have suggested. Thanks for your help, I will let you know. -- Linda "Rick Rothstein (MVP - VB)" wrote: I am new to Excel and have created a document that has a drop down list at the top to change the header from 'Bank' to 'Savings'. I read in the discussion group how to automatically save a file using a cell reference. I must change directories, before saving and so far what I have (and it works) is: ChDir "G:\SAVINGS\Pending WRO" ActiveWorkbook.SaveAs Filename:=Range("b8").Value What I need to accomplish is in the active workbook, if Cell A1 = 'Savings', then ChDir "G:\SAVINGS\Pending WRO" ActiveWorkbook.SaveAs Filename:=Range("b8").Value But if Cell A1 = 'Bank' then ChDir "G:\Bank\Pending WRO" ActiveWorkbook.SaveAs Filename:=Range("b8").Value Maybe this line of code does what you want? ChDir "G:\" & Range("A1").Value & "\Pending WRO" Personally, I wouldn't bother with setting the ChDir at all; I would eliminate the ChDir statement completely and just prepend the path onto the value from B8... ActiveWorkbook.SaveAs Filename:="G:\" & Range("A1").Value & _ "\Pending WRO\" & Range("B8").Value Rick |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to change directory based on cell content
Hi Rick,
When I tried your first suggestion: ChDir "G:\" & Range("A1").Value & "\Pending WRO", it saved the file to the current directory I was working in. Your second suggestion works perfectly. Thank you for all your help -- Linda "Rick Rothstein (MVP - VB)" wrote: I am new to Excel and have created a document that has a drop down list at the top to change the header from 'Bank' to 'Savings'. I read in the discussion group how to automatically save a file using a cell reference. I must change directories, before saving and so far what I have (and it works) is: ChDir "G:\SAVINGS\Pending WRO" ActiveWorkbook.SaveAs Filename:=Range("b8").Value What I need to accomplish is in the active workbook, if Cell A1 = 'Savings', then ChDir "G:\SAVINGS\Pending WRO" ActiveWorkbook.SaveAs Filename:=Range("b8").Value But if Cell A1 = 'Bank' then ChDir "G:\Bank\Pending WRO" ActiveWorkbook.SaveAs Filename:=Range("b8").Value Maybe this line of code does what you want? ChDir "G:\" & Range("A1").Value & "\Pending WRO" Personally, I wouldn't bother with setting the ChDir at all; I would eliminate the ChDir statement completely and just prepend the path onto the value from B8... ActiveWorkbook.SaveAs Filename:="G:\" & Range("A1").Value & _ "\Pending WRO\" & Range("B8").Value Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to change a cell color based on its content using macro? | New Users to Excel | |||
Change a cell based on current content. | Excel Programming | |||
how do I change the content of one cell based on another? | Excel Discussion (Misc queries) | |||
Change the data in one cell based on content of another. | Excel Discussion (Misc queries) | |||
How do I change content of a cell based on date | Excel Worksheet Functions |