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

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

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

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

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
>