?
Solved

Runtime 429 when running Excel macro on a Macintosh

Posted on 2005-04-01
13
Medium Priority
?
670 Views
Last Modified: 2013-12-26
Hi!
I have made this excel macro to copy data from the sheet, open a word document and then paste the data into the word document. It works fine on the Windows XP machine but when I move it to the Mac I get "Runtime 429 Activex component can't create object -> set appWD = createObject("Word.Application.8").
I use VB6, I do not know if the Mac uses VB5 or VB6, if that is the problem how can I fix it.

-----------------------------------------------------
Sub FakturaKlar()
    Dim appWD As Word.Application
.
.
        Set appWD = CreateObject("Word.Application.8")
        appWD.Visible = True
.
.
/Dave
0
Comment
Question by:GottliebD
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 10

Expert Comment

by:edwardiii
ID: 13684647
Hi, GootliebD.

You can only use VBA on the Mac, and that is if you've got Microsoft Office for Mac installed.  Also, the pathing is critical (e.g. a Word file in Windows located at "C:\" would be equivalent to "Macintosh HD:" on the Mac. For an example of how to copy from/paste to Word documents on Macs, see this EE URL (go to the bottom for the "Code that works" comment):

     http://www.experts-exchange.com/Applications/MS_Office/Word/Q_21346739.html
0
 

Author Comment

by:GottliebD
ID: 13688689
Hi again,
Yes, the Mac has MS Office installed, and I have different filepaths for the input and output files. The example in your attached link handles files and file renaming from word. If I use that code it will give me a runtime 429 on my PC because it assumes that Microsoft word is already started.  What I want my program to do is from a macro in excel, start-up MS word, copy some ranges from the already open excel sheet to a word document file and save it with a new
filename.
The mac is a friends so I do not have the possibility to test that often. There might be the problem that the pathsnames are incorrect, but I am guessing it has to do with different versions of VB, but I am not sure, nor do I know if it is possible to upgrade the MS office installed on the Mac with VB6. The program works fine on my PC with WinXP.

/Daivd
0
 

Expert Comment

by:George7a
ID: 13688997
Hello,

I think you have too options to do that...

1) creating a word object in the Excel macro check this..
http://xtremevbtalk.com/showthread.php?t=215625&highlight=word+object

2) use a third party tool to do this ...Macro scheduler
http://www.mjtnet.com/
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 13689487
VB6 cannot be installed on a Mac.  NO version of VB (other than VBA INSIDE Excel or Word) will run on a Mac.

AW
0
 
LVL 10

Expert Comment

by:edwardiii
ID: 13690734
Ok, GottliebD.

I got the following code to work both with Win2k Pro/MS Office 2000 and Mac OSX/MS Office 2004 systems. You click a command button and the data values in the Range A1:E2 are copied, a new Word document is opened, the Excel data is copied to the Word doc, and the Word doc is then closed/saved as "Gottlieb" to the following location: "Macintosh HD: Gottlieb".  I'm guessing you'll need some way to determine if the code is being run on a Mac or On a Windows PC, but I didn't include that here.  Please note:  I wrote the VBA-Excel document on my Win2k system.  When I opened the Excel document on my Mac, it choked on the "Dim NewWordApp As Word.Application" line.  Excel VBA on the Mac seems to have problems with spacing--when I removed all spacing between coding, the VBA-Excel doc written on the PC worked flawlessly on the Mac:

Private Sub CommandButton1_Click()
    Dim NewWordApp As Word.Application
    Dim NewWordDoc As Word.Document
    Dim ExcelRange As Range
    Dim strExcelContent As String
    Set NewWordApp = New Word.Application
    Set NewWordDoc = New Word.Document
    For Each ExcelRange In Range("A1:E2")
        strExcelContent = strExcelContent & " " & ExcelRange.Value
    Next
    NewWordDoc.Content = strExcelContent
    NewWordDoc.SaveAs ("Macintosh HD:Gottlieb.doc")
    MsgBox "data copied to Word: " & NewWordDoc.Name
    NewWordApp.Quit
End Sub
0
 

Expert Comment

by:George7a
ID: 13691864
Yes .. as I said .. in option 1 & link 1  .. using a word object...
0
 

Expert Comment

by:aguppy
ID: 13695885
Just a small thing, after transfering it to the MAC , did you re-reference in the project menu the correct library, as i dont think the library on an xp platform is the same as the MAC platform !!

I had a similiar problem between 2 PC's , on had a slightly older version of word and i had to re-reference the object.

regards
Andy
0
 

Author Comment

by:GottliebD
ID: 13700053
Hi Edwardii and Andy,
Edwardii: I have slightly modified your code for my program and it works on my XP machine. It remains to be seen if I get it to work on the Mac.

Andy: Sorry for the newbie question, but how do I re-reference? Do I start the excel (without macros) on the Mac and then in the VB editor select references and just mark the libraries I need?

Thanks guys
0
 

Expert Comment

by:aguppy
ID: 13705053
Apologies GottliedD,

