MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Automatically Saving a worksheet in it respective folder according to the selection in a combo box


Posted by TA on July 05, 2001 3:01 PM

I am designing a purchase order for my company and need a little help. I created a table of all of our vendors and used a combo list box and vlookup to display them and automatically insert the address, number, etc in the appropriate boxes so the person ordering would not have to (a neat trick a learned from your web site). Now I am trying to think of a way to have the completed PO saved in it's correct folder on the network automatically (with name of the folder is the same as the vendor).

A while back Mr. Excel, and the rest of you guys, showed me how to automatically save a worksheet as the the name in a specified cell. Now I want to save the worksheet in a specified folder according to the vendor that the user picks from the combo box. I am thinking that there is a way to use the cell link fom the drop down box for this purpose but I can't imagine how to do this.

Here is the code for changing the directory and saving the worksheet:
ChDir "G:\My Data\Purchase Orders\CDW\2001"
ActiveWorkbook.SaveAs Filename:= _

Do you know a way that I can make the folder name (CDW in this instance) variable according to the vendor select from the dropdown box??


Posted by Dax on July 06, 2001 4:55 AM

Depending on what your combobox is called and where it's located you could use this code. I've assumed the combobox is called combobox1 and is on sheet1.

Sub SaveFile()
Dim sFullPathName As String
sFullPathName = Sheet1.ComboBox1.Text
If sFullPathName = "" Then Exit Sub
sFullPathName = "C:\My Data\Purchase Orders\" & sFullPathName & "\2001.xls"
ActiveWorkbook.SaveAs sFullPathName
End Sub

HTH,
Dax.

Posted by t.a. on July 06, 2001 10:03 AM

This looks great and it seem like it would work but when I use this code it doesn't recognize the name of the combo drop box. I am using the combo box from the Forms Toolboar instead of from Active X controls. Does this make a difference?

Thnx
t.a.

Posted by Dax on July 08, 2001 2:57 PM


Yes, it does make a difference. You probably could do it with the forms combobox but I'm not sure how (probably something to do with the OLEobjects collection but don't quote me). Do you need to use the forms combobox?

Regards,
Dax.

Posted by Dax on July 08, 2001 2:57 PM


Yes, it does make a difference. You probably could do it with the forms combobox but I'm not sure how (probably something to do with the OLEobjects collection but don't quote me). Do you need to use the forms combobox?

Regards,
Dax.