Excel change save location when combobox value changes

[Dresse]

Hello

I was wondering if it is possible to define a save location when a value in a combobox is selected.

ex.

Combobox:
a
b
c

if 'a' is selected i want the save location to be Z:\a\filename.xls

I've searched a bit already but haven't found anything specific yet, any help is very much appreciated.

Regards

Dresse

[Stewart Ross]

Hi. It is quite possible to do what you require, but it needs a little thought as to how to do it.

In VBA you will need to use the SaveAs method of the Workbook object to save the file to a new location. SaveAs requires a filename inclusive of its path, so if you want to make the path variable on selection of a combo value then you will need to define the path and the filename separately.

You do not say whether or not your code will be running in Excel itself, or whether you are using Excel as an automation server running from Access, say.

If I assume that your combo box is in an Excel workbook then you would need something like this in the combo's Change or AfterUpdate event code:

Private Sub YourCombo_Change()
    Dim strPath as String
    Dim strFileName as String
    Const cFileName = "yourfile.xls"
    If Not IsNull(Me.yourcomboname) then
        Select Case Me.yourcomboname
            Case "a"
                 strPath = "x:\"
            Case "b"
                 strPath = "z:\subfolder\"
            Case Else
                 strPath = "C:\"
        End Select
        strFileName = strPath & cFilename
        ActiveWorkbook.SaveAs FileName := strFileName
    End If
End Sub

You will need to adapt the basics above as necessary, but the idea is to have a path that is selectable using the Change or AfterUpdate event of the combo, combining it with a name for the file to produce a full path and filename, then saving the current Excel workbook using the SaveAs method and the full path for the filename.

Be careful with paths - as these are combined with the filename to provide the full location you must ensure that the path is correctly terminated with a backslash (i.e. C:\somefolder\ and not C:\somefolder) before you concatenate it with the filename.

-Stewart

[Dresse]

Edit - nvm I found what I was looking for using part of your solution

Thanks again!