Read and write multiple excel files using ActiveX

I have 2 folders each containing 4 excel files. I need to read data from 1st excel file from each folder and write those data into the third excel file.Then I need to do arithmetic calculation between those two copied data and write result in 3rd excel file stored in folder 3. This process I want to do for all the files in the two folders. Is it possible using ActiveX server?

[Geoff Hayes]

Which part of the problem do you want to use ActiveX server for? What have you tried so far? See MATLAB, ActiveX, and Excel for details.

[Geoff Hayes]

The link I provided in my comment should get you started. I have to ask - why do you wish to use ActiveX server over the above code?

I could be mistaken, but (for example) lines 168-171 of xlswrite (R2014a) are

 %---------------------------------------------------------------
 % Attempt to start Excel as ActiveX server.
 try
    Excel = actxserver('Excel.Application');
 % etc.

So it is attempting to start Excel as an ActiveX server. Why do you want duplicate what this function is doing for you already? You will see a similar line of code in xlsread and in xlsfont (if this latter is from the MATLAB File Exchange).

[KRUNAL]

The above code is opening and closing excel each time functions like xlswrite and xlsfont runs in the loop (if you will see their code in matlab file exchange). This makes the code slow when used for files upto 20-30. So I want to use activex

Answer 1

[Geoff Hayes]

Krunal - this was taken from the link I already provided and so will get you started

 try
    numSheets = 5;

    % first open an excelActXSrvr Server
    excelActXSrvr = actxserver('Excel.Application');

    % set as visible
    set(excelActXSrvr, 'Visible', 1);

    % insert a new workbook
    excelWorkbooks = excelActXSrvr.Workbooks;
    excelWorkbook  = invoke(excelWorkbooks, 'Add');

    % get the sheets for this workbook
    excelSheets = excelActXSrvr.ActiveWorkBook.Sheets;

    % ensure that we have enough sheets
    if excelSheets.Count < numSheets
        sheetLast = get(excelSheets, 'Item', excelSheets.Count);
        invoke(sheetLast, 'Activate');
        invoke(excelSheets,'Add',[],sheetLast,numSheets-excelSheets.Count);
    end

    % for each sheet do
    for k=1:numSheets

        % make the kth sheet active
        sheetk = get(excelSheets, 'Item', k);
        invoke(sheetk, 'Activate');

        % write the header data to the A1:D1 fields
        data = {'BHS_P_SE' 'BHS_S_SE' 'BHS_P_LB' 'BHS_S_LB'};
        activeSheetRange = get(sheetk, 'Range', 'A1:D1');
        set(activeSheetRange, 'Value', data);
    end

    % now save the workbook
    invoke(excelWorkbook, 'SaveAs', 'myfile.xlsx');

    % quit excelActXSrvr
    invoke(excelActXSrvr, 'Quit');

    % end process
    delete(excelActXSrvr);

 catch exception
    fprintf('Error: %s:%s\n',exception.identifier,exception.message);
 end

It updates five worksheets with the specified column headers for columns A1 through D1. Using the above as a template, you should be able to complete the updates to your code and minimize the number of xlswrites. Once that has been completed, then I suggest you do something similar for the xlsread and xlsfont.

[KRUNAL]

ok..and where should I add the calculation part and how?

[Image Analyst]

I usually do all my calculations early in the code. I save everything into arrays, and then once all the functions have been called, computations made, and arrays have their values, then I do all my Excel writing stuff after all that.