Thursday, October 25, 2007

BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO) when Excel is opened

For Excel Connection
'IMEX = 1; = type= text
'IMEX = 2; = type= Mixed
'change the registry...
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows = 0 - it will check the data type for every field
'but at this momment we can not change using MAXSCANROWS=0 - it doesn't work
Dim excelConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileFullName & ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'"

When you retrieve a Microsoft ActiveX Data Objects (ADO) Recordset from an Excel worksheet that is open in Excel, a memory leak occurs in the Excel process. Repeated queries may eventually cause Excel to run out of memory and raise an error, or cause Excel to stop responding.

The memory used by the ADO queries cannot be reclaimed by closing and releasing the ADO objects. The only way to release the memory is to quit Excel.

If possible, query the Excel worksheet only while the file is not open in Excel.

If the worksheet must remain open (for example, to allow dynamic recalculation of worksheet values on an ongoing basis) use one of the following methods to work around the behavior:

Method 1

Use the SELECT INTO syntax of the Jet OLE DB Provider to export the Excel data to a new worksheet. For additional information about using the SELECT INTO syntax to export data, click the following article number to view the article in the Microsoft Knowledge Base:
295646 ( How To Transfer Data from ADO Data Source to Excel with ADO

Method 2

Use the SaveCopyAs method of the Workbook object in the Excel object model to programmatically save the open Excel file under a new name. You can then query the copy of the file that you previously saved under a new name from the ADO application.

Method 3

save as Excel using Excel.Application . SaveAs to Excel CSV file and Read that CSV file using OLEDB Connection

Provider=Microsoft.Jet.OLEDB.4.0;Data Source='FOLDER PATH';Extended Properties='text;HDR=Yes;IMEX=1;FMT=Delimited'

and using select * from fileName.

Adding Permission to Excel COM Object

If you run the service using Network Service Account then you need to add permission to Microsoft Excel COM Object because by default Microsoft Excel as a COM object can only activated by the following accounts:

  • Administrator
  • System
  • Interactive

Configure DCOM

  • Go to the Start-Run menu item.
  • Type in "DCOMCNFG" and hit enter.
  • This should load the "Component Services" MMC (you can also load from Administrative Tools - Component Services"
  • Expand "Component Services"
  • Expand "Computers"
  • Expand "My Computer"
  • Select the "DCOM Config" item
  • Select the "Microsoft Excel Application" item.
  • Right click and select Properties
  • Add Permission to network service or ASP Net account

EventLog.WriteEvent in Windows Service or ASPNET

If you get this error in writting EventLog.WriteEvent in Windows Service or ASP.Net.
"The source was not found, but some or all event logs could not be searched. Inaccessible logs: Security."

You need to create your source manually. because the process can not created for u.

To add your source manually,
Open Regedit and add your source inside.

After that you will be able to Write event log freely.