I have a word document that updates data in an Excel spreadsheet. These were
created in Office 97. They ran fine on updating to Office 2000! Since
updating to office XP I get "Run-time error -2147023170 (800706be)"
Automation Error The remote procedure call failed. The Office help file
gives error 440 (When Help button is pressed) The KB has no references to
this error in Office - a few in VB6. I have checked permissions and they are
OK (As partition is now NTFS) I have even updated DAO to 3.6. All to no
avail. It would appear that the spreadsheet is being opened and updated ok
but the error occurs when "Save" is invoked.

Below is a piece of code where the error occurs.

Any help greatly appreciated.

Rick

Public Sub UpdateSpreadsheetMinus()      'Procedure to alter stock (Minus)
if boot stock is used

    Dim xlWsheet As Object     'Variable to set XL SS to
    Dim SPT As Integer        'Variable for search loop (SearchParT)
    Dim PartExist As Boolean       'Variable for checking part no

    Set xlWsheet = GetObject(FullPath) 'open wsheet and assign to xlWsheet

        For SPT = 4 To 100         'Number of rows used in SS - To be
enhanced by checking for the last row entry

            With xlWsheet

                If .Worksheets(1).Cells(SPT, 1).Value = ComboBox2.Text Then
'Check to see part no in Combo2 is in the list

                PartExist = True       'Flag to check (Later in code) for
part no existing in bootstock

                    If .Worksheets(1).Cells(SPT, 2).Value <= 0 Then
'Test that stock level is not already zero or less
                    MsgBox ("Stock Quantity is Zero!" + Chr(10) + "Update
will be cancelled!!"), 16, "No Stock"
                    Call DelLastRow                    'Remove entries from
Word table (Note no removal required on SS as data not updated yet
                    Exit For
'Exit loop and jump to Next SPT
                    End If

                .Worksheets(1).Cells(SPT, 2).Value =
.Worksheets(1).Cells(SPT, 2).Value - 1    'Reduce stock amount by 1

                End If

            End With

        Next SPT

        If PartExist = False Then
'If part number did not exist-

        MsgBox ("This is not a Bootstock part number - please check it and
try again!" + Chr(13) + "                          Update Has been Cancelled
"), 48, "Incorrect Part Number"     'run error msg -

        ActiveDocument.Tables(1).Rows.Last.Delete
'clear table last table entry in word -
        Call ClearForm
'& clear form contents

        End If

    xlWsheet.Windows(1).Visible = True          'Make wsheet visible in XL
    xlWsheet.Save                               'Save before quitting - THIS
IS ERROR OCCURS
    xlWsheet.Application.Quit                   'Quit XL
    Set xlWsheet = Nothing                      'Free up memory

End Sub

Hi R.Wilson,

Are you able to open, edit and save the worksheet in the Excel UI
with no problems? Or are there messages?

What about opening, changing, saving (a copy) with code in an Excel
module? That might at least give you a more informative eror message
than "Automation error"...

Do you have any file managment or anti-virus software running? If you
do, turn it off and test again to see if that's interfering.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail :-)

Yep! your problem is with the line
xlWsheet.Save
in XP you have to change this to
a workbook object eg
if xlbook is set to the current workbook
xlbook.save  will work.

Excel Visual Basic help says
Syntax
expression.Save
expression   Required. An expression that returns a Workbook object.