Excel - Save Button

user_14906872 0
Viewed 193

I have made invoice system in excel. Where there is a customer name and id which is used as a invoice number. And other details are there as well. I have added a save Button in excel to save my worksheet with the following vb:

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\PDF\Export.pdf", _
            OpenAfterPublish:=False
    Application.ScreenUpdating = True
End Sub

Now, it just saves with the 'Export' name. So every time I save it will have the same name. But I want the save button allow me name the sheet before I save the sheet. Or, is there any way the name of the pdf I save can be according to two cell values in order? Also I want the saved pdf to be opened when this Save button is clicked.

Is it not possible to retrieve the filename from the three different cells in order. J5&J6 for Invoice number, K5&K6 customer name and L8 for status? Is it not possible if the same button will also save certain cell values in another sheet?

button save vba excel
1 Answers

Using my folder structure:

Private Sub CommandButton1_Click()
    Dim fname As String
    
    fname = Application.InputBox(Prompt:="Enter File Name", Type:=2)
    
    Application.ScreenUpdating = False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\TestFolder\" & fname & ".pdf", _
            OpenAfterPublish:=True
    Application.ScreenUpdating = True
End Sub

Naturally you would change TestFolder to PDF.

EDIT#1:

With cell content like:

enter image description here

This updated code:

Private Sub CommandButton1_Click()
    Dim fname As String, s As String
    
    s = " "
    fname = [J5] & [J6] & s & [K5] & s & [K6] & s & [L8]
    
    Application.ScreenUpdating = False
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:="C:\TestFolder\" & fname & ".pdf", _
            OpenAfterPublish:=True
    Application.ScreenUpdating = True
End Sub

will generate a file with the filename PN123-697 James Ravenswood Shipped and a filetype of .pdf.

user_24746560
Related