By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,460 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Excel change save location when combobox value changes

P: 13
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
Jul 30 '10 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub YourCombo_Change()
  2.     Dim strPath as String
  3.     Dim strFileName as String
  4.     Const cFileName = "yourfile.xls"
  5.     If Not IsNull(Me.yourcomboname) then
  6.         Select Case Me.yourcomboname
  7.             Case "a"
  8.                  strPath = "x:\"
  9.             Case "b"
  10.                  strPath = "z:\subfolder\"
  11.             Case Else
  12.                  strPath = "C:\"
  13.         End Select
  14.         strFileName = strPath & cFilename
  15.         ActiveWorkbook.SaveAs FileName := strFileName
  16.     End If
  17. 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

Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub YourCombo_Change()
  2.     Dim strPath as String
  3.     Dim strFileName as String
  4.     Const cFileName = "yourfile.xls"
  5.     If Not IsNull(Me.yourcomboname) then
  6.         Select Case Me.yourcomboname
  7.             Case "a"
  8.                  strPath = "x:\"
  9.             Case "b"
  10.                  strPath = "z:\subfolder\"
  11.             Case Else
  12.                  strPath = "C:\"
  13.         End Select
  14.         strFileName = strPath & cFilename
  15.         ActiveWorkbook.SaveAs FileName := strFileName
  16.     End If
  17. 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
Jul 30 '10 #2

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

Thanks again!
Jul 30 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.