Re: Input Box selection and file open

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



.

Loading