Input Box selection and file open
 

Hello everyone,
I'd like an Input Box that lets a user choose from a list of file names,
which would then go ahead and open the selected file. Do I need to start with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a good
day.

Input Box selection and file open
 

You could do something like this...

Sub OpenFile()
Dim lngCount As Long
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
End With
End Sub

Look up the FileDialog Object in the help files as there are many properties
you can use to customize how it functions.

Rick


"cottage6" wrote in message
...
Hello everyone,
I'd like an Input Box that lets a user choose from a list of file names,
which would then go ahead and open the selected file. Do I need to start
with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a good
day.

Input Box selection and file open
 

Hey Rick, thanks for the quick response. Your suggestion is a good one, but
not quite what I want to do. This user needs to look for specific files that
are exported out of Mainframe so I wanted to make it as easy as possible for
her. Any ideas how I can display selected file names in an Input Box and
then have the file she selects open?

"Rick Rothstein (MVP - VB)" wrote:

You could do something like this...

Sub OpenFile()
Dim lngCount As Long
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
End With
End Sub

Look up the FileDialog Object in the help files as there are many properties
you can use to customize how it functions.

Rick


"cottage6" wrote in message
...
Hello everyone,
I'd like an Input Box that lets a user choose from a list of file names,
which would then go ahead and open the selected file. Do I need to start
with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a good
day.

Input Box selection and file open
 

You could try this... Assuming your Mainframe files are all delivered to a
fixed directory (assumed to be "c:\temp\" for my example below; change it as
needed), add a UserForm to your project and put a ListBox and a
CommandButton on it. Then copy/paste this code into the UserForm's code
window...

Dim Path As String
Dim FileName As String

Private Sub UserForm_Initialize()
' Note the required trailing back slash
Path = "c:\temp\"
FileName = Dir(Path & "*.xls")
Do While Len(FileName) 0
ListBox1.AddItem FileName
FileName = Dir()
Loop
End Sub

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me
End Sub

Private Sub ListBox1_Click()
FileName = Path & ListBox1.Text
End Sub

Now, to use this, simply execute this statement...

UserForm1.Show vbModeless

from within your own code whenever you want the user to open a file.

Rick


"cottage6" wrote in message
...
Hey Rick, thanks for the quick response. Your suggestion is a good one,
but
not quite what I want to do. This user needs to look for specific files
that
are exported out of Mainframe so I wanted to make it as easy as possible
for
her. Any ideas how I can display selected file names in an Input Box and
then have the file she selects open?

"Rick Rothstein (MVP - VB)" wrote:

You could do something like this...

Sub OpenFile()
Dim lngCount As Long
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
End With
End Sub

Look up the FileDialog Object in the help files as there are many
properties
you can use to customize how it functions.

Rick


"cottage6" wrote in message
...
Hello everyone,
I'd like an Input Box that lets a user choose from a list of file
names,
which would then go ahead and open the selected file. Do I need to
start
with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a
good
day.

Input Box selection and file open
 

Does this functionality exist in Excel 2000?

Input Box selection and file open
 

I'm not sure... I only have XL2003 and XL2007 here. Hopefully someone with
earlier versions than I have will respond.

Rick


"dan dungan" wrote in message
...
Does this functionality exist in Excel 2000?

Input Box selection and file open
 

Try this

ifilenumber = FreeFile()
sfilename = Application.GetOpenFilename(, , "Enter name of file:")
If sfilename = False Then Exit Sub
msgbox sfilename


"cottage6" wrote in message
...
Hello everyone,
I'd like an Input Box that lets a user choose from a list of file names,
which would then go ahead and open the selected file. Do I need to start
with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a good
day.

Input Box selection and file open
 

Rick, this code does work in both Excel 2000 and Excel 2002. However, I'm
getting an error when the selected file tries to open. I don't think the
path is being included so the file can't be found. The error is on
Workbooks.Open FileName. I added a messagebox prior to that line and I get
only the file name and not the path. I tried making a few changes but they
didn't work. Help? This is exactly what I need and it's very slick. If you
can help me make it work, this would be awesome!

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me

"Rick Rothstein (MVP - VB)" wrote:

You could try this... Assuming your Mainframe files are all delivered to a
fixed directory (assumed to be "c:\temp\" for my example below; change it as
needed), add a UserForm to your project and put a ListBox and a
CommandButton on it. Then copy/paste this code into the UserForm's code
window...

Dim Path As String
Dim FileName As String

Private Sub UserForm_Initialize()
' Note the required trailing back slash
Path = "c:\temp\"
FileName = Dir(Path & "*.xls")
Do While Len(FileName) 0
ListBox1.AddItem FileName
FileName = Dir()
Loop
End Sub

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me
End Sub

Private Sub ListBox1_Click()
FileName = Path & ListBox1.Text
End Sub

Now, to use this, simply execute this statement...

UserForm1.Show vbModeless

from within your own code whenever you want the user to open a file.

Rick


