Does loop function cause this problem?

[Eric]

Once I use the loop function for following codes, I run into problems on
files, such as
1) All files can be read and written in the first place, but somehow I don't
understand why some file type becomes read only. Does the loop cause any
problem by retrieving data too quick while saving and closing processes do
not complete?

2) date from Data file could be retrieved by other application at the same
time, so I set the displayalert to be false, but DisplayAlerts is still pop
up for locating the Data file, any suggestions on what cause this problem?

Does anyone have any suggestions on how to solve this loop problem?
Thank in advance for any suggestions
Eric
-------------------------------------------------------------------------------------
Application.DisplayAlerts = False
{Retrieve the latest date in cell A2 from file Data.xls}
x = TheValue("E:\Files", "Data.xls", "Records", "A2")

[Repeated codes - start]
Do
{Summary file must be opened at least once}
Workbooks.Open Filename:="E:\Files\Summary.xls", UpdateLinks:=3
Workbooks("Summary.xls").Close savechanges:=True
{Retrieve the last updated date in cell A591 from file Summary.xls}
y = TheValue("E:\Files", "Summary.xls", "Records", "A591")
{If the date is different between Data and Summary files, then redo this
step again}
Loop While x <> y
[Repeated codes - end]

{This repeated codes will be duplicated 64 times for different files to be
updated}
-------------------------------------------------------------------------------------

 

[JLatham]

I don't see anything that should cause a change in the read status. You say
that they're coming up later as Read Only? Are they actually Read Only (go
to the folder, right click a filename and look at properties, if Read Only is
checked, then they are actually read only - but if it is clear then they are
not read only but Excel is somehow flagging them as "open as read only
recommended").

If the problem is Excel somehow flagging them as 'read only recommended'
then you might try this workaround. Remember that when you do a
Workbooks.Open then that workbook becomes the active workbook and remains the
active workbook until you activate another or close it, and that is the
premise of this workaround code. I introduce a string variable, fName, here
that you may need to declare earlier in the code:

fName = "E:\Files\Summary.xls"
Do
Workbooks.Open Filename:=fName, UpdateLinks:=3
'you've already turned off .DisplayAlerts, so don't have to again here
'assumes the workbook opened above is still the active workbook
ActiveWorkbook.SaveAs Filename:=fName, ReadOnlyRecommended:=False
'continue on with your code using fName instead of literal string where
appropriate

 

[Eric]

I run two excel applications separately, because I get 2 CPUs for my
computer, and assign each excel application for each CPU. Both excel
applications will be opened, save and closed a list of files one by one at
the same time, and all files are referred to Data.xls for retrieving data.
Both application run simultaneously, in the middle of process, one of excel
application stops and pop up an error on macro as show below

Loop While x <> y
I receive Error 2023 on x variable
Does anyone have any suggestions on what cause this problem and how to solve
it?
Thank for any suggestions
Eric

I don't see anything that should cause a change in the read status. You say
that they're coming up later as Read Only? Are they actually Read Only (go
to the folder, right click a filename and look at properties, if Read Only is
checked, then they are actually read only - but if it is clear then they are
not read only but Excel is somehow flagging them as "open as read only
recommended").

If the problem is Excel somehow flagging them as 'read only recommended'
then you might try this workaround. Remember that when you do a
Workbooks.Open then that workbook becomes the active workbook and remains the
active workbook until you activate another or close it, and that is the
premise of this workaround code. I introduce a string variable, fName, here
that you may need to declare earlier in the code:

fName = "E:\Files\Summary.xls"
Do
Workbooks.Open Filename:=fName, UpdateLinks:=3
'you've already turned off .DisplayAlerts, so don't have to again here
'assumes the workbook opened above is still the active workbook
ActiveWorkbook.SaveAs Filename:=fName, ReadOnlyRecommended:=False
'continue on with your code using fName instead of literal string where
appropriate

Once I use the loop function for following codes, I run into problems on
files, such as
1) All files can be read and written in the first place, but somehow I don't
understand why some file type becomes read only. Does the loop cause any
problem by retrieving data too quick while saving and closing processes do
not complete?

