Here's my problem, I have a text file with over 7000+ rows and roughly 8
columns, out of all the rows I only need about 150 of the rows and only 4 of
the columns. The rows are sorted by Contract ID such as "AA", "BB" and so
on. What I need are the "EJ" and "EM" contracts, the problem is that the
amount of contracts change on a daily basis, one day there might be 45 "EJ"'s
and the next 61 of them.

Is there any Lookup or VBA code that I can do a lookup of the "EJ" and "EM"
lines and have it return ALL of the lines so that I can then export into a
database. And before the question is asked I have already tried importing it
directly into the database. It gets a "Internal Internet Failure" error.

Thank you for any assistance.

You may open the text file in Excel and use a filter to do the job. if you
want a macro, use :

Sub test()
Dim myRange As Range, BeginRow As Long, EndRow As Long
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
Set myRange = Range("A" & BeginRow & ":D" & EndRow)
myRange.Select
End Sub

It is assumed that there are no contract ID betwenn EJ and EM. Othrwise, the
macro should be modified.
--
Regards.
Daniel
"TerryM" a écrit dans le message de news:
...
Here's my problem, I have a text file with over 7000+ rows and roughly 8
columns, out of all the rows I only need about 150 of the rows and only 4
of
the columns. The rows are sorted by Contract ID such as "AA", "BB" and so
on. What I need are the "EJ" and "EM" contracts, the problem is that the
amount of contracts change on a daily basis, one day there might be 45
"EJ"'s
and the next 61 of them.

Is there any Lookup or VBA code that I can do a lookup of the "EJ" and
"EM"
lines and have it return ALL of the lines so that I can then export into a
database. And before the question is asked I have already tried importing
it
directly into the database. It gets a "Internal Internet Failure" error.

Thank you for any assistance.

That worked great, know I guess I have 2 remaining questions. How do I get
that data to export to an Access table with the days date that it was
imported? This is probably more of an Access question than anything.

The other question, I have a code in MS Access that I run that opens up the
Excel workbook and runs a data refresh and save when I run the update feature
for this worksheet it always asks me for the file name, where to look for the
text file that I am importing. Is there any way to code this in so it
automatically does it?

Here is the URL is http://www.nymerc.com/futures/innf.txt of the text file
that I am Importing.

"Daniel.C" wrote:

You may open the text file in Excel and use a filter to do the job. if you
want a macro, use :

Sub test()
Dim myRange As Range, BeginRow As Long, EndRow As Long
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
Set myRange = Range("A" & BeginRow & ":D" & EndRow)
myRange.Select
End Sub

It is assumed that there are no contract ID betwenn EJ and EM. Othrwise, the
macro should be modified.
--
Regards.
Daniel
"TerryM" a écrit dans le message de news:
...
Here's my problem, I have a text file with over 7000+ rows and roughly 8
columns, out of all the rows I only need about 150 of the rows and only 4
of
the columns. The rows are sorted by Contract ID such as "AA", "BB" and so
on. What I need are the "EJ" and "EM" contracts, the problem is that the
amount of contracts change on a daily basis, one day there might be 45
"EJ"'s
and the next 61 of them.

Is there any Lookup or VBA code that I can do a lookup of the "EJ" and
"EM"
lines and have it return ALL of the lines so that I can then export into a
database. And before the question is asked I have already tried importing
it
directly into the database. It gets a "Internal Internet Failure" error.

Thank you for any assistance.

"TerryM" a écrit dans le message de news:
...
That worked great, know I guess I have 2 remaining questions. How do I
get
that data to export to an Access table with the days date that it was
imported? This is probably more of an Access question than anything.

It depends of the structure of your table.

The other question, I have a code in MS Access that I run that opens up
the
Excel workbook and runs a data refresh and save when I run the update
feature
for this worksheet it always asks me for the file name, where to look for
the
text file that I am importing. Is there any way to code this in so it
automatically does it?

The following macro asks for the file path, open the text file, select the
data and create a new workbook, named test.xls in "c:\temp" folder :

