Excel:needed, module that'll open the SaveAs dialogue w/suggested name 2

[coolcarls]

So I have it almost all working.
All I need now is either:
1. When the SaveAs dialogue box opens is have a suggested name in the "File Name" text box. Right now (as always) it uses the name of the workbook (book1 or whatever it maybe).

or

2. ActiveWorkbook.SaveCopyAs "C:\Projects\Lists"
This works, but, in my "Lists" file I have maybe 15 or 20 different sub folders. For example..."C:\Projects\Lists\SanFrancisco"
and C:\Projects\Lists\LosAngeles"
and C:\Projects\Lists\Chicago"

So, one of the cells in my sheet (sheet2) cell "c23" will have the text of one of the cities. Could I have this module ....."C:\Projects\Lists"
have something at the end that would include the value in cells c23 on the end to actually automatically save the file in the "C:\Projects\Lists\Chicago" directory if the value of sheet2.cells ("c23") was Chicago


Preferably, I would like method #2, but will be able to sleep tonight if either one works.

I wrote and re-wrote that about 1000 times, I still don't know if it makes any sense!

Help me if you can....

As usual, thanks a lot.
Carl

 

[rdeleon]

Unless I am misunderstanding you, for option 2, why not concatenate "C:\Projects\Lists" with the value in c23. I would give c23 a name, such as "CityName" and then use:

Dim strTargetDirectory As String

strTargetDirectory = "C:\Projects\Lists\" & Range("CityName") & "\" & ActiveWorkbook.Name

ActiveWorkbook.SaveCopyAs strTargetDirectory

Hope it helps.

Rene'

 

[PeterMoran]

Hi,

Here is some similar code which I use to open the File Save As window with a suggested filename, which you should be able to change to do what you want.

You may need to concatenate your sub-folders onto the CHDir statement. Note that with this method the selection of folder is separate from the filename.

        ' Prepares to Save File with Location and Filename+yymmdd as
        ' required and provides facility for user intervention.
        Dim fileSaveName As Variant
        ChDir Application.DefaultFilePath
        fileSaveName = Application.GetSaveAsFilename _
            (InitialFileName:="ITR Timesheet" _
            & Format(TSDate.Value, "yymmdd") & ".xls", _
            FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
        If VarType(fileSaveName) <> vbBoolean Then
            ActiveWorkbook.SaveAs Filename:=fileSaveName, _
            FileFormat:=xlNormal, AddToMru:=True
        End If

Good Luck!

Peter Moran

 

[coolcarls]

you know, I am constantly amazed here. Thanks, thanks and thanks again.
Carl