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

  • Thread starter Thread starter maleemi
  • Start date Start date
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
 
Marcel,

Try something like

Dim FName As Variant
FName = Application.GetOpenFilename()
If FName = False Then
' no file choses
Else
' open FName
End If
 
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)
 
Perhaps you're after something like:

Sub test()
Dim strFile As String

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