Is it possible to open an excel template, save as a new file then write data to the new file without overwriting the template?

10 views (last 30 days)
I have an excel template that I need to populate with data from a MATLAB script. I am able to automatically create the new file name within the script but I need to manually copy the template and create the new file within the file explorer before running the script to populate with the output data.
Is it possible to open the template file and save it as a new file with the 'New_File' variable name below all within MATLAB?
Once the file is created, I can use the existing script to write data. I just want to remove the manual creation step for the new file.
New_File = append(Test,'.xlsx');
Template = append('Template','.xlsx');

Accepted Answer

Image Analyst
Image Analyst on 30 Jan 2023
Of course. I do this all the time. I have a template workbook with all the formatting just how I like it, then when it comes time to write out my measurements, I call copyfile and then writecell or whatever function you want. Here is a well commented snippet from one of my programs:
% Get filename name of workbook from our image folder:
[folder, baseFileNameNoExtension, extension] = fileparts(handles.imageFolder);
% Create the name of the output workbook that will hold our results.
excelBaseFileName = sprintf('Results for %s.xlsx', baseFileNameNoExtension);
% Replace "handles.imageFolder" with the folder where you want your output file to be actually located.
excelFullFileName = fullfile(handles.imageFolder, excelBaseFileName);
workSheetName = 'Results';
% See if a template workbook exists. Replace "handles.programFolder" with
% the folder where your template is actually located.
templateFileName = fullfile(handles.programFolder, 'Excel Results Template.xlsx');
if isfile(templateFileName)
% Template exists so we can copy it to the output folder.
if isfile(excelFullFileName)
% Results workbook already exists. Ask if they want to replace it.
userPrompt = sprintf('The results workbook already exists:\n%s\n\nDo you want to replace it', excelFullFileName);
reply = questdlg(userPrompt, 'Replace workbook?', 'Replace', 'Quit', 'Replace');
% reply = '' for Upper right X, otherwise it's the exact wording.
if contains(reply, 'Replace', 'IgnoreCase', true)
% Send existing file to recycle bin.
recycle on
delete(excelFullFileName);
% Copy template to folder where the output workbook should go.
copyfile(templateFileName, excelFullFileName);
else
return; % They chose Quit so leave this function.
end
end
else
fprintf('Excel results template file not found:\n%s', templateFileName);
end
% Now write the output. Note: This will work regardless of whether the template exists or not.
xlswrite(excelFullFileName, data, workSheetName, 'A1');
  12 Comments
dpb
dpb on 6 Feb 2023
I wish it were extended to do more than just append directories; it still takes specific string manipulations to deal with the extension onto a string of paths; it's part of the process but the toolkit was left somewhat incomplete.

Sign in to comment.

More Answers (2)

Fangjun Jiang
Fangjun Jiang on 30 Jan 2023
copyfile()
  2 Comments
Fangjun Jiang
Fangjun Jiang on 30 Jan 2023
Good point. That could be the case. In that case, I would suggest doing this in Excel: File, New, and then Save a "template .xlsx file" with fixed name. Then in the script, use copyfile() to make a copy.

Sign in to comment.


dpb
dpb on 30 Jan 2023
Edited: dpb on 30 Jan 2023
The routine I use to do so follows...
function newYearlyBillingWorkbook(fnTemplate,outputFile,outputSheet)
% open COM server, get workbook, worksheet object handles
excel=actxserver('Excel.Application');
wbk=excel.Workbooks.Open(fnTemplate);
excel.Worksheets.Item("Found").Name = outputSheet;
FileName=outputFile;
FileFormat=XlFileFormat.xlOpenXMLWorkbookMacroEnabled;
CreateBackup=false;
ConflictResolution=XlSave.xlLocalSessionChanges;
excel.Range('A1').Select; % leave A1 selected for user convenience
excel.Application.DisplayAlerts=false;
excel.ActiveWorkbook.SaveAs(FileName,FileFormat,[],[],[],CreateBackup,[],ConflictResolution)
excel.Application.DisplayAlerts=true;
excel.ActiveWorkbook.Close
% cleanup when done
excel.Quit
delete(excel);
end
To so so requires opening the template file, making any adjustments needed to it ("the default sheet name "FOUND" in the template file here is changed to the new user-requested one) then doing a "SaveAs" with the new file name, then closing the newly-created file. "SaveAs" automagically closes the template file and breaks the connection of the COM engine to it; the code structure that calls this routine then reopens the new file to carry on so the new file is then closed and the ActiveX server object closed/deleted to not leave Excel processes hanging.
I created a set of MATLAB classes that hold the Excel constants by the MS documentation name (the XlSave and XlFileFormat references); you can find those from the <MS Excel doc>; the local implementation turned those into MATLAB classes; unfortunately, I've not found any such global tool...but they translate something like
>> type XlSave.m
classdef XlSave
properties (Constant)
xlLocalSessionChanges = 2
xlOtherSessionChanges = 3
xlUserResolution = 1
end
end
which then actually the MATLAB editor will find intellisense of...it's not mandatory to use these, of course, but does add something to the code legibility. This choice for implementation ends up with the "classname.constant" style rather than a pure constant, but the MATLAB and MS enumeration implementations are not compatible to try to use them.
NOTA BENE: The app here requires the output file be macro-enabled and the template contains the VBA module; hence the naming conventions/file type. Salt to suit your particular application...
  1 Comment
dpb
dpb on 30 Jan 2023
Oh. As can be seen from the function name, the above routine is/was written for a specific application; rename it more generically or specifically for your use... :)

Sign in to comment.

Products


Release

R2022a

Community Treasure Hunt

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

Start Hunting!