Forcing a macro to pause for filename

[matpoh]

Just wondering how I would get a macro to pause to allow me to change
filename and then when I hit enter, it continues.

I use it to export excel worksheets to our webserver and it works now
but overwrites the file name everytime. I want to change the filenam
before saving.

Any help appreciated

 

[Dave O]

This line of code opens a Save As dialog box, allows you to click to
your filepath, and specify a file name.

Application.Dialogs(xlDialogSaveAs).Show

 

[matpoh]

Is there a way to have the macro keep the same directory, but allow for
file name change?

 

[Dave Peterson]

You could get a filename with something like this (ignoring the folder):

Option Explicit
Sub testme()
Dim myFileName As Variant
Dim iCtr As Long
Dim SlashChar As String
Dim myFolder As String
Dim resp As Long

myFolder = "c:\my documents\excel"
If Right(myFolder, 1) <> "\" Then
myFolder = myFolder & "\"
End If

myFileName = Application.GetSaveAsFilename _
(filefilter:="Excel Files, *.xls")

If myFileName = False Then
'what happens if you cancel?
Else
For iCtr = Len(myFileName) To 1 Step -1
SlashChar = Mid(myFileName, iCtr, 1)
If SlashChar = "\" Then
'found it
myFileName = Mid(myFileName, iCtr + 1)
Exit For
End If
Next iCtr

myFileName = myFolder & myFileName

resp = vbYes
If Dir(myFileName) <> "" Then
resp = MsgBox(prompt:="Wanna overwrite it?", Buttons:=vbYesNo)
End If

If resp = vbYes Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=myFileName, _
FileFormat:=xlWorkbookNormal
Application.DisplayAlerts = True
Else
'what should happen if you don't want to overwrite it?
End If
End If

End Sub

But if you know you want to write to a unique filename, maybe you could just
append the date and time to the filename:

if lcase(right(myfilename,4)) = ".xls" then
myfilename = left(myfilename,len(myfilename)-4)
end if

myfilename = myfilename & "_" & format(now,"yyyymmdd_hhmmss") & ".xls"

Then unless you do something very, very quickly <vbg>, you'll have a unique
name.