i presumed it was VB not VBA

Regards
0
 

Author Comment

by:GottliebD
ID: 13745054
Hi guys!
after testing it on the Mac, I have managed to get the "Set NewWordDoc = getobject (doctemplate) to work. However, I do not get the Mac to accept the
"Set NewWordApp = New Word.Application". Not sure why. Anyways, do I need to run that? When "getting" the object doctemplate it starts word and opens the document, which is what I want.  But I need to do two other things
1. copy an excel range content to the word document
2. print the word document

The following lines
    For Each ExcelRange In Range("A1:E2")
        strExcelContent = strExcelContent & " " & ExcelRange.Value
    Next
    NewWordDoc.Content = strExcelContent
 
and also this variant
            For Each ExcelRange In Range("o9:v44")
                NewWordDoc.Content = NewWordDoc.Content & ExcelRange.Value
            Next
both gives me a "runtime '-2147417851(80010105)' Method "Text" in object "range" failed" and then word crashes

Is there some other way of doing copy and paste (keeping the formatting from excel)??

The print command, I would like to use "NewWordDoc.printout(false)" , but that does not seem to work in Mac.

/David


0
 
LVL 10

Expert Comment

by:edwardiii
ID: 13745564
My Mac kept choking on the "Dim NewWordApp as Word.Application"...I had to remove every space between every line of code.  Can I see your complete modified code?  I'll test it on my Mac and go from there.
0
 

Author Comment

by:GottliebD
ID: 13746396
Hi!
this is the version that choked on "Set NewWordApp = New Word.Application. Giving me an "Automation Error 440".

Private Sub FakturaKlar()
 Dim NyttFilnamn
    Dim Temp
    Dim NewWordApp As Word.Application
    Dim NewWordDoc As Word.Document
'*********** Create filename for new "faktura"
    'Pick up the name of the template from the meny-sheet
        Sheets("Meny").Select
        Range("D41").Select
        Fakturamall = ActiveCell.FormulaR1C1
        Range("D40").Select
        Fakturafolder = ActiveCell.FormulaR1C1
        Sheets("Fakturering").Select
    ' select the cell that contains the calculated rownumber for the invoice info in "Lista fakturor"
    ' Create new filename for the new "faktura"
        Range("v9").Select
        NyttFakturanr = ActiveCell.Value
        NyttFilnamn = Fakturafolder & ":Faktura" & NyttFakturanr & ".doc"
'*********** End create filename for new "faktura"
    ' Start Word
        Set NewWordApp = New Word.Application
        NewWordApp.Visible = True
'*********** Check if faktura already exist
        If Dir(NyttFilnamn) = "" Then
            Set NewWordDoc = NewWordApp.Documents.Open(Fakturamall)
        ' Get Word to paste the faktura content from the clipboard
            Sheets("Fakturering").Select
            Range("o9:v44").Copy
            NewWordApp.Selection.Paste
        ' Save the faktura-file with the new filename
            NewWordDoc.SaveAs (NyttFilnamn)
' *********** create faktura-list post
    ' aktivate fakturering-sheet
        Sheets("Fakturering").Select
    ' Select the cell that contains the calculated rownumber for faktura post in lista fakturor
        Range("Y1").Select
        rad = ActiveCell.Value
    ' Go to Lista fakturor
        Sheets("Lista Fakturor").Select
    'Get the address to the row where the new fakturapost shall be added
        adr1 = "A" & rad
        adr2 = "G" & rad
        adr = adr1 & ":" & adr2
    ' Go to Fakturering-sheet and copy the fakturapost from cells AA1 to AG1
        Sheets("Fakturering").Select
        Range("Z1:AF1").Select
        Selection.Copy
    'Paste the faktura post to the correct row in Lista fakturor sheet
        Sheets("Lista Fakturor").Select
        Range(adr).Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'********** End of create faktura-lista post
Else
       Set NewWordDoc = NewWordApp.Documents.Open(NyttFilnamn)
End If
'*********** print faktura
    MsgBox "Faktura skapad: " & NewWordDoc.Name
 Response = MsgBox("Skriv ut faktura?", vbYesNo + vbQuestion)
 If Response = vbYes Then
   NewWordDoc.PrintOut (False)
   MsgBox "Fakturan skrivs ut"
 End If
 ' Close fakturafile and close word
       NewWordDoc.Close
       NewWordApp.Quit
'************ End Print faktura
    Sheets("Fakturering").Select
    Range("B9").Select
End Sub
0
 
LVL 10

Accepted Solution

by:
edwardiii earned 1000 total points
ID: 13749645
Ok, I did a Google search for that error and Macs, and found several articles (for example, see http://support.microsoft.com/?scid=kb;en-us;193247).  Apparently the Mac VBA needs some error trapping to deal with several types of errors, including "Automation Error 440".  Let's try using "On Error Resume Next", which basically tells VB to ignore the error and keep on processing:

     Private Sub FakturaKlar()
          On Error Resume Next

          'put all of your macro code here.



0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month12 days, 4 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question