Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Prompt to Open or Save the downloaded file Sreesha B.R Excel Discussion (Misc queries) 2 February 11th 08 04:41 PM
Macro to save file as different filename mac Excel Worksheet Functions 6 November 21st 07 11:43 PM
Excel macro to prompt for filename [email protected] Excel Discussion (Misc queries) 8 January 4th 07 02:31 AM
save prompt for user exit, but no save prompt for batch import? lpj Excel Discussion (Misc queries) 1 February 25th 06 03:08 AM
How to CANCEL file SAVE PROMPT when MACRO is running? Stuart Macro Muppet Excel Discussion (Misc queries) 3 August 11th 05 12:26 PM


All times are GMT +1. The time now is 06:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"