Runtime 429 when running Excel macro on a Macintosh

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
GottliebDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

edwardiiiCommented:
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
GottliebDAuthor Commented:
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
George7aCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Arthur_WoodCommented:
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
edwardiiiCommented:
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
George7aCommented:
Yes .. as I said .. in option 1 & link 1  .. using a word object...
0
aguppyCommented:
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
GottliebDAuthor Commented:
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
aguppyCommented:
Apologies GottliedD,

i presumed it was VB not VBA

Regards
0
GottliebDAuthor Commented:
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
edwardiiiCommented:
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
GottliebDAuthor Commented:
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
edwardiiiCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.