"cottage6" wrote in message
...
Hey Rick, thanks for the quick response. Your suggestion is a good one,
but
not quite what I want to do. This user needs to look for specific files
that
are exported out of Mainframe so I wanted to make it as easy as possible
for
her. Any ideas how I can display selected file names in an Input Box and
then have the file she selects open?

"Rick Rothstein (MVP - VB)" wrote:

You could do something like this...

Sub OpenFile()
Dim lngCount As Long
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
End With
End Sub

Look up the FileDialog Object in the help files as there are many
properties
you can use to customize how it functions.

Rick


"cottage6" wrote in message
...
Hello everyone,
I'd like an Input Box that lets a user choose from a list of file
names,
which would then go ahead and open the selected file. Do I need to
start
with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a
good
day.

Input Box selection and file open
 

Did you copy/paste the code I gave you exactly as I posted it? If you did,
the path **had** to be attached to the filename because it was prepended in
the ListBox1_Click event. The key to making this work is to have the Dim
statements for the Path and FileName be declared **only** in the
(General)(Declarations) section of the UserForm's code window and **not**
within any other procedures. If you declared them inside a procedure, then
that declaration for them (inside that procedure) would override the
"global" declaration that occurred by placing the Dim statements in the
(General)(Declarations) section.

To see how the code should work, open up a **new** copy of Excel, add the
UserForm, put the ListBox and CommandButton on it, and copy/paste this into
the UserForm's code window...

Dim Path As String
Dim FileName As String

Private Sub UserForm_Initialize()
' Note the required trailing back slash
Path = "c:\temp\"
FileName = Dir(Path & "*.xls")
Do While Len(FileName) 0
ListBox1.AddItem FileName
FileName = Dir()
Loop
End Sub

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me
End Sub

Private Sub ListBox1_Click()
FileName = Path & ListBox1.Text
End Sub

Don't forget to change the Path assignment to that for your actual
worksheets. Then open up the code window for one of the worksheets and
copy/paste this into its code window...

Sub Test()
UserForm1.Show vbModeless
End Sub

Just place your cursor on one of the statements above and click the Run
button. Everything should work as I originally described (the code above is
the same as I originally posted). If, for some reason, it still doesn't
work, put a MessageBox in front of the Workbooks.Open statement and have it
show you what is in the Path variable. If you can see the Path as you
originally set it, you can do a quick fix by changing the Workbooks.Open
statement to this...

Workbooks.Open Path & FileName

but that would mean something was still screwed up somewhere else and you
really ought to find and correct it.

Rick


"cottage6" wrote in message
...
Rick, this code does work in both Excel 2000 and Excel 2002. However, I'm
getting an error when the selected file tries to open. I don't think the
path is being included so the file can't be found. The error is on
Workbooks.Open FileName. I added a messagebox prior to that line and I
get
only the file name and not the path. I tried making a few changes but
they
didn't work. Help? This is exactly what I need and it's very slick. If
you
can help me make it work, this would be awesome!

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me

"Rick Rothstein (MVP - VB)" wrote:

You could try this... Assuming your Mainframe files are all delivered to
a
fixed directory (assumed to be "c:\temp\" for my example below; change it
as
needed), add a UserForm to your project and put a ListBox and a
CommandButton on it. Then copy/paste this code into the UserForm's code
window...

Dim Path As String
Dim FileName As String

Private Sub UserForm_Initialize()
' Note the required trailing back slash
Path = "c:\temp\"
FileName = Dir(Path & "*.xls")
Do While Len(FileName) 0
ListBox1.AddItem FileName
FileName = Dir()
Loop
End Sub

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me
End Sub

Private Sub ListBox1_Click()
FileName = Path & ListBox1.Text
End Sub

Now, to use this, simply execute this statement...

UserForm1.Show vbModeless

from within your own code whenever you want the user to open a file.

Rick


"cottage6" wrote in message
...
Hey Rick, thanks for the quick response. Your suggestion is a good
one,
but
not quite what I want to do. This user needs to look for specific files
that
are exported out of Mainframe so I wanted to make it as easy as
possible
for
her. Any ideas how I can display selected file names in an Input Box
and
then have the file she selects open?

"Rick Rothstein (MVP - VB)" wrote:

You could do something like this...

Sub OpenFile()
Dim lngCount As Long
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
End With
End Sub

Look up the FileDialog Object in the help files as there are many
properties
you can use to customize how it functions.

Rick


"cottage6" wrote in message
...
Hello everyone,
I'd like an Input Box that lets a user choose from a list of file
names,
which would then go ahead and open the selected file. Do I need to
start
with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a
good
day.

Input Box selection and file open
 

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

"Rick Rothstein (MVP - VB)" wrote:

Did you copy/paste the code I gave you exactly as I posted it? If you did,
the path **had** to be attached to the filename because it was prepended in
the ListBox1_Click event. The key to making this work is to have the Dim
statements for the Path and FileName be declared **only** in the
(General)(Declarations) section of the UserForm's code window and **not**
within any other procedures. If you declared them inside a procedure, then
that declaration for them (inside that procedure) would override the
"global" declaration that occurred by placing the Dim statements in the
(General)(Declarations) section.

