Hi all,

I currently save copy of my Excel sheets every day, for historical
records.

However because some of the cells are connected to live data sources,
and other cells are linked to other time-varying sheets, the copying
of Excel sheets as record doesn't really work as it is supposed to.

The reason is that every time when you open the old record file,
things are changing and are no longer their original form.

Therefore, I could think of one way to remedy this: I should save a
"frozen" copy of the Excel sheets as snapshots and keep these
snapshots as historical records.

The easiest way is probably to "freeze" all cells, i.e. "copy and
paste special as values"...

How to do this from Matlab?

Thanks a lot!

On Sep 26, 5:24=A0pm, Luna Moon <lunamoonm...@gmail.com> wrote:
> Hi all,
>
> I currently save copy of my Excel sheets every day, for historical
> records.
>
> However because some of the cells are connected to live data sources,
> and other cells are linked to other time-varying sheets, the copying
> of Excel sheets as record doesn't really work as it is supposed to.
>
> The reason is that every time when you open the old record file,
> things are changing and are no longer their original form.
>
> Therefore, I could think of one way to remedy this: I should save a
> "frozen" copy of the Excel sheets as snapshots and keep these
> snapshots as historical records.
>
> The easiest way is probably to "freeze" all cells, i.e. "copy and
> paste special as values"...
>
> How to do this from Matlab?
>
> Thanks a lot!

i.e. "copy and paste special as values"... for all sheets everywhere...

On Sep 26, 5:24=A0pm, Luna Moon <lunamoonm...@gmail.com> wrote:
> Hi all,
>
> I currently save copy of my Excel sheets every day, for historical
> records.
---------------------------------------------------------------------------=
-------------------------
And why do you want to use MATLAB for this?  Just an ordinary backup/
archiving program would work well.  Maybe you should look at
SecondCopy.
http://secondcopy.com/

On Sep 26, 5:34=A0pm, ImageAnalyst <imageanal...@mailinator.com> wrote:
> On Sep 26, 5:24=A0pm, Luna Moon <lunamoonm...@gmail.com> wrote:> Hi all,
>
> > I currently save copy of my Excel sheets every day, for historical
> > records.
>
> -------------------------------------------------------------------------=
---------------------------
> And why do you want to use MATLAB for this? =A0Just an ordinary backup/
> archiving program would work well. =A0Maybe you should look at
> SecondCopy.http://secondcopy.com/

No, that won't work.

Simply saving a second copy of the sheets won't work.

That's because the cells are linked to external data sources/sheets
that are time-varying...

Next time when you open the sheets, even though you choose "not to
update links", numbers are still changed...

That's why we need a snapshot and frozen numbers...

"Luna Moon" <lunamoonmoon@gmail.com> wrote in message 
news:b6bab5f9-38b6-4ffd-9da4-464846daa35a@l20g2000yqm.googlegroups.com...
> On Sep 26, 5:34 pm, ImageAnalyst <imageanal...@mailinator.com> wrote:
>> On Sep 26, 5:24 pm, Luna Moon <lunamoonm...@gmail.com> wrote:> Hi all,
>>
>> > I currently save copy of my Excel sheets every day, for historical
>> > records.
>>
>> ----------------------------------------------------------------------------------------------------
>> And why do you want to use MATLAB for this?  Just an ordinary backup/
>> archiving program would work well.  Maybe you should look at
>> SecondCopy.http://secondcopy.com/
>
> No, that won't work.
>
> Simply saving a second copy of the sheets won't work.
>
> That's because the cells are linked to external data sources/sheets
> that are time-varying...
>
> Next time when you open the sheets, even though you choose "not to
> update links", numbers are still changed...
>
> That's why we need a snapshot and frozen numbers...

So determine the procedure to capture a snapshot using Excel (independent of 
MATLAB.)  Once you have that procedure in place, determine how to implement 
that procedure using the COM interface functionality that MATLAB provides. 
You'll probably want to ask for help with the first part of this problem on 
a Microsoft Excel-specific newsgroup/discussion board, like the ones on 
http:///www.microsoft.com.

-- 
Steve Lord
slord@mathworks.com
comp.soft-sys.matlab (CSSM) FAQ: http://matlabwiki.mathworks.com/MATLAB_FAQ
To contact Technical Support use the Contact Us link on 
http://www.mathworks.com 

If you really wanted to, you could use MATLAB and open up Excel as an
ActiveX server, then copy the cells and paste them back in (special)
as "values" - this will convert the formulas to constant numbers which
have the same values as the formulas at that instant in time.  Then
save it out to a new filename.  I hope you're up on your ActiveX
programming!

