Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
update file by DDE
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 '------------------------------------------------ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
update file by DDE
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. -- Regards, Tom Ogilvy "asburypark98" wrote: 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 '------------------------------------------------ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
update file by DDE
On 12 Feb, 15:25, Tom Ogilvy
wrote: 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. -- Regards, Tom Ogilvy 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
update file by DDE
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 -- Regards, Tom Ogilvy "asburypark98" wrote: On 12 Feb, 15:25, Tom Ogilvy wrote: 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. -- Regards, Tom Ogilvy 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
update file by DDE
On 12 Feb, 16:36, Tom Ogilvy
wrote: 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 -- Regards, Tom Ogilvy 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
update file by DDE
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. -- Regards, Tom Ogilvy "asburypark98" wrote: On 12 Feb, 16:36, Tom Ogilvy wrote: 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 -- Regards, Tom Ogilvy 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
update file by DDE
you may want to throw in an Application.Calculate just before the DoEvents
-- Regards, Tom Ogilvy "asburypark98" wrote: On 12 Feb, 16:36, Tom Ogilvy wrote: 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 -- Regards, Tom Ogilvy 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changes in destination file should update sourse file | Excel Discussion (Misc queries) | |||
Macro to pull data from another file - update name of file | Excel Discussion (Misc queries) | |||
i want to update one excel file the other one update automaticaly | New Users to Excel | |||
Automatic update of links in destination file when source file mo. | Excel Discussion (Misc queries) | |||
Automate open file, update links, run macro, close and save file | Excel Programming |