Forcing a macro to pause for filename
 

Just wondering how I would get a macro to pause to allow me to change a
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 filename
before saving.

Any help appreciated.


--
matpoh
------------------------------------------------------------------------
matpoh's Profile: http://www.excelforum.com/member.php...o&userid=27024
View this thread: http://www.excelforum.com/showthread...hreadid=466247

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

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


--
matpoh
------------------------------------------------------------------------
matpoh's Profile: http://www.excelforum.com/member.php...o&userid=27024
View this thread: http://www.excelforum.com/showthread...hreadid=466247

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.




matpoh wrote:

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

--
matpoh
------------------------------------------------------------------------
matpoh's Profile: http://www.excelforum.com/member.php...o&userid=27024
View this thread: http://www.excelforum.com/showthread...hreadid=466247

--

Dave Peterson