Locking a worksheet during the reading of it

[Guest]

Hello All!

I have an Excel 2003 workbook that contains Exchange Rates that is
automatically updated by an external program. I have used VSTO 2005 to
create an application that reads the updated Exchange Rates and saves them to
a database using a webservice.

The problem that I'm having is that when I'm reading my values, if any of
the cells change, my application crashes. Is there a way to lock the values
of the worksheet so that they don't get changed while I'm trying to read them?

 

[Guest]

Thanks for the reply MSweetG222

We thought of doing that, but the only problem is that the "external
program" that I was refering too actually is a "plug-in" for excel...sorry
for the mis-communication...I guess i meant that it's a third-party
plug-in...(Actually, it's from Reuter's, I think it's called powerplus or
something like that)...Anyways, the problem with saving the workbook, and
then using the copy to read our values is that as soon as we open the
workbook, the Reuter's stuff will automatically update and we have the same
problem...

 

[Guest]

Thanks for the response Peter,

I'm not sure I explained my problem quite right...The "external program"
that I'm refering too is actually a plug-in provided by Reuter's..and its
what does the updating of the exchange rates...basically it updates the cells
we specify with the exchange rates that we require.

I then want to read those exchange rates, connect to my webservice, and
update my database with those exchange rates...

Does that make more sense? I'm not sure that the RTD is going to work in
our situation...

Jason

 

[Peter Huang [MSFT]]

Hi Jason,

I understand the plugin is a COMAddin.
From the scenario, it may be a race condition that the plugin is hoding the
reference to certian cell/range and writing data and the VSTO code is try
to hold and read the cell reference.
This would need investigate the concrete ComAddin source about how it is
implemented.

So far I think you may try to enbrace the code that will cause the problem
in the try/catch block to see if that will try to fix the problem.
e.g. When you click the Button of the VSTO project and the excel will
crash, then you may try to enbrace all the Button click event handler code
in the try/catch.

Also you may try to contact the Addin develop to see if there is a method
to disable the addin code to refresh the data in the Excel Sheet
temporarily.
Based on my research, we can not disable a ComAddin temporarily with VBA
code without restart the Excel application.

A possible workaround is that you may try to save the current workbook as
another workbook and then use the ADO/ADO.NET code to retrieve the Data in
the Excel file that is not current loaded by Excel process so the com addin
will not affect it.
311731 How To Query and Display Excel Data by Using ASP.NET, ADO.NET, and
Visual Basic .NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;311731
306572 How to query and display excel data by using ASP.NET, ADO.NET, and
Visual C# .NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;306572

316934 How To Use ADO.NET to Retrieve and Modify Records in an Excel
Workbook With Visual Basic .NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934


Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[xltrader100]

Jason, try this


Option Explicit
Option Base 1 '<<< variant array index starts at 1

Sub makeStableCopy()

Range("A27") = "xxxx" ' <<< Reuters fills these in anywhere
Range("Q100") = "yyyy" ' <<< Reuters fills these in anywhere

Dim ReutersRates As Range
Set ReutersRates = Range("A1:IP1000") '<<< set your active range

With ThisWorkbook.Sheets("Prices") '<<< set your active sheet
ReDim vArray(.UsedRange.Row, .UsedRange.Column)
vArray = .UsedRange.Value
End With

Dim stableRates As Range
Set stableRates = Range("A1001:A10000") '<<< set your destination
range

stableRates(1).Value = vArray(27, 1) '<<< the value from [A27]
stableRates(2).Value = vArray(100, 17) '<<< the value from [Q100]
'these won't change no matter what Reuters does until you run this proc
again.


Debug.Print "range(A1001).value = "; Range("A1001").Value
Debug.Print "range(A1002).value = "; Range("A1002").Value
Debug.Print "stableRates(1).Value = "; stableRates(1).Value
Debug.Print "stableRates(2).Value = "; stableRates(2).Value
End Sub

'The shape of your destination array doesn't have to bear
'any relation to the shape of your input array as long as you
'index into the variant array with the same numbers as the
'cell coordinates of ReutersRates.

Roy