insert data into macro code from text input field?

  • Thread starter Thread starter john_t_h
  • Start date Start date
J

john_t_h

I have a macro that opens and saves files at the start and end of th
macro.

I want to have a form on the first sheet that has text input fiel
boxes to insert file paths and file names into the maco that will b
run.

So instead of

Code
-------------------

ChDir "G:\COMMON\"
ActiveWorkbook.SaveAs FileName:= _
"G:\COMMON\myfile.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=True, _
CreateBackup:=False

-------------------
I hav
Code
-------------------

$dir = path from input field
$filename = filename from input field

ChDir "$dir"
ActiveWorkbook.SaveAs FileName:= _
"$dir $filename", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=True, _
CreateBackup:=Fals
 
Why not use
Dim fName as String
fName = Application.GetSaveAsFilename(Filefilter:= _
"Excel Files (*.xls), *.xls")
if fName <> "False" then
ActiveWorkbook.SaveAs FileName:= _
fName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=True, _
CreateBackup:=False
End if

No reason to do a chdir since you are specifying the path (returned by
GetSaveAsFilename along with the name).
 
Back
Top