GetSaveAs command button - with specific address

[csprod]

Hello all you smart excel boffins !

I have just spent 3 hours searching for some VBA code that I can attach to a command button in excel to GETSAVEAS. Sounds simple enough, but I found none that can reference a particular cell in the spreadsheet for the file name.

My user is a novice and she often looses files as she dosent save them to the right directory or give them a decent name (lots of files called 'sheet1, sheet2, sheet3 on her computer!).

My spreadsheet has generated a unique file name (and directory) that is located in cell A1. (CELL A1 = "c:\users\craig\desktop\DaveJul2211.xls"). In subsequent sheets, the path stays the same, but the generated file name changes.

How do I run a GetSaveAs command and tell it to default to the file name and path that is in A1 ?. Also, force save as .xls file ?

Cheers,
Craig.
MSExcel 2000

 

[wigi]

For instance:

Sub fff()
    ActiveWorkbook.SaveAs [A1].Text, xlWorkbookNormal
End Sub

 

[csprod]

No luck there unfortinately wigi...

This is what I have so far...

InitialFileName:="C:\users\craig\documents\companies\drivername_" & Range("A1").Value & ".xls", _
fileFilter:="Excel Workbook (*.xlsx),*.xlsx")

Cell A1 = BOB

but the file only saves as "C:\users\craig\documents\companies\drivername_.xls" - ie BOB (the unique name i want) is missing.

Any thoughts ?

 

[oggy3000]

Hey csprod,
try using the following code:

Private Sub cmdSave_Click()
ActiveWorkbook.SaveAs Filename _
        :="C:\users\craig\documents\companies\drivername_" & Worksheets("Sheet1").Range("A1").Value, _
        FileFormat:=xlNormal, CreateBackup:=False
End Sub

(alter it where applicable)

Hope it works the way you want.

Cheers
Jan