Re: Input Box selection and file open

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


.