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

50 vues (au cours des 30 derniers jours)
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');

Réponse acceptée

Image Analyst
Image Analyst le 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');
  12 commentaires
Jack Smith
Jack Smith le 6 Fév 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 le 6 Fév 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.

Connectez-vous pour commenter.

Plus de réponses (2)

Fangjun Jiang
Fangjun Jiang le 30 Jan 2023
  2 commentaires
dpb le 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 le 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.

Connectez-vous pour commenter.

dpb le 30 Jan 2023
Modifié(e) : dpb le 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 commentaire
dpb le 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... :)

Connectez-vous pour commenter.




Community Treasure Hunt

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

Start Hunting!

Translated by