Save as dialogue pop-up Function instead of Save in a specific location/path

[nardotini]

Hi,

Wanted to make a worksheet where I can save bid/invoices in a different sheet (to keep track of it) but I also wanted to save it in a specific location each time I click "Save All".
Right now I have it so that it saves in a specific location, but I want to make it so that I can put the saved files (for both PDF and Excel) in different locations.

Sub SaveAll()


Dim invno As Long
Dim custname As String
Dim project As String
Dim amt As Currency
Dim dt_issue As Date
Dim term As Byte
Dim path As String
Dim fname As String
Dim nextrec As Range

invno = Range("H4")
custname = Range("B9")
project = Range("G9")
amt = Range("H36")
dt_issue = Range("H5")
term = Range("H6")
path = "C:\Users\nardo\OneDrive\Desktop\Bids\"
fname = invno & " - " & custname & " - " & project

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, IgnorePrintAreas:=False, Filename:=path & fname


Sheet3.Copy

Dim shp As Shape

For Each shp In ActiveSheet.Shapes
    shp.Delete
Next shp

With ActiveWorkbook
    .Sheets(1).Name = "Bid"
    .SaveAs Filename:=path & fname, FileFormat:=51
    .Close
End With


Set nextrec = Sheet5.Range("A1048576").End(xlUp).Offset(1, 0)

nextrec = invno
nextrec.Offset(0, 1) = custname
nextrec.Offset(0, 2) = project
nextrec.Offset(0, 3) = amt
nextrec.Offset(0, 4) = dt_issue
nextrec.Offset(0, 5) = dt_issue + term
nextrec.Offset(0, 6) = "Not Emailed"


Sheet5.Hyperlinks.Add anchor:=nextrec.Offset(0, 8), Address:=path & fname & ".xlsx"

Sheet5.Hyperlinks.Add anchor:=nextrec.Offset(0, 7), Address:=path & fname & ".pdf"




End Sub

 

[Micron]

I would probably add code to that for using msoFileDialogFolderPicker as long as I didn't need to see files in the dialog. This will return the path to the chosen folder. That's my one beef about the dialog - you can't see files when choosing a folder. If you use msoFileDialogFilePicker you can see files but then have to strip off the file name if you don't want it. Neither of these dialogs actually open anything - they just return paths as strings, which you could assign to your path variable.

 

[nardotini]

I would probably add code to that for using msoFileDialogFolderPicker as long as I didn't need to see files in the dialog. This will return the path to the chosen folder. That's my one beef about the dialog - you can't see files when choosing a folder. If you use msoFileDialogFilePicker you can see files but then have to strip off the file name if you don't want it. Neither of these dialogs actually open anything - they just return paths as strings, which you could assign to your path variable.

So is there a way to put the file name from the fname in msoFileDialogFilePicker?
So from my understanding if I did use msoFileDialogFilePicker, I wouldnt be able to put the path in another sheet?

 

[Micron]

fname is your posted variable - it's not something in the dialog. As I mentioned, it returns the file or folder path as a string. You can store that or open the file or do pretty much whatever you want with that.

Application.FileDialog property (Excel)

Office VBA reference topic

learn.microsoft.com