How do i append new columns or rows into existing Excel file?

Dear all

i have a large excel file (3000 x 7000), i need to append new column or row in this file, so when i load this file into matlab then re-write the files again its takes time.. so is there any function can append column or row into target file with out load it .. in such a way if the exist file data are start from column A then after append new column .. exist data are start from column B ..... thanks

Answer 1

[dpb]

TMW has only supplied the xlsread/write pair as packaged high-level functions interacting with Excel. If you know the size of the worksheet you can write the new column to the proper address without reading the spreadsheet first.

If you don't know that a priori, I'm sure there's an Excel method to find the last used column in Excel, but Excel syntax/methods/properties aren't a Matlab question but one of Excel. The answer to how to interact via COM/ActiveX with Excel is in the MS Excel documentation or record a macro that does what you want and translate it to the necessary ActiveX instructions thru the ActiveX server connection.

However it's done, it won't be particularly fast although can save the read/write cycle of opening/closing the connection twice. But, fundamentally this really isn't a Matlab question.

[ahmed obaid]

thanks for your explanation

[dpb]

"I know (almost) nuthink!" to quote Sgt Schultz about Excel but I tried recording two macros wherein I moved the selection to upper LH corner, then used Ctrl-rtArrow to move to last column used, and then rtArrow to the next column. I did this first in normal (absolute) mode and then in relative. The instructions recorded were

Absolute mode
  Range("A1").Select
  Selection.End(xlToRight).Select
  Range("D1").Select

Relative mode
  Range("A1").Select
  Selection.End(xlToRight).Select
  ActiveCell.Offset(0, 1).Range("A1").Select

So, from that one presumes you move to A1 and select the cell, do the .End method with the named constant xlToRight to get to the last used column, row 1.

Then, in the absolute mode, the cell reference to D1 is hardcoded; not what you want as that requires keeping track of the columns used, but in relative mode, the reference seems to start over with A1 from a new origin as the Offset(0,1) from the previous ActiveCell. That's guessing, but that'd be a start on how to try to code up something I'd guess...

I searched the online doc's for the Excel xlXXX constants but never could find them in the installed doc here--but that's pretty consistent with every time I've ever tried to use any of this stuff; the documentation is so convoluted and obtuse that to figure out how any of it works would, in essence, require making that a full time job. There are easier ways to accomplish things than that so have never spent the time required to get any further, sorry.

Maybe you can find an Excel group and they can probably give you the req'd instructions quickly if not immediately.