Macro or VBA Code to return Multiple Rows?

[TerryM]

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.

 

[Daniel.C]

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 & "" & EndRow)
myRange.Select
End Sub

It is assumed that there are no contract ID betwenn EJ and EM. Othrwise, the
macro should be modified.

 

[TerryM]

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.

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 & "" & 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

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.

 

[Daniel.C]

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 & "" & 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

 

[TerryM]

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

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 & "" & 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.C]

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

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

"TerryM" <[email protected]> a écrit dans le message de
(e-mail address removed)...

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 & "" & 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

 

[TerryM]

Daniel,

Here is a question, when I run the code/macro from earlier, it does find the
EJ and the EM. Unfortunately it also lists everything inbetween them. Is
there a way to rectify this? I'm still trying to figure out what changes I
need to make to your existing code to get stuff to work.

Terry

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

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

"TerryM" <[email protected]> a écrit dans le message de
(e-mail address removed)...
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 & "" & 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.C]

Try :

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

Daniel,

Here is a question, when I run the code/macro from earlier, it does find
the
EJ and the EM. Unfortunately it also lists everything inbetween them. Is
there a way to rectify this? I'm still trying to figure out what changes
I
need to make to your existing code to get stuff to work.

Terry

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" <[email protected]> a écrit dans le message de
(e-mail address removed)...

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

:

"TerryM" <[email protected]> a écrit dans le message de
(e-mail address removed)...
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 & "" & 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

 

[TerryM]

Daniel,

Like I first stated I'm not exactly that good at programming syntax. Where
the heck am I messing up?

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 "http://www.nymerc.com/futures/innf.txt" = 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 & "" & EndRow)
myRange.Copy
Workbooks.Add 1
ActiveSheet.Paste
ActiveWorkbook.SaveAs "c:\temp\test.xls"
ActiveWorkbook.Close
End Sub

I keep on getting Compile error:

With Object must be user-defined type, object, or variant.

Try :

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

Daniel,

Here is a question, when I run the code/macro from earlier, it does find
the
EJ and the EM. Unfortunately it also lists everything inbetween them. Is
there a way to rectify this? I'm still trying to figure out what changes
I
need to make to your existing code to get stuff to work.

Terry

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" <[email protected]> a écrit dans le message de
(e-mail address removed)...
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

:

"TerryM" <[email protected]> a écrit dans le message de
(e-mail address removed)...
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 & "" & 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.C]

Sorry to have been so long to understand. The following macro should do the
work. If it's the case, i'll add comments for better understanding :

Sub test1()
Dim wb As Workbook, BeginRow As Long, EndRow As Long
Dim myFile As String

Workbooks.OpenText "http://www.nymerc.com/futures/innf.txt"
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EJ", [A65000], , , xlColumns, xlPrevious).Row
Range("A" & BeginRow & "" & EndRow).Copy
Workbooks.Add 1
Set wb = ActiveWorkbook
ActiveSheet.Paste
[A65000].End(xlUp).Offset(1).Select
Workbooks("innf.txt").Activate
BeginRow = [A:A].Find("EM", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
Range("A" & BeginRow & "" & EndRow).Copy
wb.Activate
ActiveSheet.Paste
ActiveWorkbook.SaveAs "c:\temp\test.xls"
ActiveWorkbook.Close
End Sub

--
Regards.
Daniel

Daniel,

Like I first stated I'm not exactly that good at programming syntax.
Where
the heck am I messing up?

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 "http://www.nymerc.com/futures/innf.txt" = 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 & "" & EndRow)
myRange.Copy
Workbooks.Add 1
ActiveSheet.Paste
ActiveWorkbook.SaveAs "c:\temp\test.xls"
ActiveWorkbook.Close
End Sub

I keep on getting Compile error:

With Object must be user-defined type, object, or variant.

Try :