To see how the code should work, open up a **new** copy of Excel, add the
UserForm, put the ListBox and CommandButton on it, and copy/paste this into
the UserForm's code window...

Dim Path As String
Dim FileName As String

Private Sub UserForm_Initialize()
' Note the required trailing back slash
Path = "c:\temp\"
FileName = Dir(Path & "*.xls")
Do While Len(FileName) 0
ListBox1.AddItem FileName
FileName = Dir()
Loop
End Sub

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me
End Sub

Private Sub ListBox1_Click()
FileName = Path & ListBox1.Text
End Sub

Don't forget to change the Path assignment to that for your actual
worksheets. Then open up the code window for one of the worksheets and
copy/paste this into its code window...

Sub Test()
UserForm1.Show vbModeless
End Sub

Just place your cursor on one of the statements above and click the Run
button. Everything should work as I originally described (the code above is
the same as I originally posted). If, for some reason, it still doesn't
work, put a MessageBox in front of the Workbooks.Open statement and have it
show you what is in the Path variable. If you can see the Path as you
originally set it, you can do a quick fix by changing the Workbooks.Open
statement to this...

Workbooks.Open Path & FileName

but that would mean something was still screwed up somewhere else and you
really ought to find and correct it.

Rick


"cottage6" wrote in message
...
Rick, this code does work in both Excel 2000 and Excel 2002. However, I'm
getting an error when the selected file tries to open. I don't think the
path is being included so the file can't be found. The error is on
Workbooks.Open FileName. I added a messagebox prior to that line and I
get
only the file name and not the path. I tried making a few changes but
they
didn't work. Help? This is exactly what I need and it's very slick. If
you
can help me make it work, this would be awesome!

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me

"Rick Rothstein (MVP - VB)" wrote:

You could try this... Assuming your Mainframe files are all delivered to
a
fixed directory (assumed to be "c:\temp\" for my example below; change it
as
needed), add a UserForm to your project and put a ListBox and a
CommandButton on it. Then copy/paste this code into the UserForm's code
window...

Dim Path As String
Dim FileName As String

Private Sub UserForm_Initialize()
' Note the required trailing back slash
Path = "c:\temp\"
FileName = Dir(Path & "*.xls")
Do While Len(FileName) 0
ListBox1.AddItem FileName
FileName = Dir()
Loop
End Sub

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me
End Sub

Private Sub ListBox1_Click()
FileName = Path & ListBox1.Text
End Sub

Now, to use this, simply execute this statement...

UserForm1.Show vbModeless

from within your own code whenever you want the user to open a file.

Rick


"cottage6" wrote in message
...
Hey Rick, thanks for the quick response. Your suggestion is a good
one,
but
not quite what I want to do. This user needs to look for specific files
that
are exported out of Mainframe so I wanted to make it as easy as
possible
for
her. Any ideas how I can display selected file names in an Input Box
and
then have the file she selects open?

"Rick Rothstein (MVP - VB)" wrote:

You could do something like this...

Sub OpenFile()
Dim lngCount As Long
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
End With
End Sub

Look up the FileDialog Object in the help files as there are many
properties
you can use to customize how it functions.

Rick


"cottage6" wrote in message
...
Hello everyone,
I'd like an Input Box that lets a user choose from a list of file
names,
which would then go ahead and open the selected file. Do I need to
start
with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a
good
day.

Input Box selection and file open
 

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

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 ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\E ASTPTC.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

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" wrote in message
...
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 ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\E ASTPTC.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

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" wrote in message
...
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 ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\E ASTPTC.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

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" wrote in message
...
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" wrote in message
...
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 ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\E ASTPTC.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

Input Box selection and file open
 

Sorry I'm just getting back to you; way too many emergencies here today. The
text file is only 53kb, and the size of the file depends on the number of
items included. These are Easter items but Christmas would be larger. I
don't believe size would exceed triple the current file size. Gracias!

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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" wrote in message
...
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 ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\E ASTPTC.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

Input Box selection and file open
 

Okay, give the code after my signature a try and see if it does what you
want. I'm not sure if everything will lay out correctly or not. The "fields"
in your posted data seemed to be different from the field widths specified
in the code you posted. I tried adjusting your data to match the field
widths shown in the code for my testing. If it turns out that the data
doesn't lay out correctly, post back the first row of data that gets put
into your worksheet placing each cell's value on a separate line so I can
see how the file is being actually being broken apart. Since the code worked
for you, I'm kind of expecting my subroutine to work also (given that I used
the field widths specified in the code you posted); but, of course, one
never knows. In any event, let me know how this subroutine works out, either
way.

Rick

