Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Runtime 429 when running Excel macro on a Macintosh

Posted on 2005-04-01
13
Medium Priority
?
782 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

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.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

581 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