Autosave workbook into a specific folder

[whatsmyname]

Hello again,

I have 2 codes. One, autosaves the excel file as a certain value that is contained in a certain cell. The other one is a code that I copied off of a website that looks for a certain folder and then creates one if it doesn't exist.

I would like to combine the 2 codes. I still want the first code to do what is supposed to do. But, I would like it to look for a certain folder that is the value of another cell and then create that folder if it doesn't exist. And then save the file into that folder.

Any and all help will be greatly appreciated. I have attached the 2 codes below. I am still going to play with it but I am not having any luck, because my experiance is very slim.

Thank you all.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[JBeaucaire]

You're saving the workbook every time the user clicks on a new cell? That makes any mistakes the user makes instantly permanent.

[whatsmyname]

You're saving the workbook every time the user clicks on a new cell? That makes any mistakes the user makes instantly permanent.

No sir,

That is not what I want. Someone wrote the autosave code for me. I am still very ignorant to VBA. Slowly learning though.

What I would like the code to do is:

When the value of cell C:13 changes I would like the workbook to save itself as that value.

But, I would also like the code to look for a folder that is the value of another cell, if it finds that folder then save the workbook in there. If it does not then create the folder and then save the workbook in there.

I'm so sorry for not being clear enough before.

[JBeaucaire]

In ThisWorkbook module:

Please Login or Register  to view this content.

In the Sheet1 sheet module:

Please Login or Register  to view this content.

Change the D12 reference to the correct cell on sheet1 that has the path to use.

[whatsmyname]

Thank you JBeaucaire,

How do i get the code to activate though. On the other code it would automatically save right after the user finishes entering the value into C13. Though for this code we should have it autosave or run the code right after the user finishes entering the value into the second cell "H13".

I will try to figure this out on my own, but thats a long shot. Lol.

Thank you for all your help.

[JBeaucaire]

Wouldn't it be simpler to have the macro trigger off the entry of a value into cell C13 alone? If a value is missing from H13, the macro could prompt the user to select a folder, and enter that into H13 for you, then proceed. I find it easier to click on folders to select them rather than having to type out pathnames in a cell.

[whatsmyname]

Wouldn't it be simpler to have the macro trigger off the entry of a value into cell C13 alone? If a value is missing from H13, the macro could prompt the user to select a folder, and enter that into H13 for you, then proceed. I find it easier to click on folders to select them rather than having to type out pathnames in a cell.

I do agree. But, in C13 it's a list of 17000+ different well names. And once the user enters in the well name that they are looking for then the rest of the areas that are on the spreadsheet autopopulate based off of the value in cell C13. It's a vlookup formula in all the other cells. Then in cell H13 is where the user will type in their initials.

So ultimately I am wanting the speedsheet to autossave itself based off of the well name (C13), but save it into a folder based off of the users initials (H13). The user's of the spreadsheet are not very computer savy. They can barely type in the correct well name most of the time. lol. I would also like to somehow get to also make another folder based off of the value of another cell which is the date. But, that might be a little complicated because I want it to work off of the month and year of the date and not the day. That one is not priority though.

Essentially what I am trying to achieve is:

Month and Year Date Folder\
User Initial's Folder/
Well name File.xls

And have it do all this automatically, either after the user finishes typing in a certain cell, which I prefer because I cannot rely on them to save the file everytime, or when they click the print or save button.

Like I said though, the Month and Year Date Folder is not priority. I am trying to learn how to do this in the proccess of trying to do it. But, all I am capable of doing really is reading the code and figuring out what it's doing. I'm not too capable of writing it yet. lol.

Thank you for your help! It is greatly appreciated.

[whatsmyname]

I'm sorry I just re-read your post.

Yes, it would be great if it triggered off of the value in cell C13. But the "initial" cell H13 is entered in after the value in cell C13 is entered. So, if we could maybe get it to trigger just off of cell H13 and then if there is no date in the date cell M9, then maybe we can pop up a userform for the user to enter the date? What do you think?

[JBeaucaire]

What would the date be, just today? We can fill in today's date without needing user popups...

We can also just do a test for all 3 cells you want to watch. When all 3 are filled out...presto!

What would be an example of the cells data and the resulting full path and filename?

