For Excel Connection
'HDR=NO; = HEADER
'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 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.
'HDR=NO; = HEADER
'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 (http://support.microsoft.com/kb/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.
No comments:
Post a Comment