Setting the default save path with prompt

[CaptainGravyBum]

Hello,
I'm trying to get the script below to show the default file path as the local downloads folder when the prompt is displayed to choose a save location.
It's a bit messy and the script will run perfectly accept when the user is prompted to choose a place to save the file, it seems to come up as the system folder all the time as default even though I have specified the local environment where it should be saved.
Can anyone see where I've gone wrong?


Dim savePath As Variant
Dim newWb As Workbook
Dim newSheet As Worksheet
Dim sheetName As String
Dim currentDateAndTime As String
Dim defaultPath As String

Set ws = ThisWorkbook.Sheets("Import")
currentDateAndTime = Format(Now(), "ddmmyyyy_hhmmss")
sheetName = ws.Name
Set newWb = Workbooks.Add
Set newSheet = newWb.Sheets(1)
ws.Cells.Copy newSheet.Cells
defaultPath = Environ("USERPROFILE") & "\Downloads\" & sheetName & "_" & currentDateAndTime & ".csv"
savePath = Application.GetSaveAsFilename(FileFilter:="CSV (Comma delimited) (*.csv), *.csv", Title:="Save As", InitialFileName:=sheetName & "_" & currentDateAndTime)
If savePath <> "False" Then
newWb.SaveAs fileName:=savePath, FileFormat:=xlCSV, Local:=True

newWb.Close False

MsgBox "Import sheet saved successfully as CSV. This workbook will now close"
Else
newWb.Close False

MsgBox "Operation cancelled."
End If

Sheets("Import").Visible = False

Application.ScreenUpdating = True

For Each wb In Workbooks
If wb.Name = "Invoice Master.xlsm" Then
wb.Close False
Exit Sub
End If
Next wb

MsgBox "Workbook 'Invoice Master.xlsm' is not open.", vbExclamation

 

[Jaafar Tribak]

I don't see you specifying the folder it should default to.

See if this works for you: ( Changes in bold )

defaultPath = Environ("USERPROFILE") & "\Downloads\" & sheetName & "_" & currentDateAndTime & ".csv"
Dim initPath As String
initPath = VBA.CurDir
VBA.ChDir defaultPath
savePath = Application.GetSaveAsFilename(FileFilter:="CSV (Comma delimited) (*.csv), *.csv", Title:="Save As", InitialFileName:=sheetName & "_" & currentDateAndTime)
VBA.ChDir initPath

Note you may want to add some error handling to your code

 

[CaptainGravyBum]

Not sure what I'm doing wrong, but this doesn't work for me either.
I think I'll just have to leave it to prompt for a location without the default.