On Sep 27, 2:31=A0pm, "Steven_Lord" <sl...@mathworks.com> wrote:
> "Luna Moon" <lunamoonm...@gmail.com> wrote in message
>
> news:b6bab5f9-38b6-4ffd-9da4-464846daa35a@l20g2000yqm.googlegroups.com...
>
>
>
> > On Sep 26, 5:34 pm, ImageAnalyst <imageanal...@mailinator.com> wrote:
> >> On Sep 26, 5:24 pm, Luna Moon <lunamoonm...@gmail.com> wrote:> Hi all,
>
> >> > I currently save copy of my Excel sheets every day, for historical
> >> > records.
>
> >> ----------------------------------------------------------------------=
------------------------------
> >> And why do you want to use MATLAB for this? =A0Just an ordinary backup=
/
> >> archiving program would work well. =A0Maybe you should look at
> >> SecondCopy.http://secondcopy.com/
>
> > No, that won't work.
>
> > Simply saving a second copy of the sheets won't work.
>
> > That's because the cells are linked to external data sources/sheets
> > that are time-varying...
>
> > Next time when you open the sheets, even though you choose "not to
> > update links", numbers are still changed...
>
> > That's why we need a snapshot and frozen numbers...
>
> So determine the procedure to capture a snapshot using Excel (independent=
 of
> MATLAB.) =A0Once you have that procedure in place, determine how to imple=
ment
> that procedure using the COM interface functionality that MATLAB provides=
..
> You'll probably want to ask for help with the first part of this problem =
on
> a Microsoft Excel-specific newsgroup/discussion board, like the ones on
> http:///www.microsoft.com.
>
> --
> Steve Lord
> sl...@mathworks.com
> comp.soft-sys.matlab (CSSM) FAQ:http://matlabwiki.mathworks.com/MATLAB_FA=
Q
> To contact Technical Support use the Contact Us link onhttp://www.mathwor=
ks.com

That doesn't work.

The COM interface doesn't load all those addins automatically...

I tried manually open some of the addins using COM...

but that didn't work... I am not sure how many addins I need to load
using COM...

Basically my sheets use Bloomberg API so the numbers should be ticking
real-time...

but if I use COM to load the Excel sheets all the numbers become "!
VALUE#"

On Sep 27, 5:11=A0pm, ImageAnalyst <imageanal...@mailinator.com> wrote:
> If you really wanted to, you could use MATLAB and open up Excel as an
> ActiveX server, then copy the cells and paste them back in (special)
> as "values" - this will convert the formulas to constant numbers which
> have the same values as the formulas at that instant in time. =A0Then
> save it out to a new filename. =A0I hope you're up on your ActiveX
> programming!

That doesn't work.

The COM interface doesn't load all those addins automatically...

I tried manually open some of the addins using COM...

but that didn't work... I am not sure how many addins I need to load
using COM...

Basically my sheets use Bloomberg API so the numbers should be ticking
real-time...

but if I use COM to load the Excel sheets all the numbers become "!
VALUE#"

On Sep 27, 2:31=A0pm, "Steven_Lord" <sl...@mathworks.com> wrote:
> "Luna Moon" <lunamoonm...@gmail.com> wrote in message
>
> news:b6bab5f9-38b6-4ffd-9da4-464846daa35a@l20g2000yqm.googlegroups.com...
>
>
>
> > On Sep 26, 5:34 pm, ImageAnalyst <imageanal...@mailinator.com> wrote:
> >> On Sep 26, 5:24 pm, Luna Moon <lunamoonm...@gmail.com> wrote:> Hi all,
>
> >> > I currently save copy of my Excel sheets every day, for historical
> >> > records.
>
> >> ----------------------------------------------------------------------=
------------------------------
> >> And why do you want to use MATLAB for this? =A0Just an ordinary backup=
/
> >> archiving program would work well. =A0Maybe you should look at
> >> SecondCopy.http://secondcopy.com/
>
> > No, that won't work.
>
> > Simply saving a second copy of the sheets won't work.
>
> > That's because the cells are linked to external data sources/sheets
> > that are time-varying...
>
> > Next time when you open the sheets, even though you choose "not to
> > update links", numbers are still changed...
>
> > That's why we need a snapshot and frozen numbers...
>
> So determine the procedure to capture a snapshot using Excel (independent=
 of
> MATLAB.) =A0Once you have that procedure in place, determine how to imple=
ment
> that procedure using the COM interface functionality that MATLAB provides=
..
> You'll probably want to ask for help with the first part of this problem =
on
> a Microsoft Excel-specific newsgroup/discussion board, like the ones on
> http:///www.microsoft.com.
>
> --
> Steve Lord
> sl...@mathworks.com
> comp.soft-sys.matlab (CSSM) FAQ:http://matlabwiki.mathworks.com/MATLAB_FA=
Q
> To contact Technical Support use the Contact Us link onhttp://www.mathwor=
ks.com

