User Select File on Import External Data
 

Using Excel 2003. I am importing data into a ws and applying filters to it
to use elsewhere. How can I have the query run so the user can select the
file to be imported? The file name is always the same, it is the directory &
sub directory that will be different each day. What I have so far is:

Sub ImportData()
'
Sheets("Import").Select

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;F:\2010\January\Jan31\COOP\dscdc004.rtf" ,
Destination:=Range("A1")) <---------this line is the problem - need to
select file
.Name = "dscdc004"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 172
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 9, 1, 9, 1, 9)
.TextFileFixedColumnWidths = Array(19, 74, 4, 47, 13)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Columns("A:C").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="WD"
Selection.AutoFilter Field:=3, Criteria1:="399.99", Operator:=xlAnd, _
Criteria2:="<485.00"
End Sub

Any help is apprectiated
--
Linda

User Select File on Import External Data
 

You're really importing an RTF file??

Dim myFilename as variant
....more code
myfilename = application.getopenfilename((filefilter:="RTF Files,*.rtf")

if myfilename = false then
'user hit cancel
exit sub
end if

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
& myfilename, Destination:=activesheet.Range("A1"))
....


(Untested, uncompiled. Watch for typos.)




L.Mathe wrote:

Using Excel 2003. I am importing data into a ws and applying filters to it
to use elsewhere. How can I have the query run so the user can select the
file to be imported? The file name is always the same, it is the directory &
sub directory that will be different each day. What I have so far is:

Sub ImportData()
'
Sheets("Import").Select

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;F:\2010\January\Jan31\COOP\dscdc004.rtf" ,
Destination:=Range("A1")) <---------this line is the problem - need to
select file
.Name = "dscdc004"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 172
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 9, 1, 9, 1, 9)
.TextFileFixedColumnWidths = Array(19, 74, 4, 47, 13)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Columns("A:C").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="WD"
Selection.AutoFilter Field:=3, Criteria1:="399.99", Operator:=xlAnd, _
Criteria2:="<485.00"
End Sub

Any help is apprectiated
--
Linda

--

Dave Peterson

User Select File on Import External Data
 

THANK YOU, it works! Thanks for the heads up to watch for typos, 1 minor
correction was done.

I had seen your website with this VBA, but didn't know how to work it into
mine. And, yep, it is a .rtf file. There is a huge .csv file (125 columns,
35,000 rows) that generates 4 reports. I only need one of the reports (with
fewer columns) to accomplish my task.

Thank you again for your help. Very much appreciated.
--
Linda


"Dave Peterson" wrote:

You're really importing an RTF file??

Dim myFilename as variant
....more code
myfilename = application.getopenfilename((filefilter:="RTF Files,*.rtf")

if myfilename = false then
'user hit cancel
exit sub
end if

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
& myfilename, Destination:=activesheet.Range("A1"))
....


(Untested, uncompiled. Watch for typos.)




L.Mathe wrote:

Using Excel 2003. I am importing data into a ws and applying filters to it
to use elsewhere. How can I have the query run so the user can select the
file to be imported? The file name is always the same, it is the directory &
sub directory that will be different each day. What I have so far is:

Sub ImportData()
'
Sheets("Import").Select

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;F:\2010\January\Jan31\COOP\dscdc004.rtf" ,
Destination:=Range("A1")) <---------this line is the problem - need to
select file
.Name = "dscdc004"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 172
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 9, 1, 9, 1, 9)
.TextFileFixedColumnWidths = Array(19, 74, 4, 47, 13)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Columns("A:C").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="WD"
Selection.AutoFilter Field:=3, Criteria1:="399.99", Operator:=xlAnd, _
Criteria2:="<485.00"
End Sub

Any help is apprectiated
--
Linda

--

Dave Peterson
.

User Select File on Import External Data
 

Oops. I see that extra ( in the .getopenfilename line (now!).

RTF extensions usually indicate that it's some sort of Rich Text Format (a file
option in MSWord).

There's usually other junk in that type of file besides data.

If you made the .rtf extention, you may want to be careful with your selection.

L.Mathe wrote:

THANK YOU, it works! Thanks for the heads up to watch for typos, 1 minor
correction was done.

I had seen your website with this VBA, but didn't know how to work it into
mine. And, yep, it is a .rtf file. There is a huge .csv file (125 columns,
35,000 rows) that generates 4 reports. I only need one of the reports (with
fewer columns) to accomplish my task.

Thank you again for your help. Very much appreciated.
--
Linda

"Dave Peterson" wrote:

You're really importing an RTF file??

Dim myFilename as variant
....more code
myfilename = application.getopenfilename((filefilter:="RTF Files,*.rtf")

if myfilename = false then
'user hit cancel
exit sub
end if

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
& myfilename, Destination:=activesheet.Range("A1"))
....


(Untested, uncompiled. Watch for typos.)




L.Mathe wrote:

Using Excel 2003. I am importing data into a ws and applying filters to it
to use elsewhere. How can I have the query run so the user can select the
file to be imported? The file name is always the same, it is the directory &
sub directory that will be different each day. What I have so far is:

Sub ImportData()
'
Sheets("Import").Select

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;F:\2010\January\Jan31\COOP\dscdc004.rtf" ,
Destination:=Range("A1")) <---------this line is the problem - need to
select file
.Name = "dscdc004"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 172
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 9, 1, 9, 1, 9)
.TextFileFixedColumnWidths = Array(19, 74, 4, 47, 13)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Columns("A:C").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="WD"
Selection.AutoFilter Field:=3, Criteria1:="399.99", Operator:=xlAnd, _
Criteria2:="<485.00"
End Sub

Any help is apprectiated
--
Linda

--

Dave Peterson
.


--

Dave Peterson