Sub test()
Dim myRange1 As Range, BeginRow As Long, EndRow As Long
Dim myRange2 As Range
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EJ", [A65000], , , xlColumns, xlPrevious).Row
Set myRange1 = Range("A" & BeginRow & "" & EndRow)
BeginRow = [A:A].Find("EM", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
Set myRange2 = Range("A" & BeginRow & "" & EndRow)
Union(myRange1, myRange2).Select
End Sub
Regards.
Daniel
"TerryM" <[email protected]> a écrit dans le message de
(e-mail address removed)...

Daniel,

Here is a question, when I run the code/macro from earlier, it does
find
the
EJ and the EM. Unfortunately it also lists everything inbetween them.
Is
there a way to rectify this? I'm still trying to figure out what
changes
I
need to make to your existing code to get stuff to work.

Terry

:

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" <[email protected]> a écrit dans le message de
(e-mail address removed)...
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

:

"TerryM" <[email protected]> a écrit dans le message
de
(e-mail address removed)...
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 & "" & 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

 

[TerryM]

Daniel,

Here's a question. With using the named ranges "myrange1","myRange2" in vba
code is there anyway I can use the named ranges to export out of Excel. How
I have the other exports configured is Access looks for a specific named
range on the spread sheet and imports that data. The code that you wrote
does exactly what I was needing, now I just have to figure out how to get he
information to export.

Sorry to have been so long to understand. The following macro should do the
work. If it's the case, i'll add comments for better understanding :

Sub test1()
Dim wb As Workbook, BeginRow As Long, EndRow As Long
Dim myFile As String

Workbooks.OpenText "http://www.nymerc.com/futures/innf.txt"
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EJ", [A65000], , , xlColumns, xlPrevious).Row
Range("A" & BeginRow & "" & EndRow).Copy
Workbooks.Add 1
Set wb = ActiveWorkbook
ActiveSheet.Paste
[A65000].End(xlUp).Offset(1).Select
Workbooks("innf.txt").Activate
BeginRow = [A:A].Find("EM", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
Range("A" & BeginRow & "" & EndRow).Copy
wb.Activate
ActiveSheet.Paste
ActiveWorkbook.SaveAs "c:\temp\test.xls"
ActiveWorkbook.Close
End Sub

--
Regards.
Daniel

Daniel,

Like I first stated I'm not exactly that good at programming syntax.
Where
the heck am I messing up?

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 "http://www.nymerc.com/futures/innf.txt" = 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 & "" & EndRow)
myRange.Copy
Workbooks.Add 1
ActiveSheet.Paste
ActiveWorkbook.SaveAs "c:\temp\test.xls"
ActiveWorkbook.Close
End Sub

I keep on getting Compile error:

With Object must be user-defined type, object, or variant.

Try :

Sub test()
Dim myRange1 As Range, BeginRow As Long, EndRow As Long
Dim myRange2 As Range
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EJ", [A65000], , , xlColumns, xlPrevious).Row
Set myRange1 = Range("A" & BeginRow & "" & EndRow)
BeginRow = [A:A].Find("EM", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
Set myRange2 = Range("A" & BeginRow & "" & EndRow)
Union(myRange1, myRange2).Select
End Sub
Regards.
Daniel
"TerryM" <[email protected]> a écrit dans le message de
(e-mail address removed)...
Daniel,

Here is a question, when I run the code/macro from earlier, it does
find
the
EJ and the EM. Unfortunately it also lists everything inbetween them.
Is
there a way to rectify this? I'm still trying to figure out what
changes
I
need to make to your existing code to get stuff to work.

Terry

:

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" <[email protected]> a écrit dans le message de
(e-mail address removed)...
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

:

"TerryM" <[email protected]> a écrit dans le message
de
(e-mail address removed)...
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 & "" & 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.C]

Up to now, myRange1 and myRange2 are no named ranges but variables. The
following code creates named ranges :

Sub test()
Workbooks.OpenText "http://www.nymerc.com/futures/innf.txt"
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EJ", [A65000], , , xlColumns, xlPrevious).Row
ActiveWorkbook.Names.Add "myRange1", Range("A" & BeginRow & "" &
EndRow).Address
BeginRow = [A:A].Find("EM", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
ActiveWorkbook.Names.Add "myRange2", Range("A" & BeginRow & "" &
EndRow).Address
End Sub

--
Regards.
Daniel

Daniel,

Here's a question. With using the named ranges "myrange1","myRange2" in
vba
code is there anyway I can use the named ranges to export out of Excel.
How
I have the other exports configured is Access looks for a specific named
range on the spread sheet and imports that data. The code that you wrote
does exactly what I was needing, now I just have to figure out how to get
he
information to export.

Sorry to have been so long to understand. The following macro should do
the
work. If it's the case, i'll add comments for better understanding :

Sub test1()
Dim wb As Workbook, BeginRow As Long, EndRow As Long
Dim myFile As String

Workbooks.OpenText "http://www.nymerc.com/futures/innf.txt"
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EJ", [A65000], , , xlColumns, xlPrevious).Row
Range("A" & BeginRow & "" & EndRow).Copy
Workbooks.Add 1
Set wb = ActiveWorkbook
ActiveSheet.Paste
[A65000].End(xlUp).Offset(1).Select
Workbooks("innf.txt").Activate
BeginRow = [A:A].Find("EM", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
Range("A" & BeginRow & "" & EndRow).Copy
wb.Activate
ActiveSheet.Paste
ActiveWorkbook.SaveAs "c:\temp\test.xls"
ActiveWorkbook.Close
End Sub

--
Regards.
Daniel
"TerryM" <[email protected]> a écrit dans le message de
(e-mail address removed)...

Daniel,

Like I first stated I'm not exactly that good at programming syntax.
Where
the heck am I messing up?

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 "http://www.nymerc.com/futures/innf.txt" = 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 & "" & EndRow)
myRange.Copy
Workbooks.Add 1
ActiveSheet.Paste
ActiveWorkbook.SaveAs "c:\temp\test.xls"
ActiveWorkbook.Close
End Sub

I keep on getting Compile error:

With Object must be user-defined type, object, or variant.



:

Try :

Sub test()
Dim myRange1 As Range, BeginRow As Long, EndRow As Long
Dim myRange2 As Range
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EJ", [A65000], , , xlColumns, xlPrevious).Row
Set myRange1 = Range("A" & BeginRow & "" & EndRow)
BeginRow = [A:A].Find("EM", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
Set myRange2 = Range("A" & BeginRow & "" & EndRow)
Union(myRange1, myRange2).Select
End Sub
Regards.
Daniel
"TerryM" <[email protected]> a écrit dans le message de
(e-mail address removed)...
Daniel,

Here is a question, when I run the code/macro from earlier, it does
find
the
EJ and the EM. Unfortunately it also lists everything inbetween
them.
Is
there a way to rectify this? I'm still trying to figure out what
changes
I
need to make to your existing code to get stuff to work.

Terry

:

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" <[email protected]> a écrit dans le message
de
(e-mail address removed)...
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

:

"TerryM" <[email protected]> a écrit dans le
message
de
(e-mail address removed)...
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 & "" & 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

 

[TerryM]

Daniel.C,

I must be doing something severely wrong or i'm missing the obvious here.
When I put the syntax that you wrote with the other syntax I keep on getting
errors. It keeps on erroring out on this "BeginRow = [A:A].Find("EJ",
after:=[A1]).Row". When I run the code to find just the EJ and EM in macro
form it finds them. Is there any additional code that I can input so I can
export the named ranges to another spot or to MS Access?

I can get bits and pieces to work but not all at once. Any assistance would
be greatly welcomed.

Up to now, myRange1 and myRange2 are no named ranges but variables. The
following code creates named ranges :

Sub test()
Workbooks.OpenText "http://www.nymerc.com/futures/innf.txt"
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EJ", [A65000], , , xlColumns, xlPrevious).Row
ActiveWorkbook.Names.Add "myRange1", Range("A" & BeginRow & "" &
EndRow).Address
BeginRow = [A:A].Find("EM", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
ActiveWorkbook.Names.Add "myRange2", Range("A" & BeginRow & "" &
EndRow).Address
End Sub

--
Regards.
Daniel

Daniel,

Here's a question. With using the named ranges "myrange1","myRange2" in
vba
code is there anyway I can use the named ranges to export out of Excel.
How
I have the other exports configured is Access looks for a specific named
range on the spread sheet and imports that data. The code that you wrote
does exactly what I was needing, now I just have to figure out how to get
he
information to export.

Sorry to have been so long to understand. The following macro should do
the
work. If it's the case, i'll add comments for better understanding :

Sub test1()
Dim wb As Workbook, BeginRow As Long, EndRow As Long
Dim myFile As String

Workbooks.OpenText "http://www.nymerc.com/futures/innf.txt"
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EJ", [A65000], , , xlColumns, xlPrevious).Row
Range("A" & BeginRow & "" & EndRow).Copy
Workbooks.Add 1
Set wb = ActiveWorkbook
ActiveSheet.Paste
[A65000].End(xlUp).Offset(1).Select
Workbooks("innf.txt").Activate
BeginRow = [A:A].Find("EM", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
Range("A" & BeginRow & "" & EndRow).Copy
wb.Activate
ActiveSheet.Paste
ActiveWorkbook.SaveAs "c:\temp\test.xls"
ActiveWorkbook.Close
End Sub

--
Regards.
Daniel
"TerryM" <[email protected]> a écrit dans le message de
(e-mail address removed)...
Daniel,

Like I first stated I'm not exactly that good at programming syntax.
Where
the heck am I messing up?

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 "http://www.nymerc.com/futures/innf.txt" = 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 & "" & EndRow)
myRange.Copy
Workbooks.Add 1
ActiveSheet.Paste
ActiveWorkbook.SaveAs "c:\temp\test.xls"
ActiveWorkbook.Close
End Sub

I keep on getting Compile error:

With Object must be user-defined type, object, or variant.



:

Try :

Sub test()
Dim myRange1 As Range, BeginRow As Long, EndRow As Long
Dim myRange2 As Range
BeginRow = [A:A].Find("EJ", after:=[A1]).Row
EndRow = [A:A].Find("EJ", [A65000], , , xlColumns, xlPrevious).Row
Set myRange1 = Range("A" & BeginRow & "" & EndRow)
BeginRow = [A:A].Find("EM", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
Set myRange2 = Range("A" & BeginRow & "" & EndRow)
Union(myRange1, myRange2).Select
End Sub
Regards.
Daniel
"TerryM" <[email protected]> a écrit dans le message de
(e-mail address removed)...
Daniel,

Here is a question, when I run the code/macro from earlier, it does
find
the
EJ and the EM. Unfortunately it also lists everything inbetween
them.
Is
there a way to rectify this? I'm still trying to figure out what
changes
I
need to make to your existing code to get stuff to work.

Terry

:

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" <[email protected]> a écrit dans le message
de
(e-mail address removed)...
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

:

"TerryM" <[email protected]> a écrit dans le
message
de
(e-mail address removed)...
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 & "" & 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