Friday, September 4, 2015

Excel VBA - Run Time Automation Error


I have this below VBA code (pretty common found on this site itself as well as other places on the net to find SHA hash in VBA) that works just fine in Excel 2013 on Windows 7 32.


However the same code when run in Excel 2003 SP3 on Windows 7 32 gives Run Time Automation Error at line Set asc1 = CreateObject("System.Text.UTF8Encoding")


I am unclear why it is failing in Excel 2003. Assuming it has something to do with dot net framework I re-installed dot Net 4.0 while 3.5.1 is already part of Windows 7 and Feature already enabled.


There is very similar post on this site here but even it does not have a solution. What else should I troubleshoot further? Could this be something to do with missing or corrupt components / libraries or anything related?


Thanks.


 Public Function SHA1(str1)
Dim asc1 As Object
Dim enc1 As Object
Dim bytes, outstr, pos
Set asc1 = CreateObject("System.Text.UTF8Encoding")
Set enc1 = CreateObject("System.Security.Cryptography.SHA1CryptoServiceProvider")
bytes = asc1.GetBytes_4(str1)
bytes = enc1.ComputeHash_2((bytes))
outstr = ""
For pos = 1 To LenB(bytes)
outstr = outstr & LCase(Right("0" & Hex(AscB(MidB(bytes, pos, 1))), 2))
Next
SHA1 = outstr
Set asc1 = Nothing
Set enc1 = Nothing
End Function
Private Sub CommandButton1_Click()
Dim txt
txt = TextBox1.Text
TextBox2.Text = SHA1(txt)
End Sub

enter image description here


Answer



A similar issue was discussed at https://stackoverflow.com/questions/375457/cant-instantiate-a-com-object-written-in-c-sharp-from-vba-vb6-ok on Stackoverflow.com.
The user got same "automation error" 0x80131700 in Excel VBA.


Taking inputs from the posted answers therein, I installed Office 2003 Update KB907417 from http://www.microsoft.com/en-us/download/details.aspx?id=10624 and the issue is resolved.


No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...