How to fix CLSID {00024500-0000-0000-C000-000000000046} error when using excel component in background code?

We have  a windows Service application which uses Excel Application Object. It used to work on some machines but not all, the error which I am getting is:

Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005.

After some struggling I found a solution and it works, solution is:

Solution 1:

  • Create directory C:\Windows\SysWOW64\config\systemprofile\Desktop (for 64 bit Windows) or C:\Windows\System32\config\systemprofile\Desktop (for 32 bit Windows) and give full permission
  • Run program “dcomcnfg” Go to “Console Root/Component Services/Computer/My Computer/DCOM Config/” Look up Microsoft Excel and choose properties.
  • Go to Security and Launch and activation permissions->customize->add network, network service, administrator, interactive, system and everyone and give full access
  • Now go to “Identity” and select “The interactive user”.
  • Now go to “Console Root/Component Services/Computer/My Computer”,  right click Properties of “My Computer”, give full access permission to all users.
  • Solution 2:
    <system.web>
    <identity impersonate=”true” userName=”domain\jack” password=”********” />
    </system.web>

 

How to display negative time in excel cell

While working with excel application I found that if we find difference between 2 times is negative then excel doesn’t display time, it shows “##########” instead of values. For positive difference we don’t have any issue.

There are 2 ways to handle this, using 1904 date system and other using custom formula.

To change the date system you need open Advance options of excel and enable the 1904 date system box.

Use the formula:

=IF(D2-A2<0, "-" & TEXT(ABS(D2-A2),"hh:mm"), D2-A2)

 

 

remove duplicate values from a excel cell

Microsoft Excel is used by everyone in the world for all data related things, some time we have duplicate cell contents in rows or in columns.

To remove these duplicate values from the sheet we already have a feature “Remove Duplicates” in latest version of the excel application. But if we have some duplicate values in a single cell then this feature will not work, we need some another alternative, either we delete manually or can create a Macro / Addins / Custom Function.

Manual process will not help because it required lot of concentration,? To resolved that I have created a Addins which we can integrate in excel application and access this custom function like other we generally used.

Steps to create addins:
– Go to View – > Macro
– Define Name “RemoveDuplicatesFromCell” and click on create Button
– Add the following code at Module1 file
– Save the file as RemoveDuplicatesFromCell.xla
[vbnet]
Function RemoveDuplicatesFromCell(txt As String, Optional delim As String = ” “) As String
Dim x
With CreateObject(“Scripting.Dictionary”)
.CompareMode = vbTextCompare
For Each x In Split(txt, delim)
If Trim(x) <> “” And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
Next
If .Count > 0 Then RemoveDuplicatesFromCell= Join(.keys, delim)
End With
End Function
[/vbnet]