Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA Code to return Multiple Rows?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA Code to return Multiple Rows?
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA Code to return Multiple Rows?
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA Code to return Multiple Rows?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA Code to return Multiple Rows?
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA Code to return Multiple Rows?
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA Code to return Multiple Rows?
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA Code to return Multiple Rows?
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA Code to return Multiple Rows?
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA Code to return Multiple Rows?
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA Code to return Multiple Rows?
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 & ":D" & 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 & ":D" & EndRow).Address End Sub -- Regards. Daniel "TerryM" a écrit dans le message de news: ... 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro or VBA Code to return Multiple Rows?
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. "Daniel.C" wrote: 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 & ":D" & 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 & ":D" & EndRow).Address End Sub -- Regards. Daniel "TerryM" a écrit dans le message de news: ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return multiple rows of data based on criteria | Excel Worksheet Functions | |||
Search Columns and return multiple rows. | Excel Programming | |||
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) | |||
VLookUp function to return multiple rows | Excel Worksheet Functions | |||
VLOOKUP to return multiple rows | Excel Worksheet Functions |