Hi,
my problem refers to the code below.
It applies on two files (A.xls, B.xls).
My goal is as follow: i want to open the first file
(it updates by DDE) and, after 40 sec., save it and
open the second file and repeat the operations: update
and then, after 40 sec., save it.

But my code opens the two files at the same time.
It saves the last one and then macro stops because
of the error.
I've got a msg: "Run-time error '91'"

I'm looking at the error but I don't see where it
can be... maybe I've used the some wrong
functions.
Could you suggest me something that can help
me ?

Thank you very much,
ap

'-------------------------------------------------
Option Explicit
Public WB As Workbook
Dim arrFiles()
Dim i
'--------------------------------------------------

Public Sub link()

Dim sFile As String
Dim sPath As String

arrFiles = Array("A", "B")
sPath = "C:\FILES\"

For i = 0 To 1
sFile = Dir(sPath & arrFiles(i) & "*.xls")
Workbooks.Open (sPath & arrFiles(i))
open
Next i


End Sub


'---------------------------------------------------

Public Sub open()


Set WB = ActiveWorkbook
Worksheets("Sheet1").Range("AO1:AS38").Calculate

Application.Run (arrFiles(i) & "!" & "DoOnData")
Application.onTime Now + TimeValue("00:00:40"), "saving"

End Sub


'-------------------------------------------------------

Public Sub saving()

WB.Close savechanges:=True
Set WB = Nothing


End Sub

One issue I think you have is here. WB is declared as a procedure level
variable, not a module level variable, therefore the "saving" subroutine has
no idea what WB is. See VBA help for variable "scope".

Public Sub saving()

WB.Close savechanges:=True
Set WB = Nothing


End Sub

Also, I wouldn't use "open" for a subroutine name as VBA has it's own open
method with the same name.

My experience is that OnTime won't stop the macro from continuing to process
the next line of code. If you want to wait 40 seconds before proceeding,
perhaps check VBA help for the wait method - it sounds like that may be more
along the lines of what you want to do (I've never actually used or seen this
method before, but this is the second time I've suggested it tonight and I've
only looked at two posts).



"asburypark98" wrote:

Hi,
my problem refers to the code below.
It applies on two files (A.xls, B.xls).
My goal is as follow: i want to open the first file
(it updates by DDE) and, after 40 sec., save it and
open the second file and repeat the operations: update
and then, after 40 sec., save it.

But my code opens the two files at the same time.
It saves the last one and then macro stops because
of the error.
I've got a msg: "Run-time error '91'"

I'm looking at the error but I don't see where it
can be... maybe I've used the some wrong
functions.
Could you suggest me something that can help
me ?

Thank you very much,
ap

'-------------------------------------------------
Option Explicit
Public WB As Workbook
Dim arrFiles()
Dim i
'--------------------------------------------------

Public Sub link()

Dim sFile As String
Dim sPath As String

arrFiles = Array("A", "B")
sPath = "C:\FILES\"

For i = 0 To 1
sFile = Dir(sPath & arrFiles(i) & "*.xls")
Workbooks.Open (sPath & arrFiles(i))
open
Next i


End Sub


'---------------------------------------------------

Public Sub open()


Set WB = ActiveWorkbook
Worksheets("Sheet1").Range("AO1:AS38").Calculate

Application.Run (arrFiles(i) & "!" & "DoOnData")
Application.onTime Now + TimeValue("00:00:40"), "saving"

End Sub


'-------------------------------------------------------

Public Sub saving()

WB.Close savechanges:=True
Set WB = Nothing


End Sub

A link to the other post w/what appears to be a similar question.

http://www.microsoft.com/office/comm...2-88d78112fde9


"JMB" wrote:

One issue I think you have is here. WB is declared as a procedure level
variable, not a module level variable, therefore the "saving" subroutine has
no idea what WB is. See VBA help for variable "scope".

Public Sub saving()

WB.Close savechanges:=True
Set WB = Nothing


End Sub

Also, I wouldn't use "open" for a subroutine name as VBA has it's own open
method with the same name.

My experience is that OnTime won't stop the macro from continuing to process
the next line of code. If you want to wait 40 seconds before proceeding,
perhaps check VBA help for the wait method - it sounds like that may be more
along the lines of what you want to do (I've never actually used or seen this
method before, but this is the second time I've suggested it tonight and I've
only looked at two posts).



"asburypark98" wrote:

Hi,
my problem refers to the code below.
It applies on two files (A.xls, B.xls).
My goal is as follow: i want to open the first file
(it updates by DDE) and, after 40 sec., save it and
open the second file and repeat the operations: update
and then, after 40 sec., save it.

But my code opens the two files at the same time.
It saves the last one and then macro stops because
of the error.
I've got a msg: "Run-time error '91'"

I'm looking at the error but I don't see where it
can be... maybe I've used the some wrong
functions.
Could you suggest me something that can help
me ?

Thank you very much,
ap

'-------------------------------------------------
Option Explicit
Public WB As Workbook
Dim arrFiles()
Dim i
'--------------------------------------------------

Public Sub link()

Dim sFile As String
Dim sPath As String

arrFiles = Array("A", "B")
sPath = "C:\FILES\"

For i = 0 To 1
sFile = Dir(sPath & arrFiles(i) & "*.xls")
Workbooks.Open (sPath & arrFiles(i))
open
Next i


End Sub


'---------------------------------------------------

Public Sub open()


Set WB = ActiveWorkbook
Worksheets("Sheet1").Range("AO1:AS38").Calculate

Application.Run (arrFiles(i) & "!" & "DoOnData")
Application.onTime Now + TimeValue("00:00:40"), "saving"

End Sub


'-------------------------------------------------------

Public Sub saving()

WB.Close savechanges:=True
Set WB = Nothing


End Sub

Great thanks JMB !!!!

I've just looked at the web site you suggested me and I used
"Application.Wait" instead of
"Application.Run" command and code seems to be right now !

ap

You're welcome.

"asburypark98" wrote:

Great thanks JMB !!!!

I've just looked at the web site you suggested me and I used
"Application.Wait" instead of
"Application.Run" command and code seems to be right now !

ap