2) date from Data file could be retrieved by other application at the same
time, so I set the displayalert to be false, but DisplayAlerts is still pop
up for locating the Data file, any suggestions on what cause this problem?

Does anyone have any suggestions on how to solve this loop problem?
Thank in advance for any suggestions
Eric
-------------------------------------------------------------------------------------
Application.DisplayAlerts = False
{Retrieve the latest date in cell A2 from file Data.xls}
x = TheValue("E:\Files", "Data.xls", "Records", "A2")

[Repeated codes - start]
Do
{Summary file must be opened at least once}
Workbooks.Open Filename:="E:\Files\Summary.xls", UpdateLinks:=3
Workbooks("Summary.xls").Close savechanges:=True
{Retrieve the last updated date in cell A591 from file Summary.xls}
y = TheValue("E:\Files", "Summary.xls", "Records", "A591")
{If the date is different between Data and Summary files, then redo this
step again}
Loop While x <> y
[Repeated codes - end]

{This repeated codes will be duplicated 64 times for different files to be
updated}

 

[JLatham]

As this is a different question than your original, you may want to repost it
as a topic of its own.

I've not tried running 2 instances of Excel on a dual core system, so I
don't have any experience in that area. Have you checked the values of X and
Y when the error occurs? Choose [Debug] and use the Immediate Window to seen
their values. You mention that both instances are using one file, Data.xls,
for retrieving data - I suspect that both instances trying to access the same
file and perhaps the same data item in that file may be part of the problem.
But that's just a guess.

I run two excel applications separately, because I get 2 CPUs for my
computer, and assign each excel application for each CPU. Both excel
applications will be opened, save and closed a list of files one by one at
the same time, and all files are referred to Data.xls for retrieving data.
Both application run simultaneously, in the middle of process, one of excel
application stops and pop up an error on macro as show below

Loop While x <> y
I receive Error 2023 on x variable
Does anyone have any suggestions on what cause this problem and how to solve
it?
Thank for any suggestions
Eric

I don't see anything that should cause a change in the read status. You say
that they're coming up later as Read Only? Are they actually Read Only (go
to the folder, right click a filename and look at properties, if Read Only is
checked, then they are actually read only - but if it is clear then they are
not read only but Excel is somehow flagging them as "open as read only
recommended").

If the problem is Excel somehow flagging them as 'read only recommended'
then you might try this workaround. Remember that when you do a
Workbooks.Open then that workbook becomes the active workbook and remains the
active workbook until you activate another or close it, and that is the
premise of this workaround code. I introduce a string variable, fName, here
that you may need to declare earlier in the code:

fName = "E:\Files\Summary.xls"
Do
Workbooks.Open Filename:=fName, UpdateLinks:=3
'you've already turned off .DisplayAlerts, so don't have to again here
'assumes the workbook opened above is still the active workbook
ActiveWorkbook.SaveAs Filename:=fName, ReadOnlyRecommended:=False
'continue on with your code using fName instead of literal string where
appropriate

Once I use the loop function for following codes, I run into problems on
files, such as
1) All files can be read and written in the first place, but somehow I don't
understand why some file type becomes read only. Does the loop cause any
problem by retrieving data too quick while saving and closing processes do
not complete?

2) date from Data file could be retrieved by other application at the same
time, so I set the displayalert to be false, but DisplayAlerts is still pop
up for locating the Data file, any suggestions on what cause this problem?

Does anyone have any suggestions on how to solve this loop problem?
Thank in advance for any suggestions
Eric
-------------------------------------------------------------------------------------
Application.DisplayAlerts = False
{Retrieve the latest date in cell A2 from file Data.xls}
x = TheValue("E:\Files", "Data.xls", "Records", "A2")

[Repeated codes - start]
Do
{Summary file must be opened at least once}
Workbooks.Open Filename:="E:\Files\Summary.xls", UpdateLinks:=3
Workbooks("Summary.xls").Close savechanges:=True
{Retrieve the last updated date in cell A591 from file Summary.xls}
y = TheValue("E:\Files", "Summary.xls", "Records", "A591")
{If the date is different between Data and Summary files, then redo this
step again}
Loop While x <> y
[Repeated codes - end]

{This repeated codes will be duplicated 64 times for different files to be
updated}