Sub Import_PTC()
Dim X As Long
Dim Z As Long
Dim P As Long
Dim FileNum As Long
Dim Entry As Variant
Dim FieldWidths As Variant
Dim TotalFile As String
Dim Records() As String
FileNum = FreeFile
FieldWidths = Array(7, 26, 12, 5, 9, 10, 10, 15, 10, 15, 12)
Open "C:\TEMP\EASTPTC.TXT" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
With ActiveSheet
For X = 0 To UBound(Records)
P = 1
For Z = LBound(FieldWidths) To UBound(FieldWidths)
Entry = Mid$(Records(X), P, FieldWidths(Z))
If Z - LBound(FieldWidths) = 2 Then
Entry = "'" & Entry
End If
Cells(X + 1, Z + 1 - LBound(FieldWidths)).Value = Entry
P = P + FieldWidths(Z)
Next
Next
End With
End Sub



"cottage6" wrote in message
...
Sorry I'm just getting back to you; way too many emergencies here today.
The
text file is only 53kb, and the size of the file depends on the number of
items included. These are Easter items but Christmas would be larger. I
don't believe size would exceed triple the current file size. Gracias!

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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" wrote in message
...
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
ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\E ASTPTC.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

Input Box selection and file open
 

Rick,
Sorry I'm just getting back to you; I had major problems with a major
project. Your code is exquisite and works just the way it should! How can I
make the file name in
Open "C:\TEMP\EASTPTC.TXT" For Binary As #FileNum equal to the file name
selected by the user from the user form you had me create? I'll go ahead and
try a few things on my own and see if I can get the file name in the
meantime. Thanks again for all your help! Awesome stuff.

"Rick Rothstein (MVP - VB)" wrote:

Okay, give the code after my signature a try and see if it does what you
want. I'm not sure if everything will lay out correctly or not. The "fields"
in your posted data seemed to be different from the field widths specified
in the code you posted. I tried adjusting your data to match the field
widths shown in the code for my testing. If it turns out that the data
doesn't lay out correctly, post back the first row of data that gets put
into your worksheet placing each cell's value on a separate line so I can
see how the file is being actually being broken apart. Since the code worked
for you, I'm kind of expecting my subroutine to work also (given that I used
the field widths specified in the code you posted); but, of course, one
never knows. In any event, let me know how this subroutine works out, either
way.

Rick

Sub Import_PTC()
Dim X As Long
Dim Z As Long
Dim P As Long
Dim FileNum As Long
Dim Entry As Variant
Dim FieldWidths As Variant
Dim TotalFile As String
Dim Records() As String
FileNum = FreeFile
FieldWidths = Array(7, 26, 12, 5, 9, 10, 10, 15, 10, 15, 12)
Open "C:\TEMP\EASTPTC.TXT" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
With ActiveSheet
For X = 0 To UBound(Records)
P = 1
For Z = LBound(FieldWidths) To UBound(FieldWidths)
Entry = Mid$(Records(X), P, FieldWidths(Z))
If Z - LBound(FieldWidths) = 2 Then
Entry = "'" & Entry
End If
Cells(X + 1, Z + 1 - LBound(FieldWidths)).Value = Entry
P = P + FieldWidths(Z)
Next
Next
End With
End Sub



"cottage6" wrote in message
...
Sorry I'm just getting back to you; way too many emergencies here today.
The
text file is only 53kb, and the size of the file depends on the number of
items included. These are Easter items but Christmas would be larger. I
don't believe size would exceed triple the current file size. Gracias!

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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" wrote in message
...
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
ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\E ASTPTC.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

Input Box selection and file open
 

Sorry, the thread had gotten so long, coupled with the length of time it has
stretched over, I forgot all about the UserForm. If you put the Import_PTC
subroutine in the UserForm's code window, then the Path variable and ListBox
should be available to it, so you should be able to use this Open statement
in place of the one I gave you earlier...

Open Path & ListBox1.Text For Binary As #FileNum

Rick


"cottage6" wrote in message
...
Rick,
Sorry I'm just getting back to you; I had major problems with a major
project. Your code is exquisite and works just the way it should! How
can I
make the file name in
Open "C:\TEMP\EASTPTC.TXT" For Binary As #FileNum equal to the file name
selected by the user from the user form you had me create? I'll go ahead
and
try a few things on my own and see if I can get the file name in the
meantime. Thanks again for all your help! Awesome stuff.

"Rick Rothstein (MVP - VB)" wrote:

Okay, give the code after my signature a try and see if it does what you
want. I'm not sure if everything will lay out correctly or not. The
"fields"
in your posted data seemed to be different from the field widths
specified
in the code you posted. I tried adjusting your data to match the field
widths shown in the code for my testing. If it turns out that the data
doesn't lay out correctly, post back the first row of data that gets put
into your worksheet placing each cell's value on a separate line so I can
see how the file is being actually being broken apart. Since the code
worked
for you, I'm kind of expecting my subroutine to work also (given that I
used
the field widths specified in the code you posted); but, of course, one
never knows. In any event, let me know how this subroutine works out,
either
way.

Rick

