Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
links update problem
I'm updating links in Excel workbook from MS Access app.
It was working well and I didn't do any changes. But, now I'm getting an error during the debugging: "Error number 1004: Application-defined or object defined error." Before that, I've got some error that links cannot be updated in ...._Workbook (but no this error message during the debugging). Dim xlapp1 As New Excel.Application Dim xlbook1 As New Excel.Workbook xlapp1.DisplayAlerts = False xlapp1.AskToUpdateLinks = False Set xlbook1 = xlapp1.Workbooks.Open(strOurFileName, True) With xlbook1 .UpdateLink Name:=.LinkSources ' Here is the error End With xlapp1.ActiveWorkbook.Save xlapp1.Quit Could anybody clarify how I could fix it? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
links update problem
Which line generates the Error?
"Alex" wrote: I'm updating links in Excel workbook from MS Access app. It was working well and I didn't do any changes. But, now I'm getting an error during the debugging: "Error number 1004: Application-defined or object defined error." Before that, I've got some error that links cannot be updated in ..._Workbook (but no this error message during the debugging). Dim xlapp1 As New Excel.Application Dim xlbook1 As New Excel.Workbook xlapp1.DisplayAlerts = False xlapp1.AskToUpdateLinks = False Set xlbook1 = xlapp1.Workbooks.Open(strOurFileName, True) With xlbook1 .UpdateLink Name:=.LinkSources ' Here is the error End With xlapp1.ActiveWorkbook.Save xlapp1.Quit Could anybody clarify how I could fix it? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
links update problem
Thanks for your response, Jim.
It's .UpdateLink Name:=.LinkSources ' Here is the error But, I think some links corrupted were there at that time. "Jim Thomlinson" wrote: Which line generates the Error? "Alex" wrote: I'm updating links in Excel workbook from MS Access app. It was working well and I didn't do any changes. But, now I'm getting an error during the debugging: "Error number 1004: Application-defined or object defined error." Before that, I've got some error that links cannot be updated in ..._Workbook (but no this error message during the debugging). Dim xlapp1 As New Excel.Application Dim xlbook1 As New Excel.Workbook xlapp1.DisplayAlerts = False xlapp1.AskToUpdateLinks = False Set xlbook1 = xlapp1.Workbooks.Open(strOurFileName, True) With xlbook1 .UpdateLink Name:=.LinkSources ' Here is the error End With xlapp1.ActiveWorkbook.Save xlapp1.Quit Could anybody clarify how I could fix it? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
links update problem
If the workbook that served as a link was moved/deleted, then you could get this
error. Maybe a check first would be a good idea. Option Explicit Sub testme01() Dim xlapp1 As New Excel.Application Dim xlbook1 As New Excel.Workbook Dim myAskToUpdateLinks As Boolean Dim myLinks As Variant Dim iCtr As Long Dim testStr As String Dim strOurFileName As String strOurFileName = "C:\my documents\excel\book1.xls" xlapp1.DisplayAlerts = False myAskToUpdateLinks = xlapp1.AskToUpdateLinks xlapp1.AskToUpdateLinks = False 'nice for testing xlapp1.Visible = True Set xlbook1 = xlapp1.Workbooks.Open(strOurFileName, True) myLinks = xlbook1.LinkSources(Type:=xlLinkTypeExcelLinks) If Not IsEmpty(myLinks) Then For iCtr = LBound(myLinks) To UBound(myLinks) testStr = "" On Error Resume Next testStr = Dir(myLinks(iCtr)) On Error GoTo 0 If testStr = "" Then 'link is missing, do nothing Else xlbook1.UpdateLink Name:=myLinks(iCtr) End If Next iCtr End If xlapp1.ActiveWorkbook.Save xlapp1.AskToUpdateLinks = myAskToUpdateLinks xlapp1.Quit End Sub Now I have a question or two.... First, if you change that "asktoupdatelinks", shouldn't you change it back to what the user had before you changed it?? Second, you turn off asktoupdatelinks, but then open the workbook with links updating (that True in your workbooks.open() statement. Why bother updating the links a second time if you open that workbook with links updating? Although, True doesn't look like it's one of the documented choices -- from VBA's help: Value Meaning 0 Doesn't update any references 1 Updates external references but not remote references 2 Updates remote references but not external references 3 Updates both remote and external references I guess my question just boils down to why not just open the workbook and update the links the way you want and dump all the asktoupdatelinks and updating links in code??? Alex wrote: Thanks for your response, Jim. It's .UpdateLink Name:=.LinkSources ' Here is the error But, I think some links corrupted were there at that time. "Jim Thomlinson" wrote: Which line generates the Error? "Alex" wrote: I'm updating links in Excel workbook from MS Access app. It was working well and I didn't do any changes. But, now I'm getting an error during the debugging: "Error number 1004: Application-defined or object defined error." Before that, I've got some error that links cannot be updated in ..._Workbook (but no this error message during the debugging). Dim xlapp1 As New Excel.Application Dim xlbook1 As New Excel.Workbook xlapp1.DisplayAlerts = False xlapp1.AskToUpdateLinks = False Set xlbook1 = xlapp1.Workbooks.Open(strOurFileName, True) With xlbook1 .UpdateLink Name:=.LinkSources ' Here is the error End With xlapp1.ActiveWorkbook.Save xlapp1.Quit Could anybody clarify how I could fix it? Thanks -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
links update problem
Thanks a lot, Dave.
I'm using the data from those excel spreadsheets in MS Access. There are many excel files linked. To have updated data I'm updating those links. It should be invisible for a user. After that I'm returning back xlapp1.AskToUpdateLinks = True I'm using xlapp1.AskToUpdateLinks = False and .UpdateLink Name:=.LinkSources just in case. I wasn't sure what was working. You're right I need to use just one of them. "Dave Peterson" wrote: If the workbook that served as a link was moved/deleted, then you could get this error. Maybe a check first would be a good idea. Option Explicit Sub testme01() Dim xlapp1 As New Excel.Application Dim xlbook1 As New Excel.Workbook Dim myAskToUpdateLinks As Boolean Dim myLinks As Variant Dim iCtr As Long Dim testStr As String Dim strOurFileName As String strOurFileName = "C:\my documents\excel\book1.xls" xlapp1.DisplayAlerts = False myAskToUpdateLinks = xlapp1.AskToUpdateLinks xlapp1.AskToUpdateLinks = False 'nice for testing xlapp1.Visible = True Set xlbook1 = xlapp1.Workbooks.Open(strOurFileName, True) myLinks = xlbook1.LinkSources(Type:=xlLinkTypeExcelLinks) If Not IsEmpty(myLinks) Then For iCtr = LBound(myLinks) To UBound(myLinks) testStr = "" On Error Resume Next testStr = Dir(myLinks(iCtr)) On Error GoTo 0 If testStr = "" Then 'link is missing, do nothing Else xlbook1.UpdateLink Name:=myLinks(iCtr) End If Next iCtr End If xlapp1.ActiveWorkbook.Save xlapp1.AskToUpdateLinks = myAskToUpdateLinks xlapp1.Quit End Sub Now I have a question or two.... First, if you change that "asktoupdatelinks", shouldn't you change it back to what the user had before you changed it?? Second, you turn off asktoupdatelinks, but then open the workbook with links updating (that True in your workbooks.open() statement. Why bother updating the links a second time if you open that workbook with links updating? Although, True doesn't look like it's one of the documented choices -- from VBA's help: Value Meaning 0 Doesn't update any references 1 Updates external references but not remote references 2 Updates remote references but not external references 3 Updates both remote and external references I guess my question just boils down to why not just open the workbook and update the links the way you want and dump all the asktoupdatelinks and updating links in code??? Alex wrote: Thanks for your response, Jim. It's .UpdateLink Name:=.LinkSources ' Here is the error But, I think some links corrupted were there at that time. "Jim Thomlinson" wrote: Which line generates the Error? "Alex" wrote: I'm updating links in Excel workbook from MS Access app. It was working well and I didn't do any changes. But, now I'm getting an error during the debugging: "Error number 1004: Application-defined or object defined error." Before that, I've got some error that links cannot be updated in ..._Workbook (but no this error message during the debugging). Dim xlapp1 As New Excel.Application Dim xlbook1 As New Excel.Workbook xlapp1.DisplayAlerts = False xlapp1.AskToUpdateLinks = False Set xlbook1 = xlapp1.Workbooks.Open(strOurFileName, True) With xlbook1 .UpdateLink Name:=.LinkSources ' Here is the error End With xlapp1.ActiveWorkbook.Save xlapp1.Quit Could anybody clarify how I could fix it? Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update links box gives Continue or Edit Links dialog | Excel Discussion (Misc queries) | |||
Update Links problem | Excel Worksheet Functions | |||
update links - problem | Excel Discussion (Misc queries) | |||
update links - problem | Links and Linking in Excel | |||
Update Links - Problem | Links and Linking in Excel |