Using SaveAs & changing the path a file is saved at

Hello -

I have a spreadsheet with a button that saves a worksheet directly to the users C drive. Below is the code that is assigned to the button:

Private Sub CommandButton2_Click()
' Saves file in Excel format
    ActiveWorkbook.SaveAs (ThisWorkbook.Sheets("Test").Range("I3").Value & ".xls")
    Application.DisplayAlerts = True
End Sub

Range("I3") is a cell that has a string of text with the file path and description of how to save it:

=CONCATENATE("C:\Test Folder\",Test!$E$3)

E3 is a cell that has another concatenate that results in a name like _2008312_1048. _2008312 is 03/12/2008; _1048 is the time the file was saved.

I have been asked to change this so it saves to a 'Test Folder' in a user's 'My Documents' folder. Being on a network, the path changes; it's listed as C:\Documents and Settings\[random user name]\My Documents\Test Folder.

Is there a way for me to acheive this?

Thank you!

If this is not possible, is there a way for me to have the user specify which folder it saves to, but keep the naming format (_20080312_1048) the same?

I should also mention that I'm using Excel 2003.

I've attempted to use the VBA on this page: http://www.excelforum.com/showthread...ghlight=saveas

However, I'd like this to happen on the click of a command button and only when that button is clicked, not when the user goes to File > Save, or hits the save button.

Any suggestions for adaptation?

Bumping post.

Any help would be appreciated!

After some more trial and error, I modified the code from http://www.excelforum.com/showthread...ghlight=saveas to work for my command button.

I used the below code:

Private Sub CommandButton2_Click()
'Saves file in Excel format

    Dim varFullName As Variant
    Dim strFileName As String

    Cancel = True

    strFileName = ThisWorkbook.Sheets("Test").Range("I3").Value

    varFullName = Application.GetSaveAsFilename(InitialFileName:=strFileName, _
                                                fileFilter:="Microsoft Excel Workbook (*.xls), *.xls")

    If varFullName <> Cancel And varFullName <> False Then
          On Error GoTo FileNotSaved
              Application.EnableEvents = False
                  ThisWorkbook.SaveAs Filename:=varFullName
              Application.EnableEvents = True
    End If

    Exit Sub

FileNotSaved:

    Application.EnableEvents = True

End Sub