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

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');

Answer 1

[Image Analyst]

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');	

[dpb]

If I copy over an .xltm workbook to a new file with a .xlsm filename, then can't open the new file -- Excel complains it isn't the proper type.

Only way I've found that works is to open the template file and do a "SaveAs" on it to have Excel make the internal changes.

Maybe it'll work for non macro-enabled, I don't know; I've only had occasion to need to make the translation programmatically for macro-enabled workbooks.

I suppose one could work around it by not actually using a template but an archival copy of the workbook itself in the desired format.

[Image Analyst]

Your last sentence said it. I don't use an actual "template" format workbook. I use just a regular workbook with a .xlsx extension. So I usually write out my data to a blank, new workbook, then open it in Excel and set up all the formatting, like cell color shading, borders, number of decimal points, font size, etc. Once I get it looking the way I want in Excel, I delete all the numerical data in it (so I just have formatted cells, perhaps with some header words in there) and save it as "Excel Results Template.xlsx", then close Excel. Now it's just a normal workbook and can be copied over any time you need to make a new workbook with new results.

[dpb]

The confusion naming creates... :) -- as what, in particular, is a poster referring to when uses "table"?

"template" has a specific MS-given definition when used in conjunction with "file" whereas the dictionary definition is something different; the pattern of something followed.

Well, OP now has code for either, depending upon his actual question/usage...

[dpb]

"I don't use an actual "template" format workbook. I use just a regular workbook..."

I considered having the template workbook for the compiled app a little more robust solution in that it would be less likely for the users of the app to manually mung on it whereas they might do so on a normal workbook file.

[Fangjun Jiang]

I read the OP's question again. The new file name is created in the script. The user then manually opens the Excel template file in File Explorer, "save as" the new file name and the rest of the script can proceed.

So I think copyfile() is the needed answer. No ActiveX or GUI app involved.

[Image Analyst]

@Fangjun Jiang I agreed. My code above does just that. It creates both the template name and new output file name and the uses copyfile -- no GUI or ActiveX is needed.

@dpb I've never had a problem with my users going into the program folder and messing around with the template file. Your users may be more adventurous than mine though.

[dpb]

@Image Analyst Never underestimate sheer luck/ignorance...

I also stored the template file on a shared network file rather than distributing it with the compiled app -- this keeps only one master copy in a single location. Being as how all the potential users of the app do have access to this drive, it is possible for them to be poking around and stumble on to it by accident even if not looking for it. I minimize that possibility by putting it in a location that doesn't hold normal data files and giving it a name not like anything routinely used, but still, it is possible.

[dpb]

Just a background note for context...

In this app, given that all the fancy formatting is dynamic other than the initial column headers and it writes formulas as well as data, the use of a little ActiveX in creating a new working file from the Excel template file is a trivial piece of additional use of ActiveX.

If it were the only need throughout, I'd undoubtedly have reverted to the ploy of the archival .xlsm copy and done other things to protect it from possible end-user changes. But, there's so much elsewhere as well, just this added bit seemed totally inconsequential and it seems quite reliable.

I found during the development that the need to build these sheets piecemeal and address invidual cells with updated compound data that writecell called in a tight loop was/is far too slow and will, in fact, eventually crash MATLAB if tried in too tight/large a loop. At the present time, the app uses the <FEX xlswrite1 solution> that uses the original xlswrite but keeps the ActiveX connection alive between writes -- munging on that version a little more, I also exposed the ActiveX object handle to the workbook and so can then do the other formatting and needed operations directly before closing the file and deleting the ActiveX connection.

It's a pretty convoluted thingie at the moment; I've looked at the possibility of perhaps rewriting the whole thing from scratch with the .NET object model, but that's going to have to wait for a lull...

[Jack Smith]

Hi Image Analyst,

Your original answer worked for me with some tweaks. I didn't quite understand the setup before the for loop but I believe the end goal is to have the full folder paths for both the template and output files.

These were already within my script so I replaced everything before the loop with existing code and kept the loop as is to copy the template file to the output file.

It worked well, thanks for the answer.

[Image Analyst]

There is no for loop in my code.

But the first few lines are merely to get the name of the file for your data, and the name of the file for your template. If you want to set those up some other way, that's fine, though I do recommend you still use the fullfile() function to construct the full file name (folder + base file name + extension).

[Jack Smith]

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]

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.

Answer 2

[Fangjun Jiang]

copyfile()

[dpb]

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]

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.

Answer 3

[dpb]

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...

[dpb]

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... :)