update file by DDE

[asburypark98]

Hi,
the code I inserted below, has got a problem.
It refers to the initially loop.
My aim is: open files, update them by DDE connection,
save them.
I'd like to use a loop procedure (or something
similar), that opens files one by one. So every
file is opened, then updated, then saved.

But loop goes fast and opens all files at the same
time and the code runs wrong. I tried to use
Application.OnTime method to manage the "timing"
about the opening of the files, but I hadn't
good results.

Do you know something else to solve the problem
or tell me where my code is wrong?

I also used "inputbox" instead of the loop;
but "inputbox" is good if I've got only some file..
if I've got many files, it takes me more time.

Thank you very much for your help,
deborah


'==========================================

Option Explicit
Dim WB As Workbook
Dim arrFiles()
Dim sFile As String
Dim i
Dim A

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


Sub verification()

Dim sPath As String
Dim File As String

sPath = "C:\Files\"

arrFiles = Array("Alfa", "Beta")


For i = 0 To 1

sFile = Dir(sPath & arrFiles(i) & "*.xls")
Workbooks.Open (sPath & arrFiles(i))
Set WB = ActiveWorkbook

LinkList

End If

Next i


End Sub

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

Private Sub LinkList()

Dim Links As Variant
Links = WB.LinkSources(xlOLELinks)

If Not IsEmpty(Links) Then
For A = 1 To UBound(Links)
WB.SetLinkOnData Links(A), "LinkChange"
Next A

Else
MsgBox "This workbook does not contain any links" & _
"to other workbooks"
End If


End Sub

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

Private Sub LinkChange()
'''''
End Sub


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

 

[Tom Ogilvy]

I believe SetLinkOnData is only assigned while the workbook is open. It
isn't a permanent setting.
You need to set it each time you open the workbook. That said, you don't
use setlinkondata to create a dde connection - that is done with a cell
formula and there is no reason to have setlinkondata unless you need a macro
to react to the "linked" cell being updated via dde.

 

[asburypark98]

I believe SetLinkOnData is only assigned while the workbook is open. It
isn't a permanent setting.
You need to set it each time you open the workbook. That said, you don't
use setlinkondata to create a dde connection - that is done with a cell
formula and there is no reason to have setlinkondata unless you need a macro
to react to the "linked" cell being updated via dde.

Hi Tom!
you're right about SetLinkOnData.
My English is not so good.. so it'll have some
mistake.
The command works right and it updates my file.
My problem is in the first routine (sub verification () ),
that is in the loop...
Every file the Sub verification() opens, is passed
in the Sub LinkList().
But the loop (it stays in sub verification() ) runs
fast and opens one by one all the files.
How can I write the loop that opens one file,
the it goes into the Sub LinkList (then I'd like
to save it, after the update), then opens the second
file, goes into the Sub LinkList and so on ?

In other words, I don't want the loop open all files
at the same time.

Thank you very much for your kindly attention,


sincerely,
d.

 

[Tom Ogilvy]

Sub verification()

Dim sPath As String
Dim File As String

sPath = "C:\Files\"

arrFiles = Array("Alfa", "Beta")


For i = 0 To 1

sFile = Dir(sPath & arrFiles(i) & "*.xls")
Workbooks.Open (sPath & arrFiles(i))
Set WB = ActiveWorkbook

LinkList

wb.Close SaveChanges:=True

Next i


End Sub

 

[asburypark98]

Sub verification()

Dim sPath As String
Dim File As String

sPath = "C:\Files\"

arrFiles = Array("Alfa", "Beta")

For i = 0 To 1

sFile = Dir(sPath & arrFiles(i) & "*.xls")
Workbooks.Open (sPath & arrFiles(i))
Set WB = ActiveWorkbook

LinkList

wb.Close SaveChanges:=True

Next i

End Sub

Hi Tom,
if I insert WB.Close SaveChanges:=True,
macro runs and opens the first
file and puts it into LinkList, starts the
update and opens the second file, starts the update
and then saves both the files without the new data because
there wasn't enough time to complete the update before
the saving, I think.

Thank a lot Tom,
d.

 

[Tom Ogilvy]

when you open the workbook, check the value of the cell. then continue a
loop inside your existing until the value changes.

Sub verification()

Dim sPath As String
Dim File As String

sPath = "C:\Files\"

arrFiles = Array("Alfa", "Beta")


For i = 0 To 1

sFile = Dir(sPath & arrFiles(i) & "*.xls")
Workbooks.Open (sPath & arrFiles(i))
Set WB = ActiveWorkbook
v = WB.Worksheets(1).Range("B9").Value
LinkList
do
if v <> WB.Worksheets(1).Range("B9").Value then
exit do
end if
doevents
Loop
wb.Close SaveChanges:=True

Next i


End Sub

This assume that the update will change the value of the DDE cell. In the
example, I assume the DDE formula is in B9 of the first sheet in the tab
order. I don't have anthing running DDE nor have I ever really used it, so
this is just a guess - there may be better ways.

 

[Tom Ogilvy]

you may want to throw in an Application.Calculate just before the DoEvents