VBA that saves filename with name that is same as the directory it is placed in

[Wedge120]

The spreadsheets I use at work must be saved in a directory that is auto-generated by a separate application. Each directory has a unique name. I would like to create a macro that opens the "save as" dialog, allows me to point to the directory I wish to save in, and then name the workbook automatically with the same name as the unique directory it is placed in. Is this possible?

[skywriter]

It's one line of code to open the saveas the rest of your request I don't know exactly what you mean. I would think if you know the directory name you don't have to open save as you could just create a full path file name and save it that way. Maybe?

Please Login or Register  to view this content.

[skywriter]

This would allow you to open up the C drive and look at the folders in there and choose one and then the message box displays the folder you choose. This would be the basis for doing what you want. If you want to dig deeper inside the c we would change the path from C:\ to drill down farther in the c drive so that the list you look at is minimal. I could then write you some more code that would save your file into the folder you choose with the name of the folder as the file name.
You can run this code to see how it works.

Please Login or Register  to view this content.

[Wedge120]

The location I will be saving to every single time is "C:\Digital"

A new folder will generate inside the "Digital" folder and have the following format (just an example): 05A414295RUK
So, in this example, the path would be "C:\Digital\05A414295RUK", and I would need the file name to be "05A414295RUK". So the full path is "C:\Digital\05A414295RUK\05A414295RUK.xlsm"

[skywriter]

So in my c drive I made a folder named digital and within digital I made another folder named 05A414295RUK. When I run the code I get a box to choose a folder. The only folders I see are those within digital. I choose the folder 05A414295RUK press the button and the Excel file is saved in the folder named 05A414295RUK and the name of the file is 05A414295RUK.xlsm. If I misunderstood something you said, let me know, it's probably an easy fix.

I made a couple of other folders in digital and ran the code against those folders and it seemed to work fine.

I haven't done any coding like this so I enjoyed it and learned something.

Thanks for the opportunity.

Please Login or Register  to view this content.

[Wedge120]

My tests are resulting in workbooks that are blank (no sheets). The name is correct, as intended, but missing the content. Would this have anything to do with saving it as xlsm? I think I may have told you wrong. It should be saved as an excel 97 - 2003 worksheet.

[skywriter]

Okay, try this and let me know.

Please Login or Register  to view this content.

[Wedge120]

Wow, same thing. Can you test this on any given workbook you may have, or make a test one? Run the code on it, then close excel and go open the workbook that the code saves to. See if any sheets are present (for me there are none). We need to know if this is something peculiar to my system or if the code is missing something.

[skywriter]

I have been running it in a workbook with sheets. I wrote the code in a workbook that had three sheets with data in each sheet. I run the code close the workbook, go to the folder, there it is with the right name and when I open it the data is intact.

So now just to try it again, I exported my module to the desktop. I randomly picked a spreadsheet from many I have. I imported the module, ran the code and closed the file. I opened the folder it should be in and all was good. Right folder, right name, data intact.

The only thing I don't know is what you are doing, so I don't know how to help you. It might be obvious if I was sitting with you or I might be a stumped as you are as to what is going on.

Are you on a Mac?

[Wedge120]

sorry for the late response. I'm just now getting some free time...

I'm not on a Mac.
When i run the code it saves a workbook, but not the workbook i am working in. After running the code, I attempt to close my workbook, and i am prompted with "do you want to save?", as any file normally does Do you receive this prompt, even after running the code? Also, after running the code, the name of the workbook i am in doesn't change at all.

So basically, i can open excel, create a new document (i have just been typing "testing" in a few cells), run the code, then close the workbook (with a prompt to save or not save, and name of the workbook remaining "Book1"). A workbook with the name of the folder is there, but no sheet(s). It is as if it is creating a new workbook in addition to the one i am working in, but no sheets.

Does version matter? i'm running Excel 2010. Does workbook type matter (i.e., xlsx, xlsm, xls)?

[Wedge120]

Just realized it is creating the newly named workbook as a hidden workbook. In the VBE, i can see it in the project explorer, but i would never know about it otherwise. If i unhide to see the sheets(s), there is a single sheet with nothing in it.

