Read and write multiple excel files using ActiveX

20 views (last 30 days)
KRUNAL
KRUNAL on 5 Aug 2014
Commented: Image Analyst on 8 Aug 2014
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?
  3 Comments
KRUNAL
KRUNAL on 6 Aug 2014
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

Sign in to comment.

Answers (1)

Geoff Hayes
Geoff Hayes on 7 Aug 2014
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.
  2 Comments
Image Analyst
Image Analyst on 8 Aug 2014
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.

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!