Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prompt to Open or Save the downloaded file | Excel Discussion (Misc queries) | |||
Macro to save file as different filename | Excel Worksheet Functions | |||
Excel macro to prompt for filename | Excel Discussion (Misc queries) | |||
save prompt for user exit, but no save prompt for batch import? | Excel Discussion (Misc queries) | |||
How to CANCEL file SAVE PROMPT when MACRO is running? | Excel Discussion (Misc queries) |