[Wedge120]

Just realized it is creating the newly named workbook as a hidden workbook. In the VBE, i can see it in the project explorer, but i would never know about it otherwise. If i unhide to see the sheets(s), there is a single sheet with nothing in it.

So for me, the workbook is created as a hidden workbook, and is a completely new workbook that is devoid of data, rather than containing the data i wish to save.

The Code you have written is intended to be saved and ran in the personal.xls workbook that normally contains macro code, correct?

[skywriter]

You can skip this post and go to the next one.
I believe I have good news.

I have 2010. I originally just wrote the code in a workbook I happened to have open. Then in my last post I decided to export the module and import it into another workbook. I have had no problems. I didn't write it with the intention of running in the personal workbook, I don't even know the purpose of the personal workbook, nor where to find it. I searched my computer, if I'm supposed to have a personal workbook I can't find it. If I'm supposed to set my own up I don't know how or why. It's just code I wrote it and it works for me. If I run it the original file closes and the only file I have running is the file that the code saved. If I don't change the data and I want to close it, then it just closes no issues. The file I was running at the time I saved it is still intact and runs fine. I can run it again and save it as a different file.

I also opened one of the files that was created by the code and ran the code again in that file giving it a new name through the code and it worked fine again.

I also opened a new blank workbook, imported the code, added some data and ran it. It worked fine in that workbook also. Closed it opened it again the data was there.

In short it doesn't seem to matter what workbook I run the code in. It save the file I am running, it closes the file that was running and all data in both files is left intact.

[skywriter]

Okay I think you helped me to learn something else.
Here's a good link for anyone reading this that wants to know about what the personal workbook is and how to set one up if you don't have one. I was right I didn't have a personal workbook now I do, thanks to Ron de Bruin, and I think you were right in your thoughts that maybe the personal workbook was the issue.

So first let me say I made a change to the code:

Please Login or Register  to view this content.

Needs to be changed to:

Please Login or Register  to view this content.

So I created the personal workbook and right away I figured the main problem is ThisWorkbook refers always to the workbook the code is in. Therefore the change to ActiveWorkbook.

I started by not changing the code to see what would happen. When I run it I notice the file name on my spreadsheet has not changed. In the VBE my personal file name has changed to what the code should have changed the file I was working on to. I go into the folder where the file should have been created and there is a file by that name in there, but when I click on it Excel crashes immediately. When I choose to open any other file except this file that crashes Excel, everything seems fine and when I go into the VBE my personal file is back in the project explorer and all seems fine.

So I opened one of my existing workbooks I had and found the macro in the personal macro list and ran it. The file I used is a .xlsm and you wanted yours saved as .xls so when the macro runs I get all this stuff about significant loss of functionality, but I just click continue. It seems okay. In the VBE the original file does not show only the new one I created and the personal file. I close the file there are no issues it doesn't ask if I want to save it and it doesn't crash. I go into the folder it was saved in and double click to open it and everything seems fine. Maybe something was lost because it was a .xlsm file, but I don't know what it is that is different. If I open a blank workbook add some data and run the macro I have no issues at all. It wasn't previously saved in a non xls format so I don't get a message about loss of functionality and when I close and reopen the file the data I entered is in tact.

So I hope you are going to make the change and tell me all is good.

[Wedge120]

That worked!!! That is one less error i have to worry about making! If you only knew how repetitive this small task is for me. Thank you very much

What does the following line of code mean?

Please Login or Register  to view this content.

Also what data type is FileDialog? I don't think I've seen this before:

Please Login or Register  to view this content.

[skywriter]

What does the following line of code mean?

If you pressed the button to proceed then .Show returns -1, if you canceled I believe it equals 0.
So if you canceled it jumps to the NextCode: line and exits.

Also what data type is FileDialog? I don't think I've seen this before:

Did you read the code?

Please Login or Register  to view this content.

If you are happy with my help feel free to press the add reputation button at the bottom left of this post.

If all is good.
Please click the Thread Tools drop down box above your first post and choose solved.