How do I add a File Open box to this script???

M

maleemi

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
 
C

Chip Pearson

Marcel,

Try something like

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

Bob Phillips

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)
 
R

Rob van Gelder

Perhaps you're after something like:

Sub test()
Dim strFile As String

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