How to open Excel file / find record in SQL database ?

[Ling]

1. What are the avaliable methods to open and read Excel file (i.e. csv, xls) in VB? I need to write a routine to update rows from excel (CSV format) to tables in SQL database.

2. What are the avaliable methods to do a quick search in a ADO recordset?

My routine needs to read from a file with over 10000 records, then add to a table if record not exists in SQL table else update existing record.

"Seek" method is not supported in SQL database, I have tried using Find method:
tbl_xxx.Find "IndexField = " & SearchData

But processing is very slow, take abt 1 hr. Anyone can help?

[Iouri]

1. What are the avaliable methods to open and read Excel file (i.e. csv, xls) in VB? I need to write a routine to update rows from excel (CSV format) to tables in SQL database.

Dim xlsApp As Object

Set xlsApp = CreateObject("excel.application")
xlsApp.Visible = False
xlsApp.workbooks.Open "c:\MyPath\MyFile.XLS"

msgbox xlApp.Cells(1, 1)
xlsApp.Close
Set xlsApp = Nothing

'===another way======
' Side Effects:If you use this function to retrieve massive amounts of data it
' is best to have excel open when doing so.
' It takes about 75% longer to retrieve information when excel is closed
'


Function getExcel(rowval As Integer, columnval As String, excelfile As String)
Dim excelSheet As Object 'Excel Sheet object
'Create an instance of Excel by file name
Set excelSheet = CreateObject(excelfile)
mycell$ = columnval & rowval
getExcel = excelSheet.activesheet.range(mycell$).Value
'Retrieve the result using the cell by row and column
Set excelSheet = Nothing'release object
End Function



2. What are the avaliable methods to do a quick search in a ADO recordset?

The fastest way to search is SQL

sSQL = "select * from YourTable where ...(Your Criteria)"
rs.Open sSQL,Connection,adOpenDynamic,adLockOptimistic





Iouri Boutchkine
iouri@hotsheet.com