User to choose path and file nane to save a csv file

[bssimmonds]

I would like for the user to be able to choose the path and filename to save a file.
I can embed the path and filename in the VBA code but I would like normal Excel save screen to be displayed.
can anyone help please?

Thanks, Bernie

This is my code with embedded path

Sub ExportToFile()
' This routine copies the "Exported" data
' creates a new Workbook
' pastes the exportdata
' saves the workbook to the current path with file name in variable "newname"

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim oldname$, oldpath$, oldformat, newname$

'get current path and filename
With ActiveWorkbook
oldpath = .Path
oldname = .Name
End With

'set file name
'newname = "Export " + oldname
'MsgBox (oldpath + "\" + newname)

'Remove the last 5 characters of the filename to remove the extention
oldname = Left(oldname, Len(oldname) - 5)

'Get Filename to save file
newname = InputBox("Enter desired filename or click OK to accept default", _
"Enter Filename", "Export " + oldname)

'make sure the filename ends with .csv
If Not Right(newname, 4) = ".csv" Then newname = newname + ".csv"

'Copy export data
Sheets("EXPORT").Activate
Range("A1:A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

'Create new workbook and paste exportdata
Workbooks.Add Template:="Workbook"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

'Save Exportfile and csv then close file
ActiveWorkbook.SaveAs Filename:=oldpath + "\" + newname, FileFormat:=xlCSV, CreateBackup:=False
ActiveWindow.Close False

'goto Processing sheet
Sheets("PROCESSING").Activate
Range("A1").Select
Application.DisplayAlerts = True
End Sub

 

[Worf]

Welcome to the Board

See if this thread helps: http://www.mrexcel.com/forum/excel-questions/535724-how-open-save-dialog-box-visual-basic-applications.html

 

[bssimmonds]

Welcome to the Board

See if this thread helps: http://www.mrexcel.com/forum/excel-...ave-dialog-box-visual-basic-applications.html

Thanks Worf
This works. Just what I needed.

 

[Worf]

You are welcome and thanks for the feedback.