User Select File on Import External Data


L

L.Mathe

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
 
Ad

Advertisements

D

Dave Peterson

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

L.Mathe

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.
 
Ad

Advertisements

D

Dave Peterson

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 said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top