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.

"Daniel.C" wrote:

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 & ":D" & 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 & ":D" & EndRow).Copy
wb.Activate
ActiveSheet.Paste
ActiveWorkbook.SaveAs "c:\temp\test.xls"
ActiveWorkbook.Close
End Sub

--
Regards.
Daniel
"TerryM" a écrit dans le message de news:
...
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 & ":D" & 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.



"Daniel.C" wrote:

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 & ":D" & EndRow)
BeginRow = [A:A].Find("EM", after:=[A1]).Row
EndRow = [A:A].Find("EM", [A65000], , , xlColumns, xlPrevious).Row
Set myRange2 = Range("A" & BeginRow & ":D" & EndRow)
Union(myRange1, myRange2).Select
End Sub
Regards.
Daniel
"TerryM" a écrit dans le message de
news:
...
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

"Daniel.C" wrote:

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