Re: Input Box selection and file open

Sorry Rick, I didn't notice when the data pasted that there were blank rows.
Each new row starts with the item code; 1166702, 1166702, 1166703, etc. and
can be repeated as shown. No blank lines between the data. When I recorded
the code (Data - Get External Data - Import Text File), that invoked the Text
Import Wizard, and I set my column breaks. I never created a query table
myself; must get created when you record. I hope I've answered sufficiently.
Thanks a lot!



"Rick Rothstein (MVP - VB)" wrote:

I've never used QueryTables before, but I think I can work around that for
you. However, I need a clarification on your text file. Is each of those
lines of data you showed actually on separate lines in the file itself? If
yes, I'm assuming they are not separated by blank lines the way you showed
in your posting, right? The reason I ask is because when I tried to run your
code, the Text-to-Column dialog box that comes up shows everything on one
line (that may have to do with my not understanding QueryTables; but, as I
said, I think I can totally avoid this problem once I understand your data
layout better).

Rick


"cottage6" <cottage6@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DB4E38DF-AEBE-4A0F-AB6E-50182EF76DB4@xxxxxxxxxxxxxxxx
Here's the code; it's really long because I recorded it. I probably don't
need all of it.
Wish I had your brains.....
Sub Import_PTC()
Application.Dialogs(xlDialogSaveAs).Show

With Active***.QueryTables.Add(Connection:="TEXT;F:\EASTPTC.TXT", _
Destination:=Range("A1"))
.Name = "EASTPTC"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileStartRow = 45
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1)
.TextFileFixedColumnWidths = Array(7, 26, 12, 5, 9, 10, 10, 15, 10,
15, 12)
.Refresh BackgroundQuery:=False
End With

Text file I get from Mainframe looks like this:

1166702 PAAS COLOR CUPS DSPLY 07116938070 54 5 CT 24
1296
38.94 2114 -818.00 163.100 %

1166702 PAAS COLOR CUPS DSPLY 07116938076 54 5 CT 29
1566
0.00 0 1566.00 00.000 %

1166703 PAAS MED FRIENDS DYE KT 04110030209 81 1 CT 50
4050
0.00 0 4050.00 00.000 %

1166703 PAAS MED FRIENDS DYE KT 04110033074 81 1 CT 41
3321
68.98 5616 -2295.00 169.100 %

1166704 PAAS TIE DYE EGG KIT DS 07116933061 54 1 CT 45
2430
0.00 0 2430.00 00.000 %

"Rick Rothstein (MVP - VB)" wrote:

Rick you've obviously been working with idiots for too long because
that's
exactly what I did; I repeated the Dim statements. All works correctly
now.

LOL... no, that was not the reason... overriding the global Dim
statements
is pretty much the only way to produce the result you told me you were
getting.

I do have another question...These are all .txt files that need to be
imported. I have a procedure that does that and delimits the file
while
it's
opening. Is there any way to combine that with this file open process?

Can you show me the code you are using to do that? Also, can you show me
maybe 4 or 5 lines from one of your typical text files so I can see what
you
are working with?

Rick




.