Sub Import_PTC()
Dim X As Long
Dim Z As Long
Dim P As Long
Dim FileNum As Long
Dim Entry As Variant
Dim FieldWidths As Variant
Dim TotalFile As String
Dim Records() As String
FileNum = FreeFile
FieldWidths = Array(7, 26, 12, 5, 9, 10, 10, 15, 10, 15, 12)
Open "C:\TEMP\EASTPTC.TXT" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
With ActiveSheet
For X = 0 To UBound(Records)
P = 1
For Z = LBound(FieldWidths) To UBound(FieldWidths)
Entry = Mid$(Records(X), P, FieldWidths(Z))
If Z - LBound(FieldWidths) = 2 Then
Entry = "'" & Entry
End If
Cells(X + 1, Z + 1 - LBound(FieldWidths)).Value = Entry
P = P + FieldWidths(Z)
Next
Next
End With
End Sub



"cottage6" wrote in message
...
Sorry I'm just getting back to you; way too many emergencies here
today.
The
text file is only 53kb, and the size of the file depends on the number
of
items included. These are Easter items but Christmas would be larger.
I
don't believe size would exceed triple the current file size. Gracias!

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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" wrote in message
...
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
ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\E ASTPTC.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

Input Box selection and file open
 

Works like a charm! I can think of several files I can use this in. Never
ends for you does it....any way you know of that I can start the import at
row 45?

"Rick Rothstein (MVP - VB)" wrote:

Sorry, the thread had gotten so long, coupled with the length of time it has
stretched over, I forgot all about the UserForm. If you put the Import_PTC
subroutine in the UserForm's code window, then the Path variable and ListBox
should be available to it, so you should be able to use this Open statement
in place of the one I gave you earlier...

Open Path & ListBox1.Text For Binary As #FileNum

Rick


"cottage6" wrote in message
...
Rick,
Sorry I'm just getting back to you; I had major problems with a major
project. Your code is exquisite and works just the way it should! How
can I
make the file name in
Open "C:\TEMP\EASTPTC.TXT" For Binary As #FileNum equal to the file name
selected by the user from the user form you had me create? I'll go ahead
and
try a few things on my own and see if I can get the file name in the
meantime. Thanks again for all your help! Awesome stuff.

"Rick Rothstein (MVP - VB)" wrote:

Okay, give the code after my signature a try and see if it does what you
want. I'm not sure if everything will lay out correctly or not. The
"fields"
in your posted data seemed to be different from the field widths
specified
in the code you posted. I tried adjusting your data to match the field
widths shown in the code for my testing. If it turns out that the data
doesn't lay out correctly, post back the first row of data that gets put
into your worksheet placing each cell's value on a separate line so I can
see how the file is being actually being broken apart. Since the code
worked
for you, I'm kind of expecting my subroutine to work also (given that I
used
the field widths specified in the code you posted); but, of course, one
never knows. In any event, let me know how this subroutine works out,
either
way.

Rick

Sub Import_PTC()
Dim X As Long
Dim Z As Long
Dim P As Long
Dim FileNum As Long
Dim Entry As Variant
Dim FieldWidths As Variant
Dim TotalFile As String
Dim Records() As String
FileNum = FreeFile
FieldWidths = Array(7, 26, 12, 5, 9, 10, 10, 15, 10, 15, 12)
Open "C:\TEMP\EASTPTC.TXT" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
With ActiveSheet
For X = 0 To UBound(Records)
P = 1
For Z = LBound(FieldWidths) To UBound(FieldWidths)
Entry = Mid$(Records(X), P, FieldWidths(Z))
If Z - LBound(FieldWidths) = 2 Then
Entry = "'" & Entry
End If
Cells(X + 1, Z + 1 - LBound(FieldWidths)).Value = Entry
P = P + FieldWidths(Z)
Next
Next
End With
End Sub



"cottage6" wrote in message
...
Sorry I'm just getting back to you; way too many emergencies here
today.
The
text file is only 53kb, and the size of the file depends on the number
of
items included. These are Easter items but Christmas would be larger.
I
don't believe size would exceed triple the current file size. Gracias!

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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" wrote in message
...
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
ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\E ASTPTC.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

Input Box selection and file open
 

Works like a charm!

Great!

I can think of several files I can use this in. Never ends for you does
it....
any way you know of that I can start the import at row 45?

When you say "import at row 45", you mean start placing the text imported
from the file at row 45, right? You can control which line the print out
starts on by making a small change on the 6th line up from the bottom of the
Import_PTC subroutine. This is the line as it appeared in my code (where the
imported data was outputted to Row 1)...

Cells(X + 1, Z + 1 - LBound(FieldWidths)).Value = Entry

and here is that same line changed to start outputting the data on Row 45
instead...

Cells(X + 45, Z + 1 - LBound(FieldWidths)).Value = Entry

Notice, all I changed is the value being added to the X loop index value in
the first argument.

Rick

Input Box selection and file open
 

