Unable to open file as a workbook excel 97

I am attempting to import a list of .xls files and combine them. The issue is that the files were created in Excel 97-2003 and I have MATLAB version 2020b. Because of this, I get the following error:

Error using readtable (line 245)
Unable to open file '...12a.xls' as a
workbook. Check that the file exists, read access is available, and the file is a
valid spreadsheet file.

If I convert to a .txt file, the code can open the file and read it. The issue is when I convert from .xls to .txt, the .txt file combines all of the rows into one cell rendering the .txt file unusable. I believe this is caused because of the format of the .xls file.

I am curious if it is possible to read only certain rows and columns of the .xls file when converting to a .txt file to get rid of this issue or even to bypass needing the .txt file and somehow get MATLAB to read the .xls file properly.

For a better understanding, please run the following code on the attached files to see what I am speaking on (Look for the data values. The table is made properly on the first set of data. But on the second, things get weird. I want to only select the values listed out in the first data file and leave all the rest out as it is not important. Keep in mind that I have placed a bunch of 5's for confidentiality reasons, so disregard all of that.):

% Specify the desired path for the folder you want to operate on. You can
% simply copy and paste the directory from your file manager. Make sure to
% always add the '\' after the directory so the rest of the code knows
% where to start.
fileDir = '\';

% Import the raw data from the files in the directory
path_info = fullfile(fileDir, '*.xls');
files_temp = dir(path_info);

% Loop through each .out file, copy it and give new extension: .txt
for i = 1:numel(files_temp)
    file = fullfile(fileDir, files_temp(i).name);
    [tempDir, tempFile] = fileparts(file); 
    status = copyfile(file, fullfile(tempDir, [tempFile, '.txt']));
end

% Select all .txt files
path_info = fullfile(fileDir, '*.txt');
files = dir(path_info);

% Initialize parameters for the loop.
tables = [];

for i = 1:length(files)
        
        % Read in the information from each data file and combine back into
        % a single output file.
        tables = readtable(files(i).name, 'VariableNamingRule', 'preserve');
        tables
end

[Mario Malic]

What happens if you open old Excel file and save it as new version, will it be readable by readtable? If yes, you can open the files with Excel through actxserver, and save them with newer format.

[Steven Manz]

As an update to my previous response, yes, that does work, but how can I use actXserver to automate that process? I know nothing about this library.

[Mario Malic]

Here's an example of opening the file and saving it.

hExcel = actxserver('Excel.Application');
% hExcel.Visible = 1;
Workbooks = hExcel.Workbooks;
Workbook = Workbooks.Open("V22pw12a.xls");
Workbook.SaveAs('newfile.xlsx', 51); % It looks like format needs to be specified, without it, it won't be opened
Workbooks.Close;
% and so on

% Clean up
invoke(hExcel,'Quit');
delete(hExcel);
clear hExcel;

See here for the arguments in SaveAs methid, it allows you to specify the different formats for the file to be saved in.

[Steven Manz]

Thank you for the edit. I did see that myself as well. I am currently debugging to make sure everything will save properly.

[Steven Manz]

Will my body code need to come before "invoke(hExcel,'Quit');" or can it all go after hExcel has been closed? Does this code save the xlsx files to my directory so I can use them after the actXserver is closed?

[Steven Manz]

As an update, it does not seem like the files are saving properly. When I run your code, I get this error:

Which is not true since the one that is saved is a .xls file. If I hit yes, MATLAB gives:

Error: The object invoked has disconnected from its clients.

And I have to close Excel manually. Afterward, nothing new is in my directory and the same issue is occuring with the.xls file.

[Steven Manz]

If it helps, here is what I have:

fileDir = '....\';
path_info = fullfile(fileDir, 'V22pw13a.xls');
Wkbk = dir(path_info);

hExcel = actxserver('Excel.Application');
hExcel.Visible = 1;
for i=1:length(Wkbk)
   Workbooks = hExcel.Workbooks;
   files = hExcel.Workbooks.Open(fullfile(fileDir,Wkbk(i).name)); % Opens Excel file
%    Wkbk = Wkbk(38:48,1:10);
   files.SaveAs([extractBefore(Wkbk(i).name,'.') '.xlsx',51]); 
   files.Close;
end
invoke(hExcel,'Quit');
delete(hExcel);
clear hExcel;

[Mario Malic]

I was editing to make sure it works as I haven't used Excel with actxserver before.

The error you mentioned occurs when you give command to the Excel window that is closed. If you close the instance of Excel that MATLAB has created, the one that you see when you set Visible property to on, you'll get that error.

files.SaveAs([extractBefore(Wkbk(i).name,'.') '.xlsx'],51);
%                                                    ^ the bracket was on incorrect spot

It is always better to use full path to the file when using SaveAs. You can use fileparts instead of extractBefore.

[Steven Manz]

Thank you for your help Mario. Even while in actXserver it just seems that MATLAb struggles to change the .xls file to .xlsx. I tried saving the file using the number 56 (which is an EXcel 97-2003 Excel Workbook), and it then created the same issue I ran into with saving as a .txt. At least in that case, it did save the information. However, when trying to save as a .xlsx (51), no luck. I am curious if MATLAB has any documentation on this at all. It may just nopt be possible.

[Mario Malic]

Save format 51 works fine, the issue is in save command. If you don't supply fullpath to it, it'll be saved in C:\Users\<username>\Documents

Your documents folder isn't on MATLAB path so it can't search for files there.

t = readtable('V22pw12a.xlsx');
>> head(t)
ans =
  8×10 table
        VB        IB     VG        IG           VD       ID    VSENSE    RSENSE      RAVE          I1    
    __________    __    ____    _________    ________    __    ______    ______    _________    _________
    {'-1.8'  }    0     -1.8    -0.000114    -0.00048    0       -1.8    15800     {'20000'}    {'5e-05'}
    {'-1.4'  }    0     -1.4    -8.05e-05    -0.00034    0       -1.4    17400     {'*'    }    {'*'    }
% posting only two lines, will remove this after your reply

It's not the MATLAB who has the documentation on actxserver, it's the Microsoft. Use the Google to find the documentation, keywords: OLE Automation, activex . I am unable to find exact document with references.

Here are some links, but I am not super familiar with their documentation.

https://docs.microsoft.com/en-us/office/vba/library-reference/concepts/ole-programmatic-identifiers-office#microsoft-excel

https://docs.microsoft.com/en-us/office/vba/api/overview/excel

[Steven Manz]

Hey Mario. Is it possible for you to direct message the code you used? I am not sure why, but I just cannot get this to output properly.

[Steven Manz]

By the way, I did also use fileparts. You are right, it is much easier to use!

[Mario Malic]

Here it is, not in its automated form, but you can convert to it easily.

hExcel = actxserver('Excel.Application');
Workbooks = hExcel.Workbooks;
Workbook = Workbooks.Open("C:\Users\Mario\Downloads\V22pw11a.xls");
Workbook.SaveAs("C:\Users\Mario\Downloads\V22pw12a.xlsx", 51); % It looks like format needs to be specified, without it, it won't be opened
Workbooks.Close;
t = readtable("C:\Users\Mario\Downloads\V22pw12a.xlsx");

t =

  10×2 table

      VB      IB
    ______    __

      -1.8    0 
      -1.4    0 

[Steven Manz]

Ok. Well I dont know if this is just a user error at this point, but here is the error I get:

Error using readtable (line 245)
Unable to find or open 'V22pw14a.xlsx'. Check the path and filename or file
permissions.

Here is my code:

fileDir = 'C:\Users\smanz\Documents\Pwell_02_13_2021\';


% Select all .txt files
path_info = fullfile(fileDir, 'V22pw14a.xls');
files = dir(path_info);


hExcel = actxserver('Excel.Application');
Workbooks = hExcel.Workbooks;
Workbook = Workbooks.Open(files(1).name);
[~,name,~] = fileparts(files(1).name);
wkbk = [name '.xlsx'];
Workbook.SaveAs(wkbk, 51); % It looks like format needs to be specified, without it, it won't be opened
Workbooks.Close;
t = readtable(wkbk);

When I run the code, see attached for the popup that I get.

Have you ever had this happen? I think it is the source for why I cannot seem to get this all to work.

[Mario Malic]

wkbk = [name '.xlsx']; % the output of this is below
wkbk = 'V22pw14a.xlsx'

This is the error, wkbk is not the full path to the file.

If you don't provide fullpath to the file you want to save, your file will be saved in C:\Users\<username>\Documents as mentioned in the comment above. If you go there, you'll see your file and if you use readtable on it, it'll work properly.

Better alternative for constructing the full path to files

[filepath, name, ext] = fileparts(files(1).name);
fullFilePath = fullfile(filepath, strcat(name, ext));
wkbk = 'C:\...\...\V22pw14a.xlsx' % this is how wkbk should look like

[Steven Manz]

Ok it works! Thank you so much! Now I just have to figure out how to turn all data in the table into a double because it is currently not as trivial as I would have expected.

Answer 1

[Mario Malic]

Moving conversation here as an answer.

For the reference, read the comment section.

hExcel = actxserver('Excel.Application');
Workbooks = hExcel.Workbooks;
Workbook = Workbooks.Open("C:\Users\<username>\Downloads\V22pw11a.xls");
Workbook.SaveAs("C:\Users\<username>\Downloads\V22pw12a.xlsx", 51); % It looks like format needs to be specified, without it, it won't be opened
Workbooks.Close;

t = readtable("C:\Users\<username>\Downloads\V22pw12a.xlsx");
t =
  53×10 table
% not showing the table contents

To convert cell array to numeric array (if possible), you can use cellfun and str2num like shown below, keep in mind, to get the content of the table you index using curly brackets.

t{1,1}; 
  1×1 cell array
    {'-1.8'}

To get the content of cell array, index further into cell, use curly brackets again

t{1,1}{1}
ans =
    '-1.8' % this is a char array

This is an example to get the numeric array from table

cellfun(@str2num, t{1:10,1}); 

However, I'd suggest you to read the documentation on readtable, it is probably possible to tweak its settings to read your file more easily.

[Steven Manz]

I actually just got it to work right before this answer. I really appreciate your help!

[Mario Malic]

Happy to help!

Answer 2

[Jeremy Hughes]

I tried opening one of your files in a hex editor, and it's not an excel file. It's a text file.

What clued me in was a warning when I tried to open the file with Excel.

How were these created?

BTW, this works:

T = readtable('V22pw12a.xls','FileType','text')

[Steven Manz]

They were created using measurement software in a lab. It runs a very old version of Windows, and thus has Excel 97-2003. When the files are transferred to my laptop, there is obviously some translation issues. When I copy all the data into an updated version of Excel, things are a whole lot easier. However, doing this to a ton of data files just is not ideal. I am simply hoping to automate changing to filetype to something more reasonable.

[Steven Manz]

Sorry Jeremy for the late reply. I tried what you did and I still have the same issue. Because the text file only recognizes 4 columns for some reason, all information longer than that is meshed into 1 cell and thus unreadable. I want to bypass this issue and extract data from a specific spot in the .xls file. But MATLAB cannot open the .xls file like an updated .xlsx, and I am left changing it to that somehow. I guess the only way is through actXserver. But I am trying to figure that all out.

[Jeremy Hughes]

Open the file in a text editor and you can see it's actually a tab delimited text file--the tool generating it is just labeling it .xls, but that's not what it is. I think the reason activeX is working is because that just opens Excel in the background and Excel is transforming the file. You could do this with readcell/writecell pretty easily.

C = readcell('V22pw12a.xls','FileType','text','Delimiter','\t')
writecell(C,'V22pw12a.xlsx')

But you don't really need to you. These read functions accept range for textfiles as well.

A1 = readmatrix('V22pw12a.xls','FileType','text','Delimiter','\t','Range','A39:J48')
A2 = readmatrix('V22pw12a.xls','FileType','text','Delimiter','\t','Range','A85:J89')