[whatsmyname]

What would the date be, just today? We can fill in today's date without needing user popups...

We can also just do a test for all 3 cells you want to watch. When all 3 are filled out...presto!

What would be an example of the cells data and the resulting full path and filename?

No, the date would would vary.

I do like the test idea. The main thing I want to be sure of though, is not letting the user mess up the original spreedsheet.

An example would be something like:

C:\Desktop\June2010\K.H.\SJ 32-9 #6Y.xls
[Date] [Initials] [File]

I hope I explained that right.

[JBeaucaire]

Try this...be sure to edit the macro for the date cell, I put it in P13:

Please Login or Register  to view this content.

[whatsmyname]

Which cell triggers the macro? I cannot seem to get it to trigger to save.

[JBeaucaire]

The macro is in the sheet module, correct?

Check and make sure macros haven't gotten turned off accidentally by code interruption during your testing. Put this in the Immediate Window to turn them back on:

Please Login or Register  to view this content.

Or save, close Excel, restart...


The macro is being triggered each time a change is made in C13, H13 and P13. It checks all 3 cells to make sure they are filled in before proceeding.

[whatsmyname]

The macro is in the sheet module, correct?

Check and make sure macros haven't gotten turned off accidentally by code interruption during your testing. Put this in the Immediate Window to turn them back on:

Please Login or Register  to view this content.

Or save, close Excel, restart...


The macro is being triggered each time a change is made in C13, H13 and P13. It checks all 3 cells to make sure they are filled in before proceeding.

Yes I have it in the sheet1 module. The security level on my macros are low. I'm not sure how to turn them on or off though.

What's the immediate window?

Also, it's a combo box on my my C13 and H13, does that make a difference?

[whatsmyname]

Okay,

Did get it to work when I set it up to work with cells that have no validation lists or comboboxes.
I did get an error saying that the file path doesn't exist. So, I created a directory and it did automatically save in there. I was curious if there is a way to have it create the directory if it doesn't exist. Also, I was going to see if we could reverse the 2 folders. Meaning that you would open the "date" folder then look at the "Initial" folder and open that and then the file would be in there.

[JBeaucaire]

the MkDir fPath lines of code are supposed to create the directory for you if it is missing. When the error occurred, what was the value of fPath?

Leaving for a meeting, back in about 3 hours.

[whatsmyname]

the MkDir fPath lines of code are supposed to create the directory for you if it is missing. When the error occurred, what was the value of fPath?

Leaving for a meeting, back in about 3 hours.

The value is /K.H./November2010. It works if I make the directory. But, it is not automatically creating the directory for me.

It Highlights this line of code yellow:

Please Login or Register  to view this content.

Also is there a way to reverse the directories too be like this:

November2010/K.H./??????.xls

I was trying to mess with it but I cannot seem to get it to work.

Also, why did it work when I changed everything to regular cells and not with my original cells with the combobox?

Thank you for all your help.

[JBeaucaire]

I don't know anything about your comboboxes so my code doesn't reference comboboxes. You would have to adapt that.

My apologies, I didn't create the full path in the fPath variable. Play around with this and see if it does better for making the directories properly:

Please Login or Register  to view this content.

[whatsmyname]

Thank you so much for all your help JBeaucaire!!!! I let you know if I get it to work like I need too.

[whatsmyname]

I cannot seem to get it too create a folder if it doesn't exist. I'll keep playing with it and see. Also, I need to compare the differences to see if I can figure it out on my own. But, with the new code it doesn't look for the second folder....the "initial" folder.

[whatsmyname]

Okay I was able to get it too create the sub folder ("H13") but not the main folder ("P13").

I wonder why?

[JBeaucaire]

The code only tries to make one folder. It's not an all-encompassing "create all folders and subfolders" macro, yet. On my site, however, I've already got a macro to do just that...make all folders in a string for you.

Make Folders and Subfolders

Let's add that macro into your module, and run it as shown. First, place this into a regular code module (Insert > Module)

Please Login or Register  to view this content.

Then, here's your macro edited to call that function to create all the folders as needed:

Please Login or Register  to view this content.

[whatsmyname]

Okay with this code:

Please Login or Register  to view this content.

I get this error:

Run-time error '1004':