Hi Rick,
Actually in the Text Import Wizard, you can choose which row to start the
import with from the .txt file. The text file has 44 rows of total garbage
at the top that I don't need (header rows, the report name, etc). We have a
lot of old programs here. I can talk to the programmer about getting rid of
these because I think he can do that right in his job. Thanks again!

"Rick Rothstein (MVP - VB)" wrote:

Works like a charm!

Great!

I can think of several files I can use this in. Never ends for you does
it....
any way you know of that I can start the import at row 45?

When you say "import at row 45", you mean start placing the text imported
from the file at row 45, right? You can control which line the print out
starts on by making a small change on the 6th line up from the bottom of the
Import_PTC subroutine. This is the line as it appeared in my code (where the
imported data was outputted to Row 1)...

Cells(X + 1, Z + 1 - LBound(FieldWidths)).Value = Entry

and here is that same line changed to start outputting the data on Row 45
instead...

Cells(X + 45, Z + 1 - LBound(FieldWidths)).Value = Entry

Notice, all I changed is the value being added to the X loop index value in
the first argument.

Rick

Input Box selection and file open
 

The replacement Import_PTC subroutine below will skip over the first 44
lines of your file and begin outputting the 45th line onward starting at Row
1.

Rick

Sub Import_PTC()
Dim X As Long
Dim Z As Long
Dim P As Long
Dim FileNum As Long
Dim JunkCount As Long
Dim Entry As Variant
Dim FieldWidths As Variant
Dim TotalFile As String
Dim Records() As String
FileNum = FreeFile
FieldWidths = Array(7, 26, 12, 5, 9, 10, 10, 15, 10, 15, 12)
Open Path & ListBox1.Text For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
With ActiveSheet
For X = 45 To UBound(Records)
P = 1
For Z = LBound(FieldWidths) To UBound(FieldWidths)
Entry = Mid$(Records(X), P, FieldWidths(Z))
If Z - LBound(FieldWidths) = 2 Then
Entry = "'" & Entry
End If
Cells(X - 44, Z + 1 - LBound(FieldWidths)).Value = Entry
P = P + FieldWidths(Z)
Next
Next
End With
End Sub



"cottage6" wrote in message
...
Hi Rick,
Actually in the Text Import Wizard, you can choose which row to start the
import with from the .txt file. The text file has 44 rows of total
garbage
at the top that I don't need (header rows, the report name, etc). We have
a
lot of old programs here. I can talk to the programmer about getting rid
of
these because I think he can do that right in his job. Thanks again!

"Rick Rothstein (MVP - VB)" wrote:

Works like a charm!

Great!

I can think of several files I can use this in. Never ends for you
does
it....
any way you know of that I can start the import at row 45?

When you say "import at row 45", you mean start placing the text imported
from the file at row 45, right? You can control which line the print out
starts on by making a small change on the 6th line up from the bottom of
the
Import_PTC subroutine. This is the line as it appeared in my code (where
the
imported data was outputted to Row 1)...

Cells(X + 1, Z + 1 - LBound(FieldWidths)).Value = Entry

and here is that same line changed to start outputting the data on Row 45
instead...

Cells(X + 45, Z + 1 - LBound(FieldWidths)).Value = Entry

Notice, all I changed is the value being added to the X loop index value
in
the first argument.

Rick

Input Box selection and file open
 

Here is a possibly more general solution... if the "junk" in the front of
the file **never** can start with 7 digits followed by a space, then you can
use the following replacement Import_PTC subroutine to read the file (it
will skip over any number of lines of "junk" in the front of the file, not
just 45 of them).

Rick

Sub Import_PTC()
Dim X As Long
Dim Z As Long
Dim P As Long
Dim FileNum As Long
Dim JunkCount As Long
Dim Entry As Variant
Dim FieldWidths As Variant
Dim TotalFile As String
Dim Records() As String
FileNum = FreeFile
FieldWidths = Array(7, 26, 12, 5, 9, 10, 10, 15, 10, 15, 12)
Open Path & ListBox1.Text For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
With ActiveSheet
For X = 0 To UBound(Records)
If Records(X) Like "####### *" Then
P = 1
For Z = LBound(FieldWidths) To UBound(FieldWidths)
Entry = Mid$(Records(X), P, FieldWidths(Z))
If Z - LBound(FieldWidths) = 2 Then
Entry = "'" & Entry
End If
Cells(X + 1 - JunkCount, Z + 1 - LBound(FieldWidths)).Value =
Entry
P = P + FieldWidths(Z)
Next
Else
JunkCount = JunkCount + 1
End If
Next
End With
End Sub



"Rick Rothstein (MVP - VB)" wrote in
message ...
The replacement Import_PTC subroutine below will skip over the first 44
lines of your file and begin outputting the 45th line onward starting at
Row 1.

Rick