Sub test3()
Dim myRange As Range, BeginRow As Long, EndRow As Long
Dim myFile As String
With Application.FileDialog(msoFileDialogOpen)
.Show
myFile = .SelectedItems(1)
End With
Workbooks.OpenText Filename:=myFile, Origin:= _
xlMSDOS, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote _
, ConsecutiveDelimiter:=True, Space:=True, Other:=False,
OtherChar:=".", FieldInfo:=Array(Array(1, 1 _
), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 3))
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
Set myRange = Range("A" & BeginRow & ":D" & EndRow)
myRange.Copy
Workbooks.Add 1
ActiveSheet.Paste
ActiveWorkbook.SaveAs "c:\temp\test.xls"
ActiveWorkbook.Close
End Sub

If the text file retains its name and path, I can modify the macro.

Daniel

Daniel,

If I'm understanding you correctly when you said "does the text file path
and name remain constant", yes it does. The http:// address and text file
name remains the same. All they do is put a new text file with the same name
and location each day around noon.

Sorry it took me so long to get back, I was moving to a new place.

Terry

"Daniel.C" wrote:

"TerryM" a écrit dans le message de news:
...
That worked great, know I guess I have 2 remaining questions. How do I
get
that data to export to an Access table with the days date that it was
imported? This is probably more of an Access question than anything.

It depends of the structure of your table.

The other question, I have a code in MS Access that I run that opens up
the
Excel workbook and runs a data refresh and save when I run the update
feature
for this worksheet it always asks me for the file name, where to look for
the
text file that I am importing. Is there any way to code this in so it
automatically does it?

The following macro asks for the file path, open the text file, select the
data and create a new workbook, named test.xls in "c:\temp" folder :

Sub test3()
Dim myRange As Range, BeginRow As Long, EndRow As Long
Dim myFile As String
With Application.FileDialog(msoFileDialogOpen)
.Show
myFile = .SelectedItems(1)
End With
Workbooks.OpenText Filename:=myFile, Origin:= _
xlMSDOS, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote _
, ConsecutiveDelimiter:=True, Space:=True, Other:=False,
OtherChar:=".", FieldInfo:=Array(Array(1, 1 _
), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 3))
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
Set myRange = Range("A" & BeginRow & ":D" & EndRow)
myRange.Copy
Workbooks.Add 1
ActiveSheet.Paste
ActiveWorkbook.SaveAs "c:\temp\test.xls"
ActiveWorkbook.Close
End Sub

If the text file retains its name and path, I can modify the macro.

Daniel

From Excel, the code is :
Workbooks.OpenText "http://www.nymerc.com/futures/innf.txt"
if it's executed from Access, post your code and i try to change it
--
Cordialement.
Daniel
"TerryM" a écrit dans le message de news:
...
Daniel,

If I'm understanding you correctly when you said "does the text file path
and name remain constant", yes it does. The http:// address and text file
name remains the same. All they do is put a new text file with the same
name
and location each day around noon.

Sorry it took me so long to get back, I was moving to a new place.

Terry

"Daniel.C" wrote:

"TerryM" a écrit dans le message de
news:
...
That worked great, know I guess I have 2 remaining questions. How do I
get
that data to export to an Access table with the days date that it was
imported? This is probably more of an Access question than anything.

It depends of the structure of your table.

The other question, I have a code in MS Access that I run that opens up
the
Excel workbook and runs a data refresh and save when I run the update
feature
for this worksheet it always asks me for the file name, where to look
for
the
text file that I am importing. Is there any way to code this in so it
automatically does it?

The following macro asks for the file path, open the text file, select
the
data and create a new workbook, named test.xls in "c:\temp" folder :

Sub test3()
Dim myRange As Range, BeginRow As Long, EndRow As Long
Dim myFile As String
With Application.FileDialog(msoFileDialogOpen)
.Show
myFile = .SelectedItems(1)
End With
Workbooks.OpenText Filename:=myFile, Origin:= _
xlMSDOS, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote _
, ConsecutiveDelimiter:=True, Space:=True, Other:=False,
OtherChar:=".", FieldInfo:=Array(Array(1, 1 _
), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6,
1),
Array(7, 3))
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
Set myRange = Range("A" & BeginRow & ":D" & EndRow)
myRange.Copy
Workbooks.Add 1
ActiveSheet.Paste
ActiveWorkbook.SaveAs "c:\temp\test.xls"
ActiveWorkbook.Close
End Sub

If the text file retains its name and path, I can modify the macro.

Daniel