I use a macro to automatically upda

[schwob]

I use a macro to automatically update and save an Excel workbook in *.csv type. It works perfectly for any document opened in Excel, but when I use it on an inserted object in Word, it fails...

I have a 1004 error message each time I run my macro on an Excel Worksheet object inserted in Word: the 'DisplayAlert' method from the object '_Application' has failed... I use 'Application.DisplayAlerts = False' in order not to have the confirm msgs pop ups.

How can I make my code work???

 

[bkclaw113]

I am using Excel and Word 97 on my machine here, so the process might not be the same if you are using a different version.

Each Microsoft Application has its own object model which included certain objects, preterits, functions, etc. If you are trying to run Excel code in Word, it is probably using the Word object model instead of Excel. To get around this open up the VBA editor window, select tools, references. Then select the reference to Excel. You may need to play with the priority of the different objects or go back into your code and explicitly reference everything to the Excel Application object.

 

[schwob]

In fact, my (simplified) code is:

Public Sub Tarif1()
Application.DisplayAlerts = False
ChDir "G:\"
On Error Resume Next
ActiveWorkbook.Save
On Error GoTo 0
If Err.Number = 91 Then
'=> error
Else
Filename = ActiveWorkbook.FullName
If Filename = "" Then
'=>error
Else
ActiveWorkbook.SaveAs Filename:="G:\tarif.xls", FileFormat:=xlNormal, _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
End If
Application.DisplayAlerts = True
End Sub

I tried to put Excel.Application.DisplayAlerts but it doesn't work. It seems that the object inserted in word has no name, and that may be why I get a 1004 error msg, but I don't know what to do...

 

[bkclaw113]

Sorry but I do not have any additional suggestions, hopefully someone out there is more experienced than I.

 

[schwob]

I finally found the solution!!!
it's VERY tricky, especially for someone like me who is relatively new to VBA programming... The problem was that the embedded object had no name, so when I ran the macro I got "1004" error msg. What I did was the following:

Dim XL As Excel.Application

Public Sub Tarif1()

Dim MyTask As Task

On Error GoTo NotMSWord
If Right(ActiveWorkbook.FullName, 3) = "doc" Then
On Error GoTo 0
Cells.Select
Selection.Copy
Set XL = New Excel.Application
XL.Visible = True
XL.Workbooks.Add
XL.ActiveSheet.Paste
XL.AddIns("Tarification&quot.Installed = True
Word.Application.DisplayAlerts = wdAlertsNone
XL.Application.DisplayAlerts = False
For Each MyTask In Tasks
If InStr(MyTask.Name, "Microsoft Excel&quot > 0 Then
MyTask.Activate
End If
Next MyTask
TarifPath = XL.AddIns("Tarification&quot.FullName
XL.Workbooks.Open TarifPath
For Each MyTask In Tasks
If InStr(MyTask.Name, "Microsoft Word&quot > 0 Then
XL.Run ("tarif.xla!CloseWord&quot
Exit Sub
End If
Next MyTask
End If
NotMSWord:

Application.DisplayAlerts = False
ChDir "G:\"
If Err.Number = 91 Then
'=> error
Else
Filename = ActiveWorkbook.FullName
If Filename = "" Then
'=>error
Else
ActiveWorkbook.SaveAs Filename:="G:\tarif.xls", FileFormat:=xlNormal, _
ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
End If
Application.DisplayAlerts = True
End Sub

Sub CloseWord()
Tarif1
If Tasks.Exists("Microsoft Word&quot Then
With Tasks("Microsoft Word&quot
.Application.DisplayAlerts = wdAlertsNone
.Activate
.Close
End With
End If
End Sub

In fact, I open Excel but cannot run my macro yet since I am still in Word!!! So what I did was to open a new excel workbook, paste in whatever was in the embeded object, then open the addin where my macro is, and run another macro of this addin that closes word and runs the macro I want. That way I am no longer in word and I can run excel!!!