Read same cell in multiple excel files

5 views (last 30 days)
robert
robert on 28 Oct 2014
Commented: Image Analyst on 29 Oct 2014
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

Sign in to answer this question.

Accepted Answer

Image Analyst
Image Analyst on 29 Oct 2014
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.
  2 Comments
dpb
dpb on 29 Oct 2014
...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
Image Analyst on 29 Oct 2014
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.)

Sign in to comment.

More Answers (1)

dpb
dpb on 28 Oct 2014
Edited: dpb on 29 Oct 2014
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.

Categories

Find more on ActiveX in Help Center and File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!