The Basic Script below was captured using Excels "Record Macro"
feature. As recorded, it opens the file: "TEXT;P:\WB Test\Aspen Sample
Mail Files\020604\mailing\new\SBELECPRD_9111140388.SPF; 1" and performs
a series of actions on the file.

Instead of opening a specific file I would like this macro to instead
launch an "Open File" Dialog box to allow me to specify which file
will be processed.

Can anyone tell me how to add the "Open File" dialog to this macro?

Best Regards,

Marcel




Sub Process_Aspen_Mail_File()
'
' Process_Aspen_Mail_File Macro
' Macro recorded 2/5/2004 by preprint
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;P:\WB Test\Aspen Sample Mail
Files\020604\mailing\new\SBELECPRD_9111140388.SPF; 1" _
, Destination:=Range("A1"))
.Name = "SBELECPRD_9111140388.SPF;1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2)
.Refresh BackgroundQuery:=False
End With

Marcel,

Try something like

Dim FName As Variant
FName = Application.GetOpenFilename()
If FName = False Then
' no file choses
Else
' open FName
End If



"maleemi" wrote in message
om...
The Basic Script below was captured using Excels "Record Macro"
feature. As recorded, it opens the file: "TEXT;P:\WB Test\Aspen
Sample
Mail Files\020604\mailing\new\SBELECPRD_9111140388.SPF; 1" and
performs
a series of actions on the file.

Instead of opening a specific file I would like this macro to
instead
launch an "Open File" Dialog box to allow me to specify which
file
will be processed.

Can anyone tell me how to add the "Open File" dialog to this
macro?

Best Regards,

Marcel




Sub Process_Aspen_Mail_File()
'
' Process_Aspen_Mail_File Macro
' Macro recorded 2/5/2004 by preprint
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;P:\WB Test\Aspen Sample Mail
Files\020604\mailing\new\SBELECPRD_9111140388.SPF; 1" _
, Destination:=Range("A1"))
.Name = "SBELECPRD_9111140388.SPF;1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2,
2, 2, 2,
2, 2, 2, 2, 2, 2)
.Refresh BackgroundQuery:=False
End With

Look at GetOpenFileName in VBA Help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"maleemi" wrote in message
om...
The Basic Script below was captured using Excels "Record Macro"
feature. As recorded, it opens the file: "TEXT;P:\WB Test\Aspen Sample
Mail Files\020604\mailing\new\SBELECPRD_9111140388.SPF; 1" and performs
a series of actions on the file.

Instead of opening a specific file I would like this macro to instead
launch an "Open File" Dialog box to allow me to specify which file
will be processed.

Can anyone tell me how to add the "Open File" dialog to this macro?

Best Regards,

Marcel




Sub Process_Aspen_Mail_File()
'
' Process_Aspen_Mail_File Macro
' Macro recorded 2/5/2004 by preprint
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;P:\WB Test\Aspen Sample Mail
Files\020604\mailing\new\SBELECPRD_9111140388.SPF; 1" _
, Destination:=Range("A1"))
.Name = "SBELECPRD_9111140388.SPF;1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2)
.Refresh BackgroundQuery:=False
End With

Perhaps you're after something like:

Sub test()
Dim strFile As String

strFile = Application.GetOpenFilename("SPF Files (*.spf), *.spf")
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"maleemi" wrote in message
om...
The Basic Script below was captured using Excels "Record Macro"
feature. As recorded, it opens the file: "TEXT;P:\WB Test\Aspen Sample
Mail Files\020604\mailing\new\SBELECPRD_9111140388.SPF; 1" and performs
a series of actions on the file.

Instead of opening a specific file I would like this macro to instead
launch an "Open File" Dialog box to allow me to specify which file
will be processed.

Can anyone tell me how to add the "Open File" dialog to this macro?

Best Regards,

Marcel




Sub Process_Aspen_Mail_File()
'
' Process_Aspen_Mail_File Macro
' Macro recorded 2/5/2004 by preprint
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;P:\WB Test\Aspen Sample Mail
Files\020604\mailing\new\SBELECPRD_9111140388.SPF; 1" _
, Destination:=Range("A1"))
.Name = "SBELECPRD_9111140388.SPF;1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2)
.Refresh BackgroundQuery:=False
End With