Importing CSV file into Excel 2007 and then manipulating it

[stuck]

I have a macro that runs fine in Excel 2000 but fails in 2007.

The problem is that when the line:

Code: Select all

Workbooks.OpenText Filename:=fCSV, _

executes in XL 2007 the workbook created has more than 256 columns. This means that later on in the macro when the imported file is copied to an existing (.xls) workbook it stops because the destination workbook can't swallow a worksheet with over 256 columns.

Is there a tweak to the line that will make XL 2007 open a .xls/256 column workbook?

Ken

[HansV]

Do you have any idea why there are more than 256 columns?

[rory]

Why not only copy the 256 columns?

[stuck]

The reason I think there are more than 256 columns in the imported workbook is because of the error message the XL 2007 throws up later on in the macro, see attached, and the line it fails on is the one that copies the imported stuff to another workbook:

Code: Select all

Sheets(Array("participants_raw", "reported_results_raw")).Copy Before:=wsStats.Sheets(1)

Ken

[HansV]

Try saving as an Excel 97-2003 workbook before copying the sheets into the existing workbook.

[stuck]

...and then deleting it just before the macro finishes, since I don't want/need the intermediate imported file.

Something to try at least.

Thank you,

Ken