Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to pause for user input in dialog box | Excel Discussion (Misc queries) | |||
How do I pause a macro to select specific cells | Excel Worksheet Functions | |||
Macro to move to specified cells, pause for input at each? | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) |