I use Matlab here because Matlab is my centralized automation center.

I use it to do everything on my Windows XP... (much like a scripting
language)

On Sep 27, 8:19=A0pm, Luna Moon <lunamoonm...@gmail.com> wrote:
> That doesn't work.
[snip]
> but if I use COM to load the Excel sheets all the numbers become "!
> VALUE#"
---------------------------------------------------------------------------=
-----

OK, like I thought, you don't know how to do ActiveX programming.
That's OK, everyone who learns has a first time, even me.  You are
correct with what you said, but the thing is: you don't want to do
that.  You don't want to read the cells from your workbook into
MATLAB.  You simply want to open Excel and issue some commands to it
just as if you were typing directly into it (well almost - what you're
actually doing is issuing the same commands that would get issued as
if you did stuff with the keyboard or mouse right in Excel).  So you
just issue the .Selectrange command, and the copy command, and the
"paste special - values" command.  I'm not sure what these commands
are off the top of my head, but you can look them up on Microsoft's
web site (but unfortunately I don't have that URL bookmarked on this
computer though I think it's on my other one).
ImageAnalyst

On Sep 27, 9:56=A0pm, ImageAnalyst <imageanal...@mailinator.com> wrote:
> On Sep 27, 8:19=A0pm, Luna Moon <lunamoonm...@gmail.com> wrote:> That doe=
sn't work.
> [snip]
> > but if I use COM to load the Excel sheets all the numbers become "!
> > VALUE#"
>
> -------------------------------------------------------------------------=
-------
>
> OK, like I thought, you don't know how to do ActiveX programming.
> That's OK, everyone who learns has a first time, even me. =A0You are
> correct with what you said, but the thing is: you don't want to do
> that. =A0You don't want to read the cells from your workbook into
> MATLAB. =A0You simply want to open Excel and issue some commands to it
> just as if you were typing directly into it (well almost - what you're
> actually doing is issuing the same commands that would get issued as
> if you did stuff with the keyboard or mouse right in Excel). =A0So you
> just issue the .Selectrange command, and the copy command, and the
> "paste special - values" command. =A0I'm not sure what these commands
> are off the top of my head, but you can look them up on Microsoft's
> web site (but unfortunately I don't have that URL bookmarked on this
> computer though I think it's on my other one).
> ImageAnalyst

Of course I know how to do COM/ACTIVEX programming. I recorded the
keystrokes.

But when I manually run the macro it worked.

If I run the macro programmatically from within Matlab (two ways: 1.
directly run the macro in Com/ActiveX; 2. Matlabize the VBA code line
by line to make it Com/ActiveX), both failed because of the cells
(linked to Bloomberg real-time data-feeds) become !VALUE#

Then I don't know.  It seems like you should be able to run a macro in
Excel that's stored in the workbook, and have it work the same as if
you ran it directly from Excel itself.  Likewise for your second way
of trying.

Sorry I don't have the time to help on this issue anymore.  Maybe
you'll have to hire a VB/Excel programmer.  Good luck.

"Luna Moon" <lunamoonmoon@gmail.com> wrote in message 
news:47b7dc10-6ae7-44a8-99cf-77352a245341@t3g2000vbb.googlegroups.com...
> On Sep 27, 2:31 pm, "Steven_Lord" <sl...@mathworks.com> wrote:
>> "Luna Moon" <lunamoonm...@gmail.com> wrote in message

*snip*

>> So determine the procedure to capture a snapshot using Excel (independent 
>> of
>> MATLAB.)  Once you have that procedure in place, determine how to 
>> implement
>> that procedure using the COM interface functionality that MATLAB 
>> provides.
>> You'll probably want to ask for help with the first part of this problem 
>> on
>> a Microsoft Excel-specific newsgroup/discussion board, like the ones on
>> http:///www.microsoft.com.
>>
>> --
>> Steve Lord
>> sl...@mathworks.com
>> comp.soft-sys.matlab (CSSM) 
>> FAQ:http://matlabwiki.mathworks.com/MATLAB_FAQ
>> To contact Technical Support use the Contact Us link 
>> onhttp://www.mathworks.com
>
> I use Matlab here because Matlab is my centralized automation center.
>
> I use it to do everything on my Windows XP... (much like a scripting
> language)

Well, unless you can reverse engineer the file format for Microsoft Excel 
and write your own function in MATLAB to manipulate the file using that 
knowledge, I think you're going to HAVE to make use of Excel.

-- 
Steve Lord
slord@mathworks.com
comp.soft-sys.matlab (CSSM) FAQ: http://matlabwiki.mathworks.com/MATLAB_FAQ
To contact Technical Support use the Contact Us link on 
http://www.mathworks.com