Sub Import_PTC()
Dim X As Long
Dim Z As Long
Dim P As Long
Dim FileNum As Long
Dim JunkCount As Long
Dim Entry As Variant
Dim FieldWidths As Variant
Dim TotalFile As String
Dim Records() As String
FileNum = FreeFile
FieldWidths = Array(7, 26, 12, 5, 9, 10, 10, 15, 10, 15, 12)
Open Path & ListBox1.Text For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
With ActiveSheet
For X = 45 To UBound(Records)
P = 1
For Z = LBound(FieldWidths) To UBound(FieldWidths)
Entry = Mid$(Records(X), P, FieldWidths(Z))
If Z - LBound(FieldWidths) = 2 Then
Entry = "'" & Entry
End If
Cells(X - 44, Z + 1 - LBound(FieldWidths)).Value = Entry
P = P + FieldWidths(Z)
Next
Next
End With
End Sub



"cottage6" wrote in message
...
Hi Rick,
Actually in the Text Import Wizard, you can choose which row to start the
import with from the .txt file. The text file has 44 rows of total
garbage
at the top that I don't need (header rows, the report name, etc). We
have a
lot of old programs here. I can talk to the programmer about getting rid
of
these because I think he can do that right in his job. Thanks again!

"Rick Rothstein (MVP - VB)" wrote:

Works like a charm!

Great!

I can think of several files I can use this in. Never ends for you
does
it....
any way you know of that I can start the import at row 45?

When you say "import at row 45", you mean start placing the text
imported
from the file at row 45, right? You can control which line the print out
starts on by making a small change on the 6th line up from the bottom of
the
Import_PTC subroutine. This is the line as it appeared in my code (where
the
imported data was outputted to Row 1)...

Cells(X + 1, Z + 1 - LBound(FieldWidths)).Value = Entry

and here is that same line changed to start outputting the data on Row
45
instead...

Cells(X + 45, Z + 1 - LBound(FieldWidths)).Value = Entry

Notice, all I changed is the value being added to the X loop index value
in
the first argument.

Rick

Input Box selection and file open
 

Hey Rick, thanks for both solutions. I've tried both of them and they work
great. I'm sure I'll use both methods in the future, and it always helps to
get several solutions. That's the only way I ever learn. Again, thanks for
all your help!

"Rick Rothstein (MVP - VB)" wrote:

Here is a possibly more general solution... if the "junk" in the front of
the file **never** can start with 7 digits followed by a space, then you can
use the following replacement Import_PTC subroutine to read the file (it
will skip over any number of lines of "junk" in the front of the file, not
just 45 of them).

Rick

Sub Import_PTC()
Dim X As Long
Dim Z As Long
Dim P As Long
Dim FileNum As Long
Dim JunkCount As Long
Dim Entry As Variant
Dim FieldWidths As Variant
Dim TotalFile As String
Dim Records() As String
FileNum = FreeFile
FieldWidths = Array(7, 26, 12, 5, 9, 10, 10, 15, 10, 15, 12)
Open Path & ListBox1.Text For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
With ActiveSheet
For X = 0 To UBound(Records)
If Records(X) Like "####### *" Then
P = 1
For Z = LBound(FieldWidths) To UBound(FieldWidths)
Entry = Mid$(Records(X), P, FieldWidths(Z))
If Z - LBound(FieldWidths) = 2 Then
Entry = "'" & Entry
End If
Cells(X + 1 - JunkCount, Z + 1 - LBound(FieldWidths)).Value =
Entry
P = P + FieldWidths(Z)
Next
Else
JunkCount = JunkCount + 1
End If
Next
End With
End Sub



"Rick Rothstein (MVP - VB)" wrote in
message ...
The replacement Import_PTC subroutine below will skip over the first 44
lines of your file and begin outputting the 45th line onward starting at
Row 1.

Rick

Sub Import_PTC()
Dim X As Long
Dim Z As Long
Dim P As Long
Dim FileNum As Long
Dim JunkCount As Long
Dim Entry As Variant
Dim FieldWidths As Variant
Dim TotalFile As String
Dim Records() As String
FileNum = FreeFile
FieldWidths = Array(7, 26, 12, 5, 9, 10, 10, 15, 10, 15, 12)
Open Path & ListBox1.Text For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
With ActiveSheet
For X = 45 To UBound(Records)
P = 1
For Z = LBound(FieldWidths) To UBound(FieldWidths)
Entry = Mid$(Records(X), P, FieldWidths(Z))
If Z - LBound(FieldWidths) = 2 Then
Entry = "'" & Entry
End If
Cells(X - 44, Z + 1 - LBound(FieldWidths)).Value = Entry
P = P + FieldWidths(Z)
Next
Next
End With
End Sub



"cottage6" wrote in message
...
Hi Rick,
Actually in the Text Import Wizard, you can choose which row to start the
import with from the .txt file. The text file has 44 rows of total
garbage
at the top that I don't need (header rows, the report name, etc). We
have a
lot of old programs here. I can talk to the programmer about getting rid
of
these because I think he can do that right in his job. Thanks again!

"Rick Rothstein (MVP - VB)" wrote:

