-
Jun 27th, 2012, 12:19 PM
#1
Thread Starter
Addicted Member
[RESOLVED] Runtime Error 429 - ActiveX Component Can't Create Object
Dear Reader,
I'm attempting to turn my program into Late Bound and have some problems. I now get this error message when attempting to load an Excel Worksheet into a VSFlexGrid.
Runtime Error 429 - ActiveX Component Can't Create Object
All the research I've done says to reregister Excel by using Command Prompt with \regserver If that is the case I'd love some information on how to do that. I can't find the .exe of the Excel I use. I think I may have to register the entire Microsoft Office considering it came in a pack for my job. Anyways, following the Runtime Error 429, when Debugged, it points to this line
Code:
Set lsh = CreateObject("Excel.Worksheet")
Any thoughts?
P.S. Educate me don't tell me what to do, please
Thank you for taking the time to read this.
-
Jun 27th, 2012, 12:26 PM
#2
Re: Runtime Error 429 - ActiveX Component Can't Create Object
You need to create Excel.Application first and then add Workbook - Worksheets are part of workbook.
-
Jun 27th, 2012, 12:41 PM
#3
Thread Starter
Addicted Member
Re: Runtime Error 429 - ActiveX Component Can't Create Object
Alright that makes sense. Thank you for your input. However, now I'm getting the error message...
Code:
Set lBook = CreateObject("Excel.Workbook")
That's the line it points to with the same error message. Before that I also did this
Code:
Dim lbook As Object
Thoughts?
-
Jun 27th, 2012, 01:05 PM
#4
Re: Runtime Error 429 - ActiveX Component Can't Create Object
You shouldn't be using CreateObject for that either, only for the Application.
I recommend you take another look at what my Excel tutorial (link in my signature) says for converting to late bound... it explains all of the steps you need, and you are currently adding a few extra random ones that wont work.
-
Jun 27th, 2012, 01:10 PM
#5
Thread Starter
Addicted Member
Re: Runtime Error 429 - ActiveX Component Can't Create Object
What would I set book to then?
-
Jun 27th, 2012, 01:17 PM
#6
Re: Runtime Error 429 - ActiveX Component Can't Create Object
Whatever you did before... because the section about converting to late binding doesn't tell you to change it.
-
Jun 27th, 2012, 01:24 PM
#7
Re: Runtime Error 429 - ActiveX Component Can't Create Object
Originally Posted by johndmingione
Alright that makes sense. Thank you for your input. However, now I'm getting the error message...
Code:
Set lBook = CreateObject("Excel.Workbook")
That's the line it points to with the same error message. Before that I also did this
Code:
Dim lbook As Object
Thoughts?
Once you create Excel.Application object you can add workbook to Workbooks collection:
Code:
Option Explicit
Private Sub Command1_Click()
Dim objExcel As Object
Dim objBook As Object
Set objExcel = CreateObject("Excel.Application")
Set objBook = objExcel.Workbooks.Add
objExcel.Visible = True
Set objExcel = Nothing
Set objBook = Nothing
End Sub
-
Jun 27th, 2012, 01:28 PM
#8
Thread Starter
Addicted Member
Re: Runtime Error 429 - ActiveX Component Can't Create Object
Yeah I got that far I'm just stuck on what to turn Range into. I put it as something that's incorrect I forget what I had it set to before. This is how I have it:
[CODE]Dim oXLApp As Object
Dim oxLBook As Object
Dim oxLSh As Object
Dim oXLRange As Object
Set oXLApp = CreateObject("Excel.Application")
Set oxLBook = lApp.Workbooks.Add
Set oxLSh = lBook.Worksheets(1)
Set oXLRange = lBook.Sheets.Range[\CODE]
And the range part is wrong.
-
Jun 27th, 2012, 01:30 PM
#9
Re: Runtime Error 429 - ActiveX Component Can't Create Object
You need to tell it which range, perhaps something like:
Code:
Set oXLRange = lBook.Sheets.Range("D23")
..but obviously we can't tell what your code was before, so we don't know what it should be.
-
Jun 27th, 2012, 01:31 PM
#10
Thread Starter
Addicted Member
Re: Runtime Error 429 - ActiveX Component Can't Create Object
Ah. Makes sense. Is there a way to make it so the Range is unlimited? Like an auto-detect of sorts?
-
Jun 27th, 2012, 01:40 PM
#11
Re: Runtime Error 429 - ActiveX Component Can't Create Object
Yes:
Code:
Set oXLRange = lBook.Sheets.UsedRange
..but note that under some circumstances it wont update after changes you have made (such as adding/removing rows).
-
Jun 27th, 2012, 01:43 PM
#12
Thread Starter
Addicted Member
Re: Runtime Error 429 - ActiveX Component Can't Create Object
-
Jun 27th, 2012, 05:25 PM
#13
Thread Starter
Addicted Member
Re: Runtime Error 429 - ActiveX Component Can't Create Object
So an awesome thing happened...my computer froze and I lost my work.
So I've been re-doing everything from this morning. Now I'm getting the same 429 but I can't figure out what to change, add, delete, or edit.
Code:
FileName = App.Path & "\PhraseList.xlsx"
Set oXLApp = CreateObject("Excel.Applcation")
oXLApp.Visible = False
Set oXLBook = oXLApp.Workbooks.Open(FileName)
Set oXLSheet = oXLBook.Worksheets("Symptoms")
With oXLSheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Not IsEmpty(.cells(i, "A")) Then
lstSymptoms.AddItem .cells(i, "A")
End If
Next i
End With
oXLBook.Close savechanges:=False
oXLApp.Quit
Set oXLApp = Nothing
Set oXLBook = Nothing
Set oXLSheet = Nothing
The line
Set oXLApp = CreateObject("Excel.Application")
Is giving me the error message 429. What am I doing wrong?
-
Jun 28th, 2012, 06:01 AM
#14
Re: Runtime Error 429 - ActiveX Component Can't Create Object
Check your spelling:
Set oXLApp = CreateObject("Excel. Applcation")
-
Jun 28th, 2012, 12:02 PM
#15
Thread Starter
Addicted Member
Re: Runtime Error 429 - ActiveX Component Can't Create Object
Yeah I noticed that too. Silly mistake. I still get an error 91 message, object variable or block with variable not set. it points to "exApp.Visible = False" but I defined and set exApp. What am I doing wrong?
-
Jun 28th, 2012, 12:56 PM
#16
Re: Runtime Error 429 - ActiveX Component Can't Create Object
Can you post your complete code (not just pieces)?
-
Jun 28th, 2012, 01:42 PM
#17
Thread Starter
Addicted Member
Re: Runtime Error 429 - ActiveX Component Can't Create Object
Ignore this one. Look below. I put in half the code instead of all of it. And I can't delete this I can't just edit it.
-
Jun 28th, 2012, 01:43 PM
#18
Thread Starter
Addicted Member
Re: Runtime Error 429 - ActiveX Component Can't Create Object
Code:
'DTCS List
FileName = App.Path & "\PhraseList.xlsx"
Set exApp = CreateObject("Excel.Application")
exApp.Visible = False
Set exBook = exApp.Workbooks.Open(FileName)
Set exSheet = exBook.worksheets("DTCS")
With exSheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Not IsEmpty(.cells(i, "A")) Then
lstDTCS.AddItem .cells(i, "A")
End If
Next i
End With
exBook.Close savechanges:=False
exApp.Quit
Set exApp = Nothing
Set exBook = Nothing
Set exSheet = Nothing
'Symptoms to Tip Title
FileName = App.Path & "\PhraseList.xlsx"
Set exApp = CreateObject("Excel.Application")
exApp.Visible = False
Set exBook = exApp.Workbooks.Open(FileName)
Set exSheet = exBook.worksheets("Symptoms")
With exSheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Not IsEmpty(.cells(i, "A")) Then
lstSymptomOne.AddItem .cells(i, "A")
End If
Next i
End With
exBook.Close savechanges:=False
exApp.Quit
Set exApp = Nothing
Set exBook = Nothing
Set exSheet = Nothing
'Symptoms to Complaint
FileName = App.Path & "\PhraseList.xlsx"
Set exApp = CreateObject("Excel.Application")
exApp.Visible = False
Set exBook = exApp.Workbooks.Open(FileName)
Set exSheet = exBook.worksheets("Complaints")
With exSheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Not IsEmpty(.cells(i, "A")) Then
lstComplaint.AddItem .cells(i, "A")
End If
Next i
End With
exBook.Close savechanges:=False
exApp.Quit
Set exApp = Nothing
Set exBook = Nothing
Set exSheet = Nothing
'Symptoms to Both
FileName = App.Path & "\PhraseList.xlsx"
Set exApp = CreateObject("Excel.Applcation")
exApp.Visible = True
Set exBook = exApp.Workbooks.Open(FileName)
Set exSheet = exBook.worksheets("Symptoms")
With exSheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Not IsEmpty(.cells(i, "A")) Then
lstSymptoms.AddItem .cells(i, "A")
End If
Next i
End With
exBook.Close savechanges:=False
exApp.Quit
Set exApp = Nothing
Set exBook = Nothing
Set exSheet = Nothing
'Cause List
FileName = App.Path & "\PhraseList.xlsx"
Set exApp = CreateObject("Excel.Application")
exApp.Visible = False
Set exBook = exApp.Workbooks.Open(FileName)
Set exSheet = exBook.worksheets("Causes")
With exSheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Not IsEmpty(.cells(i, "A")) Then
lstCause.AddItem .cells(i, "A")
End If
Next i
End With
exBook.Close savechanges:=False
exApp.Quit
Set exApp = Nothing
Set exBook = Nothing
Set exSheet = Nothing
'Corrections List
FileName = App.Path & "\PhraseList.xlsx"
exApp.Visible = False
Set exBook = exApp.Workbooks.Open(FileName)
Set exSheet = exBook.worksheets("Corrections")
With exSheet
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 1 To LastRow
If Not IsEmpty(.cells(i, "A")) Then
lstCorrection.AddItem .cells(i, "A")
End If
Next i
End With
exBook.Close savechanges:=False
exApp.Quit
Set exApp = Nothing
Set exBook = Nothing
Set exSheet = Nothing
End Sub
-
Jun 28th, 2012, 02:04 PM
#19
Re: Runtime Error 429 - ActiveX Component Can't Create Object
Please keep each issue to one thread, rather than posting the same issue in 3 different threads - it always wastes peoples time, and even more than usual when have been told what the problem was over an hour before:
http://www.vbforums.com/showthread.p...44#post4191744
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|