Can a MACRO prompt for the filename to open and/or save?
 

Take a look at the application.getopenfilename and application.getsaveasfilename
in VBA's help:

dim myFileName as Variant
myfilename = application.getopenfilename
if myfilename = false then
exit sub
end if

workbooks.opentext filename:=myfilename, ....

might get you started.


Frank Altamura wrote:

I need to write a macro to import several delimited text
files into Excel. Using the "record" technique, I created
one that opens one specific filename and formats it. I
have many similar files to import, each with a unique
name. Is there a way to modify the macro so that it
presents a "file open" dialog where the user can navigate
to the correct file? How about a "save as" dialog as as
well?

This is the "recorded" macro code:

Sub BenprogPartic()
'
' BenprogPartic Macro
' Macro recorded by Frank M. Altamura
'

'
Workbooks.OpenText Filename:="N:\PSV8
\Data\CM.BENPROG.PARTIC", Origin:= _
xlWindows, StartRow:=2, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=True,
OtherChar:="~", FieldInfo _
:=Array(Array(1, 2), Array(2, 2), Array(3, 2),
Array(4, 4), Array(5, 2), Array(6, 2))
Columns("A:F").Select
Columns("A:F").EntireColumn.AutoFit
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "EMPLID"
Range("B1").Select
ActiveCell.FormulaR1C1 = "RCD NO"
Range("C1").Select
ActiveCell.FormulaR1C1 = "COBRA EVENT ID"
Range("D1").Select
ActiveCell.FormulaR1C1 = "EFFDT"
Range("E1").Select
ActiveCell.FormulaR1C1 = "BEN PROGRAM"
Range("F1").Select
ActiveCell.FormulaR1C1 = "SSN"
Columns("A:F").Select
Range("F1").Activate
Columns("A:F").EntireColumn.AutoFit
Range("A1").Select
ChDir "C:\FMA\xls"
ActiveWorkbook.SaveAs
Filename:="C:\FMA\xls\CM.BENPROG.PARTIC.xls", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

--

Dave Peterson

Can a MACRO prompt for the filename to open and/or save?
 

Thanks, that's a good start. I should be able to muddle
through with the HELP files now!

-----Original Message-----
Take a look at the application.getopenfilename and
application.getsaveasfilename
in VBA's help:

dim myFileName as Variant
myfilename = application.getopenfilename
if myfilename = false then
exit sub
end if

workbooks.opentext filename:=myfilename, ....

might get you started.


Frank Altamura wrote:

I need to write a macro to import several delimited text
files into Excel. Using the "record" technique, I
created
one that opens one specific filename and formats it. I
have many similar files to import, each with a unique
name. Is there a way to modify the macro so that it
presents a "file open" dialog where the user can
navigate
to the correct file? How about a "save as" dialog as as
well?

This is the "recorded" macro code:

Sub BenprogPartic()
'
' BenprogPartic Macro
' Macro recorded by Frank M. Altamura
'

'
Workbooks.OpenText Filename:="N:\PSV8
\Data\CM.BENPROG.PARTIC", Origin:= _
xlWindows, StartRow:=2, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=True,
OtherChar:="~", FieldInfo _
:=Array(Array(1, 2), Array(2, 2), Array(3, 2),
Array(4, 4), Array(5, 2), Array(6, 2))
Columns("A:F").Select
Columns("A:F").EntireColumn.AutoFit
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "EMPLID"
Range("B1").Select
ActiveCell.FormulaR1C1 = "RCD NO"
Range("C1").Select
ActiveCell.FormulaR1C1 = "COBRA EVENT ID"
Range("D1").Select
ActiveCell.FormulaR1C1 = "EFFDT"
Range("E1").Select
ActiveCell.FormulaR1C1 = "BEN PROGRAM"
Range("F1").Select
ActiveCell.FormulaR1C1 = "SSN"
Columns("A:F").Select
Range("F1").Activate
Columns("A:F").EntireColumn.AutoFit
Range("A1").Select
ChDir "C:\FMA\xls"
ActiveWorkbook.SaveAs
Filename:="C:\FMA\xls\CM.BENPROG.PARTIC.xls", _
FileFormat:=xlNormal, Password:="",
WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

--

Dave Peterson

.