Saving Range Data to your Desktop
EE Pros.
I am looking for a simple Macro that when fired, copies and saves a set of range names as separate XLS sheets in a folder on the desktop. The folder name is driven by a particular cell. If the Folder already exists on the desktop, the macro will overwrite it; if it is a new name, it creates a new Folder and performs the copy. A message should show up on the original WS (where the data resides), "Data has been saved......."
That's it! Sample attached.
B.
Saving-each-Category.xlsm
I am looking for a simple Macro that when fired, copies and saves a set of range names as separate XLS sheets in a folder on the desktop. The folder name is driven by a particular cell. If the Folder already exists on the desktop, the macro will overwrite it; if it is a new name, it creates a new Folder and performs the copy. A message should show up on the original WS (where the data resides), "Data has been saved......."
That's it! Sample attached.
B.
Saving-each-Category.xlsm
ASKER
Gowflow,
Happy Holidays! Thanks for picking this up.
No. I will change the file name and folder name in the final version. If you note it in the code, I think I'll be able to find it and adapt the correct cell references. Or perhaps I should make it a range name and move it anywhere in the WS.
Thanks,
B.
Happy Holidays! Thanks for picking this up.
No. I will change the file name and folder name in the final version. If you note it in the code, I think I'll be able to find it and adapt the correct cell references. Or perhaps I should make it a range name and move it anywhere in the WS.
Thanks,
B.
fine it is as you like I will refer to C19 for the file name and C20 and it is easy it is 1 line of code.
Now something else you say:
Save each category in a sheet the category is also always in Col E and is Called 'Category1' and 'Category2' or this also can change ?? I prefer to work on a final version so you will hv minimum changes to the final version my whole routine is set I only need these fine tuning and will shoot it to you to check
gowflow
Now something else you say:
Save each category in a sheet the category is also always in Col E and is Called 'Category1' and 'Category2' or this also can change ?? I prefer to work on a final version so you will hv minimum changes to the final version my whole routine is set I only need these fine tuning and will shoot it to you to check
gowflow
ASKER
gowflow,
Understand. The challenge I have is that my final is a very large WB and has Company confidential info. in it so I generally have to "doctor up" the sample and then figure it out (that also gives me an educational experience.....generally).
No again. Actually in the production model there are 12 categories that will need to be saved, each with different names. And the Columns are actually B, C & D. My idea here was to use range names in each Category to simply copy and paste the range name into the backup folder with a category name I would create from a "helper cell/column".
I think that if we follow the original simple model, I'll be able to figure out how to "scale" it and reference out the right cells.
Does that make good sense?
B.
Understand. The challenge I have is that my final is a very large WB and has Company confidential info. in it so I generally have to "doctor up" the sample and then figure it out (that also gives me an educational experience.....generally).
No again. Actually in the production model there are 12 categories that will need to be saved, each with different names. And the Columns are actually B, C & D. My idea here was to use range names in each Category to simply copy and paste the range name into the backup folder with a category name I would create from a "helper cell/column".
I think that if we follow the original simple model, I'll be able to figure out how to "scale" it and reference out the right cells.
Does that make good sense?
B.
ASKER
With that said, if you can use the sample sheet to create a copy and paste macro that puts the categories in a folder on the desktop, I'll close the question and work on modifying.
Much thanks,
B.
Much thanks,
B.
Well for sure if you can manage to modify adapt that's fine I give you the tools and you cater it to your need. One thing still not clear is the following:
In the file you attached you have 1 name in Cell C19 John's worksheet and you have 2 categories Category1 and Category2 and you want each one to be saved in a separate file but you only gave 1 name John's worksheet so how to deal with this ? Shall we name the file:
John's worksheet - Category1.xls
John's worksheet - Category2.xls
or you want something diffrent ? as the same name John's worksheet only will be overritten by the second instance and you will only end up with the last Category beeing Category2.
Something else I can create a name range Category1 and assign it the data in that range but I feel (from IT experience that for a user it is too much handling to have to create ranges so the user will adapt to the IT requirements. I usually do it the other way arround I let the user be free as much as possible from hasltles of having to obey IT rules or requirment and I let the macro finds its way to be able to find the correct categories. So If you give me the boundaries or let say what would be the first occurence of a category or if you want we can agree on a certain string that when the macro encounter it is will start the category and when it counter the same string it will stop the category and start a new one. If you agree on this and your production workbbook allows a small change then we can say that you will simply put a special caracter or your choice in the begining of the category to specify that this cell is a Category I propose this character | (pipe) or this caracter ^ (shift 6) so you would have in your case
In Cell E4 |Category 1 or ^Category 1
In Cell E11 |Category 2 or ^Category 1
This way it would avoid you creating any named range and the only thing you would need is to put this caracter. If you find it ok let me know and not pls also let me know so I can finalize the macro accordingly.
gowflow
In the file you attached you have 1 name in Cell C19 John's worksheet and you have 2 categories Category1 and Category2 and you want each one to be saved in a separate file but you only gave 1 name John's worksheet so how to deal with this ? Shall we name the file:
John's worksheet - Category1.xls
John's worksheet - Category2.xls
or you want something diffrent ? as the same name John's worksheet only will be overritten by the second instance and you will only end up with the last Category beeing Category2.
Something else I can create a name range Category1 and assign it the data in that range but I feel (from IT experience that for a user it is too much handling to have to create ranges so the user will adapt to the IT requirements. I usually do it the other way arround I let the user be free as much as possible from hasltles of having to obey IT rules or requirment and I let the macro finds its way to be able to find the correct categories. So If you give me the boundaries or let say what would be the first occurence of a category or if you want we can agree on a certain string that when the macro encounter it is will start the category and when it counter the same string it will stop the category and start a new one. If you agree on this and your production workbbook allows a small change then we can say that you will simply put a special caracter or your choice in the begining of the category to specify that this cell is a Category I propose this character | (pipe) or this caracter ^ (shift 6) so you would have in your case
In Cell E4 |Category 1 or ^Category 1
In Cell E11 |Category 2 or ^Category 1
This way it would avoid you creating any named range and the only thing you would need is to put this caracter. If you find it ok let me know and not pls also let me know so I can finalize the macro accordingly.
gowflow
I did not see that you already posted a reply. The macro as it is now will create aduplicate of the actual sheet in a new workbook and save it on the desktop under the directory specified in C20 and the name specified in C19 if that's what you want here let me know I can shoot it for you, if you want to take in consideration my comments just posted above pls do so I have no problem to get this done in this question.
gowflow
gowflow
ASKER
gowflow,
"Thank you!"
The file folder should have the same name as "John's Data" or the title that is in C19 and then individual Workbooks for each Category which I'm using as a Named Range. In other words when the macro is triggered, it will autosave and write over, in the case of the example, 2 Categories or Workbooks in the Folder (Category 1 and Category 2); in my production model, I'll have 12 Workbooks that will each have a Sheet that has the Category info. for each Category. Your comments are correct.
As for the second comment...... I'm envisioning a simple pasteing of the Category as indicated in the Range Name. This will keep it simple;
I'll end up with 12 Range names that when the macro is fired, they will all be updated as .XLS Workbooks in a Folder on the desktop.
Make sense?
B.
"Thank you!"
The file folder should have the same name as "John's Data" or the title that is in C19 and then individual Workbooks for each Category which I'm using as a Named Range. In other words when the macro is triggered, it will autosave and write over, in the case of the example, 2 Categories or Workbooks in the Folder (Category 1 and Category 2); in my production model, I'll have 12 Workbooks that will each have a Sheet that has the Category info. for each Category. Your comments are correct.
As for the second comment...... I'm envisioning a simple pasteing of the Category as indicated in the Range Name. This will keep it simple;
I'll end up with 12 Range names that when the macro is fired, they will all be updated as .XLS Workbooks in a Folder on the desktop.
Make sense?
B.
ok fine my mistake I did not look to see the Ranges created you already have in sample Cat1Range and Cat2Range sorry my mistake to have overlooked this. Definitively it make sense no problem. Now when it comes to 12 workbook how you will do this you will copy I suppose the code in each and every one of them or you want the solution to sit on 1 workbook then to look them over as being in a same directory ?
gowflow
gowflow
ASKER
Yes. The Folder name on the desktop should be from the Cell in the original WB which is now in C20. In that Folder will be 12 Workbooks each with the range that has been copied from the orginial WB and the WB shall have the name of the range name. In other words, in the end, when you press the save data button, what results is a folder, on the desktop, with 12 Range Name driven WB's, with the contents only from each Range in Sheet1. Each time you fire the Macro, it will re-write to the Folder and recopy all of the WBs (no need to warn "WB Exists).......
Let me also give you the business context. These Categories are filled out during a discussion. As the discussion progresses, the person using the WB wants to save the findings. So they are going to be pressing the Save Data key probably after each of the Categories are completed. This way if the original WB gets corrupted, they will have a backup copy of the latest data.
Make sense?
B.
Let me also give you the business context. These Categories are filled out during a discussion. As the discussion progresses, the person using the WB wants to save the findings. So they are going to be pressing the Save Data key probably after each of the Categories are completed. This way if the original WB gets corrupted, they will have a backup copy of the latest data.
Make sense?
B.
ASKER
Wow gowflow!!! This looks great on the surface. Let me work on it for a few minutes to see if I can scale it into my production system. This is really really good.
B.
B.
ASKER
Can I get the Tab within the Workbooks to = the Range Name? So if I had a Range Name called Category1, then within the folder, the Tab name would be Category1, and so on......?
B.
B.
ASKER
Also, is there a way to use regular (not Active X) button to trigger the Macro? If not, how do i copy it or transfer it over? I'm getting a compatability error when I do a cut/paste on the button/control.......
B.
B.
for the activex button I always use this as it allows to but code there whereas the regular button just fire a macro. You simply need to copy/paste the button that is in this workbook onto you production one and then copy the code there. Unless you want the fancy surroundings ... then let me know I will look up this.
For the category by Tab you mean worksheet name ? I am confused you have the following
WB name = Range = Cat1Range
Worksheet Name = Tab = Cell C19 = John's Worksheet
How else you want it ?
gowflow
For the category by Tab you mean worksheet name ? I am confused you have the following
WB name = Range = Cat1Range
Worksheet Name = Tab = Cell C19 = John's Worksheet
How else you want it ?
gowflow
OK I hv fixed the button issue the macro that fires the Save Data button is GetCategories copy the Module1 to you production workbook and assign it the GetCategories and it should work fine. Now when you answer the Tab issue I will advise the change.
gowflow
Copy-of-Saving-each-Category.xlsm
gowflow
Copy-of-Saving-each-Category.xlsm
ASKER
Ha!!!! Your code is "too good"! You're not going to believe this. So, I put this in my production copy. Well, in addition to the Categories that I'm trying to save to the WB/File on the desktop, there are about 200 additional "range names". Your code began to copy ALL of them into the file. After watching Excel spin for 5 min., I got the hunch that it was copying all of the range names and I terminated the program and went to the file. Sure enough, it had copied over half of all the Ranges!!. I laughed for 2 minutes over this!
So what I need is somewhere in the code, to refer to the actual range names I need copied.
That should do it.
B.
So what I need is somewhere in the code, to refer to the actual range names I need copied.
That should do it.
B.
I thought you wanted to copy all the range names !!! seems you hve some that you want to copy and some you don't is that correct ? if you give me a prefix of all the Categories Say you all name them Cat ... then we can use this as a filter and you will only get those. Will wait till you advise what is the prefix.
Pls also advise about the Tab issue so we can hv both at the same time.
gowflow
Pls also advise about the Tab issue so we can hv both at the same time.
gowflow
ASKER
I did say that! It was totally my fault. I didn't think about the fact that it would copy all the range names. too funny.... anyway; "yes". All Range names that begin with Category, have a number, and then Survey. So....Category1Survey, Category2Survey, Category3Survey, etc.etc.
And the reason I need the "Survey" part is that I will be using the same code but a different range on another sheet. So if there is a way to "wild card" the number in the middle....perfect. If not, I'll live with simply, Category......
Thank you, thank you, thank you,
B.
And the reason I need the "Survey" part is that I will be using the same code but a different range on another sheet. So if there is a way to "wild card" the number in the middle....perfect. If not, I'll live with simply, Category......
Thank you, thank you, thank you,
B.
Noted but the survey part is not clear to me. You mean to say there may be ranges called Category12Action or whatever that you would not want to save ??? Is that what you mean ? coz the easiest would be Category but if you have cases like above then we can be more specific.
Maybe I am not expressing myself well but if you can give me examples of range names that you would want and some you wouldn't so I could understand why you need to filter Category*Survey
gowflow
Maybe I am not expressing myself well but if you can give me examples of range names that you would want and some you wouldn't so I could understand why you need to filter Category*Survey
gowflow
ASKER
So I have two different worksheets that need to save Categories on the desktop in the production version; but only one Worksheet is used in a session.
My plan is to take the code and replicate it (with a new name) so as to work on the other Worksheet... but that is way beyond the scope of my original question (so don't worry about it). With this in mind, naming conventions become rather important. So for this project, if we could identify the following range names (the Example you asked for), that would be great;
Category1Survey
Category2Survey
Category3Survey
Category4Survey:Category12
That's it. Then later when I have a different cut/paste/save set of ranges, I will simply define them differently and adjust the code by creating a different macro. Those range names will be:
Category1Workshop:Category
That is why I was asking if we could use "Category"&*"Survey" as the designator with the difference being the number in the middle.
Make sense?
Thanks again,
B.
My plan is to take the code and replicate it (with a new name) so as to work on the other Worksheet... but that is way beyond the scope of my original question (so don't worry about it). With this in mind, naming conventions become rather important. So for this project, if we could identify the following range names (the Example you asked for), that would be great;
Category1Survey
Category2Survey
Category3Survey
Category4Survey:Category12
That's it. Then later when I have a different cut/paste/save set of ranges, I will simply define them differently and adjust the code by creating a different macro. Those range names will be:
Category1Workshop:Category
That is why I was asking if we could use "Category"&*"Survey" as the designator with the difference being the number in the middle.
Make sense?
Thanks again,
B.
ok fine no problem Survey will always be the last name like can you run into something like
Category123SurveyAction ??? or it will always be "Category" &*"Survey" like you put it ?
gowflow
Category123SurveyAction ??? or it will always be "Category" &*"Survey" like you put it ?
gowflow
ASKER
It will always be Category.....a number, 1-12, and Survey.
TY,
B.
TY,
B.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I made it is easy for you to substitute Survey by Workshop in your next worksheet, but make sure you type it in lowercase !!!
Enjoy !!!!
Finally, I am depply sorry for all these back and fourth questions prior to delivering the solution as I always like to deliver a solution that works correctly from the first time and that answers user's request correctly.
gowflow
Enjoy !!!!
Finally, I am depply sorry for all these back and fourth questions prior to delivering the solution as I always like to deliver a solution that works correctly from the first time and that answers user's request correctly.
gowflow
ASKER
gowflow,
You have been one of the finest EE Pros. I've had the honor to work with. I love this network; it makes us all better at being very clear and specific to translate business requirements into Excel deliverables and value. You are very good at driving the right questions out.
I'm working on the integration and do have one question;
On this line of code
If LCase(Left(Nam.Name, 8)) = "Category" And LCase(Right(Nam.Name, 6)) = "Survey" Then
Is this case sensitive? Does this allow for a number in the middle? (example: Category1Survey, Category2Survey, etc.?
I got it to run but it didn't copy any of the ranges so I think my issue is in the range naming convention.
Please advise,
B.
You have been one of the finest EE Pros. I've had the honor to work with. I love this network; it makes us all better at being very clear and specific to translate business requirements into Excel deliverables and value. You are very good at driving the right questions out.
I'm working on the integration and do have one question;
On this line of code
If LCase(Left(Nam.Name, 8)) = "Category" And LCase(Right(Nam.Name, 6)) = "Survey" Then
Is this case sensitive? Does this allow for a number in the middle? (example: Category1Survey, Category2Survey, etc.?
I got it to run but it didn't copy any of the ranges so I think my issue is in the range naming convention.
Please advise,
B.
for sure it will not copy anything !!!!
the line I put in the code is the following:
If LCase(Left(Nam.Name, 8)) = "category" And LCase(Right(Nam.Name, 6)) = "survey" Then
and not
If LCase(Left(Nam.Name, 8)) = "Category" And LCase(Right(Nam.Name, 6)) = "Survey" Then
As lcase is specifically LOWER CASE !!! this is made on purpose case by mistake you had names like
CaTegory1surVey or catEGORY12SurvEY or category5survey or CATEGORY34SURVEY
it will pick all of them !!!
gowflow
the line I put in the code is the following:
If LCase(Left(Nam.Name, 8)) = "category" And LCase(Right(Nam.Name, 6)) = "survey" Then
and not
If LCase(Left(Nam.Name, 8)) = "Category" And LCase(Right(Nam.Name, 6)) = "Survey" Then
As lcase is specifically LOWER CASE !!! this is made on purpose case by mistake you had names like
CaTegory1surVey or catEGORY12SurvEY or category5survey or CATEGORY34SURVEY
it will pick all of them !!!
gowflow
ASKER
This has been one of the best experiences I have had with an EE Professional! This was a difficult Macro that I would never have been able to put together on my own. I would have awarded 2000 points for this one if I could.
Gowflow, I really appreciate your professionalism and talent on this. And I hope to work with you again in the future.
All the best,
B.
Gowflow, I really appreciate your professionalism and talent on this. And I hope to work with you again in the future.
All the best,
B.
Thank you for your kind comments and I do appreciate your straight forwardness and I am totally fullfilled when I know that my solution put a smile on a face this is my biggest reward far beyond any points awarding. Belive it or not I do learn everyday by the questions that are asked here and I use them in my daily developpment in my current business So I thank you for your question as it is the first time I deal with Named ranges and I am glad I know how to deal with them !!! :)
Pls feel free to put a link in here to any other question you may need macro developpment with as it is my pleasure to do so.
Tks again and hv a great time.
gowflow
Pls feel free to put a link in here to any other question you may need macro developpment with as it is my pleasure to do so.
Tks again and hv a great time.
gowflow
ASKER
gowflow,
Just when I thought I had this nailed down, one of my users/customers decided what they want is "version" control. Meaning that instead of simply saving the Categories, they would like another folder within the one on the desktop, that is labled "Name" (C20) and a version identifier (v1, v2, v3, etc.). That way they not only save the range names, but also save a set of versions that they can go back to if necessary.
I think what you did for me is 90% of that modification. The only mod. is to add another folder within the folder that gets created each time the Save Macro is fired and the folder has a version designator. The data (i.e. Categories) then get saved to that new folder.
I will need to author another question to resolve this but it is probably unlikely to be picked up by another EE Pro. Is this something you'd like to pursue if I pass you the reference number or does this add a lot of complication?
Thank you,
B.
Just when I thought I had this nailed down, one of my users/customers decided what they want is "version" control. Meaning that instead of simply saving the Categories, they would like another folder within the one on the desktop, that is labled "Name" (C20) and a version identifier (v1, v2, v3, etc.). That way they not only save the range names, but also save a set of versions that they can go back to if necessary.
I think what you did for me is 90% of that modification. The only mod. is to add another folder within the folder that gets created each time the Save Macro is fired and the folder has a version designator. The data (i.e. Categories) then get saved to that new folder.
I will need to author another question to resolve this but it is probably unlikely to be picked up by another EE Pro. Is this something you'd like to pursue if I pass you the reference number or does this add a lot of complication?
Thank you,
B.
no not at all I will be glad to assist. But first let me grasp here what you want as it is not v clear to me can you put this in a simpler way so we can be efficient and quick ?
Offhand as a first comment when it comes to version my default always is datetime stamp with the name so it can be meaningful to the user and easier to pick a certain version will this be ok with your client ? still waiting u clarify these folders and what they should contain.
gowflow
Offhand as a first comment when it comes to version my default always is datetime stamp with the name so it can be meaningful to the user and easier to pick a certain version will this be ok with your client ? still waiting u clarify these folders and what they should contain.
gowflow
ASKER
Thank you! Let me give you this and then I'll post the question in a short while.
AS/IS
When you press the Save Data Button, each Category is saved to a workbook/xls, individually, that resides in a file on the Desktop. It overwrites the previous .xls sheet within the WB. That means that within the WB, the latest saved copy of each category resides.
TO/BE
When you press the Save Data Button, each Category is saved to a NEW Folder within the Folder on the Desktop but that Folder should have a New Name (each time it is saved) that reflects a version (v1, v2, v3, etc.). So if you press the Save Data Button 3 times during the open session (in the original Workbook), you would end up with a Single Folder on your desktop like you do now; BUT..... inside the single folder would be 3 new Folders labeled with the name we derived from C20 and a Version number that increments as each new Folder was added. Inside each of those folders would be the Categories (Range Names) that were saved AT THAT TIME (so no overriding data).
Make sense?
B.
AS/IS
When you press the Save Data Button, each Category is saved to a workbook/xls, individually, that resides in a file on the Desktop. It overwrites the previous .xls sheet within the WB. That means that within the WB, the latest saved copy of each category resides.
TO/BE
When you press the Save Data Button, each Category is saved to a NEW Folder within the Folder on the Desktop but that Folder should have a New Name (each time it is saved) that reflects a version (v1, v2, v3, etc.). So if you press the Save Data Button 3 times during the open session (in the original Workbook), you would end up with a Single Folder on your desktop like you do now; BUT..... inside the single folder would be 3 new Folders labeled with the name we derived from C20 and a Version number that increments as each new Folder was added. Inside each of those folders would be the Categories (Range Names) that were saved AT THAT TIME (so no overriding data).
Make sense?
B.
Yes got it just post the question and I hv the solution already. Pls post the link here.
To make it easier for you just copy the last para of your reply after TOBE and oit will be the question and attach the file to give a chance to other Experts as well.
gowflow
To make it easier for you just copy the last para of your reply after TOBE and oit will be the question and attach the file to give a chance to other Experts as well.
gowflow
Are you intrested to ge tthe solution ? I am waiting for the question !!!
gowflow
gowflow
ASKER
Gowflow,
Sorry for the delay.
2013-01-03 at 12:21:30ID: 27983847
Here it is.
B.
Sorry for the delay.
2013-01-03 at 12:21:30ID: 27983847
Here it is.
B.
Done check out the reply
gowflow
gowflow
gowflow