Excel VBA出现Run-time error 429（ActiveX无法创建对象）求助

Hey there, let's break down why you're hitting that Run-time Error 429 and get your data copy-paste routine working smoothly.

First: The Direct Cause of Error 429 #

Your line Set wbXL = CreateObject("D:\project\Ruby\Live info Ruby.xls") is using CreateObject incorrectly. This method expects a ProgID (like "Excel.Application") to create an ActiveX component, not a file path. The system can't find an ActiveX object named after your file path, hence the 429 error.

Refactored & Fixed Code #

I've rewritten your code to avoid error-prone Select/Activate calls (these are unstable and inefficient) and use proper object references. This will also resolve the 429 error:

Sub GetSheetInfo()
    Dim sourceWb As Workbook
    Dim targetWb As Workbook
    Dim sourceWs As Worksheet
    Dim targetWs As Worksheet
    
    ' Set reference to your macro-enabled workbook (the one running this code)
    Set targetWb = ThisWorkbook
    Set targetWs = targetWb.Sheets("Frequency")
    
    ' Open the source workbook correctly (no CreateObject needed here)
    On Error Resume Next ' Catch cases where the file can't be opened
    Set sourceWb = Workbooks.Open("D:\project\Ruby\Live info Ruby.xls")
    On Error GoTo 0
    
    ' Check if the source workbook opened successfully
    If sourceWb Is Nothing Then
        MsgBox "Failed to open the source file! Please verify the file path and ensure it's not locked by another program.", vbExclamation
        Exit Sub
    End If
    
    ' Set reference to the source worksheet
    On Error Resume Next
    Set sourceWs = sourceWb.Sheets("Ruby - 2020")
    On Error GoTo 0
    
    If sourceWs Is Nothing Then
        MsgBox "Worksheet 'Ruby - 2020' not found in the source file!", vbExclamation
        sourceWb.Close SaveChanges:=False
        Exit Sub
    End If
    
    ' Clear target rows without using Select
    targetWs.Range("A9:H800").EntireRow.Delete
    
    ' Copy and paste data directly (no Activate/Select required)
    sourceWs.Range("A155:G950").Copy
    With targetWs.Range("A9")
        .PasteSpecial Paste:=xlPasteAllUsingSourceTheme
        .PasteSpecial Paste:=xlPasteValues
    End With
    
    ' Clean up: close source workbook and clear clipboard
    sourceWb.Close SaveChanges:=False
    Application.CutCopyMode = False
    
    MsgBox "Data copied successfully!", vbInformation
End Sub

Additional Troubleshooting for Error 429 (Since You Tried Basic Fixes) #

If you still hit the error after fixing the code, try these deeper checks:

• Re-register Excel's COM Component: Open Command Prompt as Administrator, then run (adjust the path for your Office version; Office16 is for 2016/2019/365):

  regsvr32.exe "C:\Program Files\Microsoft Office\Root\Office16\EXCEL.EXE"
  

• Verify Office-System Bitness Match: Ensure you're running 64-bit Office on a 64-bit system, or 32-bit Office on a 32-bit system. Mixed bitness often causes ActiveX errors.
• Disable COM Add-Ins: Open Excel → File → Options → Add-Ins → Manage: COM Add-Ins → Go. Disable all add-ins, restart Excel, and test your code. Add-ins can conflict with ActiveX components.
• Check for Missing VBA References: Open the VBA Editor → Tools → References. Look for any entries marked "MISSING"—uncheck them, or browse to locate the correct reference file if needed.
• Ensure File Isn't Locked: Make sure the source Excel file isn't open in another program, set to read-only, or protected by file permissions.

内容的提问来源于stack exchange，提问作者Noah