Re: Input Box selection and file open

Sorry, one other question... about how big is your EASTPTC.TXT text file (that is, the file size shown in Windows Explorer)? Actually, I'm interested in how large that file could ever be... its maximum possible size (but if you don't know, the existing file size should give me enough of a hint). This question has to do with the method I'll use to read data from the file.

Rick


"cottage6" <cottage6@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:EBF10BB1-6667-48B0-B652-04B43B78B8CA@xxxxxxxxxxxxxxxx
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
>>
>>



.