How to find the first empty row of a .xlsx-file?

I have a script in which I use writetable() to write data to a row in an excel spreadsheet. I need to get the row number of the next empty row in the file, but I haven't found a solution on this site.

Kind regards

Mikkel

[dpb]

That's an Excel Q? more than Matlab; one syntax that works as macro is

unusedRow = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row

Use COM to implement via ML.

Alternatively, read the data and find the size of the array and use that or keep a running counter and define a location in the spreadsheet that stores the information so can just retrieve it.

There is no magic query built into the ML interface, however.

Answer 1

[Guillaume]

So, in matlab, the unusedRow = Cells.SpecialCells(xlCellTypeLastCell) that dpb mentions, would be implemented as:

function lastrow = findLastRow(excelfile, sheetname)
    %find last used row of an exel workbook
    %excelfile: FULL PATH of excel workbook. Required, char vector or scalar string
    %sheetname: sheet name or number. optional (default is 1st sheet). char vector, scalar string or scalar numeric
    %TODO: add input validation
    if nargin < 2
        sheetname = 1;
    end
    excel = actxserver('Excel.Application');    %start excel
    cleanup = onCleanup(@() excel.Quit);        %make sure to close excel even if an error occurs. Will also close the workbook if it is open since it never gets modified
    workbook = excel.Workbooks.Open(excelfile); %open workbook
    worksheet = workbook.Worksheets.Item(sheetname);   %get worksheet
    lastrow = worksheet.Cells.SpecialCells('xlCellTypeLastCell').Row;  %get last used row
    workbook.Close;
end

Code untested. If there are bugs in the code, blame me. If Cells.SpecialCells(xlCellTypeLastCell) doesn't work, blame dpb.

[dpb]

Iff'en Cells.SpecialCells(xlCellTypeLastCell) doesn't work, blame Stack Overflow! -- dpb :)

https://stackoverflow.com/questions/12497804/finding-first-blank-row-then-writing-to-it

[Mikkel Eskildsen]

So, when I run this after defining 'excelfile' and 'sheetname', The function just returns those two variables. How do I get it to show the lastrow?

[Guillaume]

Huh? The function doesn't and cannot return excelfile and sheetname. The function is defined with only one output: lastrow.

Perhaps you do not know how to use functions. Save the code in a file called findlastrow.m. Then you can use the function like any other function (such as writeable) with:

lastrow = findlastrow('C:\somewhere\somefile', 1);

[Mikkel Eskildsen]

I see, I didn't use the "lastrow =" which is why I got the previous result. Now it seems to have some problems with starting excel?

The following line:

lastrow = findLastRow('/Users/mfeskildsen/Desktop/Praktik/RFD_Matlab/rfd_skitse_kopi.xlsx',1);

produces following error:

Error using actxserver (line 93)
Server creation failed. Invalid ProgID 'Excel.Application'.

Error in findLastRow (line 10)
    excel = actxserver('Excel.Application');    %start excel

Error in rfd (line 168)
lastrow = findLastRow('/Users/mfeskildsen/Desktop/Praktik/RFD_Matlab/rfd_skitse_kopi.xlsx',1);

[Guillaume]

Looks like you haven't got excel installed (or not installed properly). The code requires excel to be installed.

[Mikkel Eskildsen]

Hmm, but I should have. Does this not work on mac?

I have Excel for mac, version 16.19 installed (Student license). If it should work, anything I can do to solve it?

[Guillaume]

I'm very surprised that actxserver doesn't check the OS and complain appropriately if it's not Windows.

Everything we've discussed so far is windows only. The required machinery (COM) to automate excel from other programs is only implemented on Windows. It is simply not possible to do the same on a Mac.

On a Mac, writetable, xlswrite and co. don't talk to excel in order to read the file. Mathworks have implemented their own parser. Unfortunately, to solve your problem you'd have to do the same: write your own parser for xlsx files (and a different one if you want to support the older xls files which use a completely different format). That's probably several weeks worth of effort.

[Mikkel Eskildsen]

Woah, bad luck. The intended use for my app is for windows though, so my only issue is that I'm currently unable to test the code. But thanks for all your help!

[Ajinkya Bankar]

Hello Sir, I am trying to use this function to find last row in excel file but I am getting error as shown in the screenshot. I do have A.xslx file in the same directory. Can you please help to find the error? Thank you.

[Guillaume]

You have to give the full path of the file. It's excel opening the file and excel doesn't know what matlab's current directory is.