[Resolved] Using Cell Text as filename in VB

[apgriffiths]

Hi all

I would like to write a bit of code so that when a user selects the Save As option the filename is automatically entered into the box as the text from a certain cell.
Can anybody help with this??

 

[erik.van.geit]

Hi,

try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim file_name As Variant

    If SaveAsUI Then
    Cancel = True
    
    ChDrive Left(ActiveWorkbook.Path, 3)
    ChDir ActiveWorkbook.Path
    
    file_name = Application.GetSaveAsFilename(Sheets(1).Range("A1"))
    If file_name = False Then Exit Sub
    
    ThisWorkbook.SaveAs file_name & "xls"

    End If

End Sub

 

[apgriffiths]

Hi

The first part worked ok. The file name was correctly put into the box but after i clicked on save another Save as dialog opened and after that there was an error.

 

[erik.van.geit]

another saveasbox ?

how did you use this code ?

I did this
new file
sheet1 a1 put any string (valid filename)
put code in thisworkbookmodule
menu File/SaveAs

got box
clicked OK
file was saved
(end code-execution)

 

[apgriffiths]

Hi Erik

I managed to sort it out. I changed the sheet name and i had originally referenced a merged cell and it didnt like it for some reason so ive now just referenced a single cell and it all works great.

Cheers