Read same cell in multiple excel files

Hi, I apologize for being naive and new. I am trying to read the same cell in multiple excel files. I have figured out how to rename each file ending with a counter from *0001 to *5000. If I want to read cell A1 in each file, and copy and paste that to an existing spreadsheet into A1 to A5000, how would I accomplish this? I was able to name each spreadsheet with the counter but I do not know how to efficiently read and write. Activex seems the best way?

Thank you in advance, RO

Answer 1

[Image Analyst]

Yes. If you have lots of files ActiveX is the best way - it will be a lot faster, a lot . I attach an ActiveX demo using Excel. It shouldn't be too hard to adapt it.

[dpb]

...ActiveX [...] will be a lot faster, ...

Ayup, 'cepting I know so little of the VBA syntax it's so frustrating to try to write stuff that unless it's going to be used over and over I can just wait for the other way and still be way ahead overall...

[Image Analyst]

Granted, you can spend more time writing the ActiveX code than you save over just using xlswrite. You don't need to know any VBA exactly. You can record a macro in Excel. Just start recording a macro (while you're in Excel) and then do whatever things you need to do, then stop recording. Then edit the macro and you'll see the VBA script with VBA style of calling the ActiveX commands. While this doesn't transfer over directly (copy-and-paste) into your MATLAB code, it does show you what ActiveX method got called. So then you can call the same methods in your MATLAB function using the MATLAB-style syntax (which may be slightly different). It's a little tricky when the VBA script uses "enumerated" values, like vbHorizontalAlignment or whatever. Then you have to do a little digging or coding to figure out what number that actually is because MATLAB only knows how to use the number it is, not an enumeration variable. (Hope I didn't lose anyone with all that.)

Answer 2

[dpb]

I'd hope all the files to be read are in the same location and have at least some naming convention in common. If so, the simplest thing is

d=dir('Appropriatewildcardexpression*.xls');  % return the directory list of desired files
outfile='Yourdesiredoutputfilename.xls')
L=length(d);   % how many found
v=zeros(L,1);  % array to hold values
for i=1:length(d)
  v(i)=xlsread(d(i).name,'A1');  % read the values in array
end
xlswrite(outfile,'A:')  % write in column A

ADDENDUM

Actually, if you can use something other than Excel when creating these (like a regular text file) or even better stream the output to a single file you could eliminate both of the problems with Excel -- slow the easy-to-code way, pita to code the other.