Clear Filters
Clear Filters

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

31 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
% Copy template to folder where the output workbook should go.
copyfile(templateFileName, excelFullFileName);
return; % They chose Quit so leave this function.
fprintf('Excel results template file not found:\n%s', templateFileName);
% Now write the output. Note: This will work regardless of whether the template exists or not.
xlswrite(excelFullFileName, data, workSheetName, 'A1');
Jack Smith
Jack Smith on 6 Feb 2023
Sorry, I meant if statement instead of for loop. I am still using the fullfile() function, was unaware this existed and has made my life much easier!
Thanks again.
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
dpb on 30 Jan 2023
This solution works only if the "template" is a normal workbook file of the type desired; it won't work if the template is actually a MS Excel template file.
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 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.Worksheets.Item("Found").Name = outputSheet;
excel.Range('A1').Select; % leave A1 selected for user convenience
% cleanup when done
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
which then actually the MATLAB editor will find intellisense'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 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.




Community Treasure Hunt

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

Start Hunting!