Microsoft Office Excel cannot access the file 'C:\Documents and Settings\Kori.LAP-SERVICE024\Desktop\December2010\K.H.'.
There are several possible reasons:



-The file name or path does not exist.
-The file is being used by another program.
-The workbook you are trying to save has the same name as a


What sould be going on?

[whatsmyname]

Sorry, just seen your reply. Let me try that.

[whatsmyname]

Do I place the code off of your site in my sheet1 or in another module?

[JBeaucaire]

Read post #22, follow those instructions.

[whatsmyname]

Thank you so much!!!!!!! It works perfectly!!! So much better than what I was wanting!

I have one little problem though. I got it to work fine on my laptop but, when I put it on my desktop, I get an error that says it "can't find project or library", and it highlights the first cell that we reference. What does that mean and how do i fix that? I tried following the help section but, I'm not sure what to do.

[JBeaucaire]

In the VBEditor, check the Tools > References and make sure you have them set the same on both computers.

[whatsmyname]

Okay,

It is says that I'm missing my "RXRadio Capture 1.0 Type Library". So I'm copying the files that are not on my desktop off of my laptop and then copying them too my desktop.

That should work, right.......?

[JBeaucaire]

That's beyond the scope of this thread, I'm afraid. Hope it turns out to be simple.

[whatsmyname]

Yes sir it was simple.

Im trying to figure out how to change the pattern in which you type in the cells to trigger the autosave now. I've been playing with it but not having much luck. Anyways, Thank you very much for all your help. It was greatly appreciated. It works better than I imagined. Enjoy your dinner!!!

[whatsmyname]

Okay, i can't figure it out. How would I change the order in which to type in the cells to trugger the autosave?

[JBeaucaire]

Too hard to keep working blind like this. Please post a desensitized version of your workbook so we can get this done in a couple of posts...32 and growing, hehe.

[whatsmyname]

Okay, did that. You would just enter in the location, then the Salesman, then the date and it autosaves. YOu might have to change the destination in your code. No matter what though you have to enter the date last. As long as you do that it will autosave. If I comment out the combo box code it will work. But, like I said before, everyone was throwing a fit that I took that out.

Thank you for your help, hopefully I explained everything well enough.

[JBeaucaire]

Well, the comboboxes are the problem. When comboboxes make entries into cells, it doesn't trigger a "ws_change" event. So, I hid some cells in column O that are watching H13 and P13, then changed the autosave routine into a ws_calculate event.

Please Login or Register  to view this content.

Use of comboboxes has another problem. If the user is using the arrow keys to cycle down the list each movement actually writes the value into the linked cell, thus triggering the autosave routine. Rather than accidentally get 6 different versions of the same order, I made the macro ask if you're ready to save.

Comboboxes are nice with the autocomplete, but they present a lot of issues, too.

[whatsmyname]

Jerry,

Thank you. I have been pretty busy lately. So, now I have some time. I thank you so much for your time. Is there another way besides comboboxes to get the autocomplete feature? If there is I might start another topic.

I'll let you know if I have anymore problems.

Once again, thank you so much for your time. I really appreciate it.

[JBeaucaire]

One trick is to use Excel's autocomplete from the current column trick.

If you type "Cat" in A1, when you press the letter "c" in A2, it will autosuggest "cat" from the adjacent cell above.

As you continue to type downward, the autosuggest will continue to work for all the contiguous cells. As soon as you skip a cell and leave it blank, the next cell down will no longer get the autosuggest.

This allows you a trick, you could put your complete list of options in the cells above where you want this action, then HIDE those rows. This would cause the cell still visible directly below the hidden rows to still be able to autosuggest. However, the autosuggest would not occur until you had typed enough unique character to make a unique match.

You could also press ALT-DownArrow to see the current autosuggest options, sort of a hidden combobox, there's no "down arrow" in the cell to show you.

Your users could probably get used to anything as long as it was consistent, so this is an idea worth at least playing with.

[whatsmyname]

Yes sir it sure is. I played with it and I like the way it works. I'm going to stick with the way it is now and see how it goes. I just made a message box that pops up after the file is open that explains to them to fill out the form completely and then enter the date last. So far it's working, so we'll see how it goes.

Thank you for all your help Jerry.