Macro error with .CSV - too many rows

**rasonline** · 03-10-2011, 10:32 AM

Hi guys

I cannot seem to find a solution to my problem by using the search function.

I have written a Macro in xl2007 in a .xls workbook which copies a sheet from a .csv file and then runs further actions on that sheet. I cannot use .xlsx as there are other users who will use this workbook but don't have excel 2007.

When I first created the Macro workbook all the procedures worked fine, because althogh the Macro workbook was .xls, it hadn't been closed and re-opened. So it still had 1-million rows.

When I closed the .xls and re-opened, it now refuses to run the Macro as the error msg states that the .csv file has more rows.

I have only started with VB about 3-4 weeks ago. Am using xl2007. Not a computer programmer, am an accountant but am pretty good at using excel functions (sumproduct, match, index, sumifs, etc.)

How do I copy the the .csv sheet (1-mil rows) into my .xls worksheet? (65k rows)

Here is the code I'm using.

Please Login or Register  to view this content.

**MarvinP** · 03-10-2011, 10:48 AM

Hi rasonline,

Does http://office.microsoft.com/en-us/ex...010077823.aspx help?

Could it really be the number of rows is too big for 2003 excel?

**rasonline** · 03-10-2011, 11:01 AM

hi.

the .csv file is only 400 rows long.
in the meantime i'm experimenting with the following code

it's a lot of trial and error as I'm just going along with what I can pick up from internet searches and various forum threads :P Hope I don't sound to amateurish here, but I'm persistent so I'm sure I'll get there eventually.

Please Login or Register  to view this content.

I'm copying and pasting all the info from .csv into the .xls file.

**rasonline** · 03-10-2011, 11:10 AM

Originally Posted by MarvinP

Hi rasonline,

Does http://office.microsoft.com/en-us/ex...010077823.aspx help?

Could it really be the number of rows is too big for 2003 excel?

The .csv always seems to open with 1-million rows in excel 2007.

**MarvinP** · 03-10-2011, 11:24 AM

Have you tried opening the csv file using word or notepad? I wonder if there are really a million rows, most of blank data. Maybe all you need is an End Of File mark at the end of the data.

**Domski** · 03-10-2011, 11:51 AM

Originally Posted by rasonline

The .csv always seems to open with 1-million rows in excel 2007.

When you open a csv file in 2007 it will import it into a 2007 workbook with 1 million rows. As soon as you save it as a .xls file you will revert to working in compatibility mode with 65536 rows. To retain the higher row count you will have to save it as a .xlsx or .xlsm file.

Dom

**nimrod** · 03-10-2011, 12:22 PM

Please Login or Register  to view this content.

Workbooks.Open Filename:=FilesToOpen
In this line you are opening the csv file in excel 2007 ... in other words saving it to a sheet that has 1million lines

Sheets().Copy After:=ThisWorkbook.Sheets("Instr")
In this line of code you're attempting to move/copy the entire sheet (i.e. 1 million rows) to a excel 2003 wookbook that cannot contain that many rows.

SOLUTION:
Do not attempt to move the entire sheet , instead select the range of data and then only copy/paste the selected range of cells. ... If you could indicate what column , on the source, can be used to determine how many rows have data PLUS how many columns AND the name of the Target sheet , then someone here could help you with the few lines of code it would take to solve the problem. ( see example below)

Please Login or Register  to view this content.

**rasonline** · 03-10-2011, 03:43 PM

@Nimrod: thanks, solves the problem. I think at post #3 i started heading off on the right track. Your solution allows me to select on the text that I need to.

@MarvinP: the .csv file is supplied from external source. As Nimrod mentiond, it seems like Excel2007 opens the .csv by default with 1-million lines. So I'm now doing a copy paste into the target workbook.

Thanks again guys.