-
Oct 19th, 2019, 09:40 AM
#1
[RESOLVED] Automation Error In .EXE mode only
WAS working fine. Small program using Reference to Microsoft Excel 12.0 Object Library runs fine in IDE. Made some small changes this morning (added a command button, and a few other minor changes). When I compile it, I get this error:
Attachment 171661
I've had this occur in other programs in the past...cannot recall what caused it.
Anyway, I believe it ran fine before this morning (can't recall if I actually ran it in EXE mode or not, but THINK so). After those changes (again, very minor stuff), the program STILL RUNS IN IDE MODE, but this error is received at startup in EXE mode.
Here is a snippet of what is run on startup"
Code:
Private Sub Form_Load()
LoadData
End Sub
Private Sub LoadData()
Dim x As Integer
List2.Clear
For x = 0 To 5
List3(x).Clear
Next x
Set excelApp = CreateObject("Excel.application")
Set excelWB = excelApp.Workbooks.Add
Set excelWB = excelApp.Workbooks.Open(App.Path & "\firstPrinciples.xlsx")
Set excelWS = excelWB.Worksheets(1)
rowCount = excelWS.UsedRange.Rows.Count
colCount = excelWS.UsedRange.Columns.Count
For x = 1 To rowCount
If excelWS.Cells(x, 2).Value <> "" Then
List2.AddItem (excelWS.Cells(x, 2).Value)
End If
Next x
For x = 1 To 6
List3(0).AddItem (excelWS.Cells(x, 2).Value)
Next x
For x = 7 To 14
List3(1).AddItem (excelWS.Cells(x, 2).Value)
Next x
For x = 15 To 17
List3(2).AddItem (excelWS.Cells(x, 2).Value)
Next x
For x = 18 To 25
List3(3).AddItem (excelWS.Cells(x, 2).Value)
Next x
For x = 26 To 31
List3(4).AddItem (excelWS.Cells(x, 2).Value)
Next x
For x = 32 To 37
List3(5).AddItem (excelWS.Cells(x, 2).Value)
Next x
DoEvents
excelWB.Close
Excel.Application.Quit
'set up for drag/drop
List2.OLEDropMode = 1
For x = 0 To 5
List1(x).OLEDropMode = 1
Next x
End Sub
-
Oct 19th, 2019, 09:51 AM
#2
Re: Automation Error In .EXE mode only
Sam, I'm definitely not an expert with Office automation.
My GoogleFu found hits regarding a possible registry corruption, small problem. Maybe try this search and see if the solutions work for you? Google search: createobject( excel.application ) library not registered
-
Oct 19th, 2019, 02:44 PM
#3
Re: Automation Error In .EXE mode only
Elevated vs. standard user runs?
-
Oct 19th, 2019, 03:21 PM
#4
Re: Automation Error In .EXE mode only
@dile....will look at that in a second...
@Fox....
After looking at SEVERAL posts elsewhere about Early and Late Binding (as mentioned in Google Searches), I changed my code to this: (added a bunch of msgboxes to track which line hiccupped. This now works, Late Binding, in both IDE and EXE modes. So, there surely must be something in the Registry as far as versions of Excel (I've probably had 2010 and 2013 on this particular computer). Strange thing though...on one of my other laptops, where I used Early Binding, the same error occurred, BUT NOT ON ALL APPLICATIONS--THAT is what confused me, and still does. I know I can rewrite my simple program without using Excel (I can put the data in several other formats), and may do that just because, like LaVolpe, don't do too much dabbling with Office Automation...some, but not a whole bunch.)
Code:
Option Explicit
Dim rowCount As Integer
Dim colCount As Integer
'Dim excelApp As Excel.Application
'Dim excelWB As Excel.Workbook
'Dim excelWS As Excel.Worksheet
Dim excelApp As Object
Dim excelWB As Object
Dim excelWS As Object
'for dragDrop
Dim iGrabX As Integer
Dim iGrabY As Integer
'for moving up/down in listbox
Dim str1 As String
Dim str3 As String
Dim str5 As String
Private Sub Form_Load()
LoadData
End Sub
Private Sub LoadData()
Dim x As Integer
List2.Clear
For x = 0 To 5
List3(x).Clear
Next x
MsgBox "0"
' Set excelApp = New Excel.Application
MsgBox "1"
Set excelApp = CreateObject("Excel.application")
MsgBox "2"
Set excelWB = excelApp.Workbooks.Add
MsgBox "3"
Set excelWB = excelApp.Workbooks.Open(App.Path & "\firstPrinciples.xlsx")
MsgBox "4"
Set excelWS = excelWB.Worksheets(1)
MsgBox "5"
rowCount = excelWS.UsedRange.Rows.Count
MsgBox "6"
colCount = excelWS.UsedRange.Columns.Count
MsgBox "7"
For x = 1 To rowCount
If excelWS.Cells(x, 2).Value <> "" Then
List2.AddItem (excelWS.Cells(x, 2).Value)
End If
Next x
For x = 1 To 6
List3(0).AddItem (excelWS.Cells(x, 2).Value)
Next x
For x = 7 To 14
List3(1).AddItem (excelWS.Cells(x, 2).Value)
Next x
For x = 15 To 17
List3(2).AddItem (excelWS.Cells(x, 2).Value)
Next x
For x = 18 To 25
List3(3).AddItem (excelWS.Cells(x, 2).Value)
Next x
For x = 26 To 31
List3(4).AddItem (excelWS.Cells(x, 2).Value)
Next x
For x = 32 To 37
List3(5).AddItem (excelWS.Cells(x, 2).Value)
Next x
DoEvents
MsgBox "8"
excelWB.Close
MsgBox "9"
' Excel.Application.Quit
excelApp.Quit
MsgBox "10"
'set up for drag/drop
List2.OLEDropMode = 1
For x = 0 To 5
List1(x).OLEDropMode = 1
Next x
End Sub
-
Oct 19th, 2019, 03:27 PM
#5
Re: Automation Error In .EXE mode only
@dile....
Hmmm....I changed my code to original (Early Binding) and ran the executable not elevated...got the error. Then ran it as Administrator, and it worked. Can you tell me why, and how to fix it if I want to run Early Binding?
Puzzled Sam
-
Oct 19th, 2019, 03:38 PM
#6
Re: Automation Error In .EXE mode only
You probably are using the same user account, right?
Most likely when you run elevated your program is seeing the real registry entries which are correct at this point. When you run as a standard user the program is seeing broken registry entries that were dumped into the VirtualStore by some bad install and still hanging around.
-
Oct 19th, 2019, 03:49 PM
#7
Re: Automation Error In .EXE mode only
bad install of VB, or Office?
Yes, same account.
-
Oct 19th, 2019, 03:53 PM
#8
Re: Automation Error In .EXE mode only
If I were to deploy this small app for someone else's use (as an executable, without VB6 installed), would THEY have to run it as Elevated?
If they did not have any Excel (or a different one from what I have), I would need LATE BINDING, correct? Do they NEED excel? (They probably have a version or two, I would assume, but if they did not have any, what's the answer?)
-
Oct 19th, 2019, 03:58 PM
#9
Re: Automation Error In .EXE mode only
I think either Excel was misinstalled at some point or else something went wrong on uninstall or replacement.
Nothing works without some version of Excel installed. You can't automate what isn't there. Late binding is just a way to get around the problem that MS Office applications break binary compatibility pretty much every release.
It isn't normal to require elevation for this. You just have a screwed up machine, or at least the one user profile on it.
-
Oct 19th, 2019, 04:04 PM
#10
Re: Automation Error In .EXE mode only
Thx dilettante...So, I am assuming if I deploy it (as Early Binding) that the user would have to have the exact same version of Excel installed, correct? If so, then I guess the answer is to deploy it as late binding, no?
-
Oct 19th, 2019, 06:53 PM
#11
Re: Automation Error In .EXE mode only
Early binding only works with the version the program was compiled against.
Late binding is more flexible but only works for features that a group of the versions has in common. It can also be a lot slower, but normally you are doing a fairly small number of operations infrequently when you automate so it doesn't matter.
-
Oct 19th, 2019, 07:23 PM
#12
Re: Automation Error In .EXE mode only
thx dilettante
and LaVolpe
You both got me on the right track....
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
|