Works like a charm!

Great!

I can think of several files I can use this in. Never ends for you
does
it....
any way you know of that I can start the import at row 45?

When you say "import at row 45", you mean start placing the text
imported
from the file at row 45, right? You can control which line the print out
starts on by making a small change on the 6th line up from the bottom of
the
Import_PTC subroutine. This is the line as it appeared in my code (where
the
imported data was outputted to Row 1)...

Cells(X + 1, Z + 1 - LBound(FieldWidths)).Value = Entry

and here is that same line changed to start outputting the data on Row
45
instead...

Cells(X + 45, Z + 1 - LBound(FieldWidths)).Value = Entry

Notice, all I changed is the value being added to the X loop index value
in
the first argument.

Rick

Input Box selection and file open
 

My pleasure... I'm glad this all worked out for you.

Remember, by the way, that the "general" solution requires the "junk" at the
beginning of the file to **never** have a line start with 7 digits followed
by a space. If you are guaranteed that, then the general solution will work
even if the number of lines of "junk" should ever change in the future.

Rick


"cottage6" wrote in message
...
Hey Rick, thanks for both solutions. I've tried both of them and they
work
great. I'm sure I'll use both methods in the future, and it always helps
to
get several solutions. That's the only way I ever learn. Again, thanks
for
all your help!

"Rick Rothstein (MVP - VB)" wrote:

Here is a possibly more general solution... if the "junk" in the front of
the file **never** can start with 7 digits followed by a space, then you
can
use the following replacement Import_PTC subroutine to read the file (it
will skip over any number of lines of "junk" in the front of the file,
not
just 45 of them).

Rick

Sub Import_PTC()
Dim X As Long
Dim Z As Long
Dim P As Long
Dim FileNum As Long
Dim JunkCount As Long
Dim Entry As Variant
Dim FieldWidths As Variant
Dim TotalFile As String
Dim Records() As String
FileNum = FreeFile
FieldWidths = Array(7, 26, 12, 5, 9, 10, 10, 15, 10, 15, 12)
Open Path & ListBox1.Text For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
With ActiveSheet
For X = 0 To UBound(Records)
If Records(X) Like "####### *" Then
P = 1
For Z = LBound(FieldWidths) To UBound(FieldWidths)
Entry = Mid$(Records(X), P, FieldWidths(Z))
If Z - LBound(FieldWidths) = 2 Then
Entry = "'" & Entry
End If
Cells(X + 1 - JunkCount, Z + 1 - LBound(FieldWidths)).Value =
Entry
P = P + FieldWidths(Z)
Next
Else
JunkCount = JunkCount + 1
End If
Next
End With
End Sub



"Rick Rothstein (MVP - VB)" wrote
in
message ...
The replacement Import_PTC subroutine below will skip over the first 44
lines of your file and begin outputting the 45th line onward starting
at
Row 1.

Rick

Sub Import_PTC()
Dim X As Long
Dim Z As Long
Dim P As Long
Dim FileNum As Long
Dim JunkCount As Long
Dim Entry As Variant
Dim FieldWidths As Variant
Dim TotalFile As String
Dim Records() As String
FileNum = FreeFile
FieldWidths = Array(7, 26, 12, 5, 9, 10, 10, 15, 10, 15, 12)
Open Path & ListBox1.Text For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Records = Split(TotalFile, vbCrLf)
With ActiveSheet
For X = 45 To UBound(Records)
P = 1
For Z = LBound(FieldWidths) To UBound(FieldWidths)
Entry = Mid$(Records(X), P, FieldWidths(Z))
If Z - LBound(FieldWidths) = 2 Then
Entry = "'" & Entry
End If
Cells(X - 44, Z + 1 - LBound(FieldWidths)).Value = Entry
P = P + FieldWidths(Z)
Next
Next
End With
End Sub



"cottage6" wrote in message
...
Hi Rick,
Actually in the Text Import Wizard, you can choose which row to start
the
import with from the .txt file. The text file has 44 rows of total
garbage
at the top that I don't need (header rows, the report name, etc). We
have a
lot of old programs here. I can talk to the programmer about getting
rid
of
these because I think he can do that right in his job. Thanks again!

"Rick Rothstein (MVP - VB)" wrote:

Works like a charm!

Great!

I can think of several files I can use this in. Never ends for you
does
it....
any way you know of that I can start the import at row 45?

When you say "import at row 45", you mean start placing the text
imported
from the file at row 45, right? You can control which line the print
out
starts on by making a small change on the 6th line up from the bottom
of
the
Import_PTC subroutine. This is the line as it appeared in my code
(where
the
imported data was outputted to Row 1)...

Cells(X + 1, Z + 1 - LBound(FieldWidths)).Value = Entry

and here is that same line changed to start outputting the data on
Row
45
instead...

Cells(X + 45, Z + 1 - LBound(FieldWidths)).Value = Entry

Notice, all I changed is the value being added to the X loop index
value
in
the first argument.

Rick