copy cells from excel to SQL server

[abenitez77]

I have a directory with many xls files and I want to capture the value of 7 cells (L:8, L:12, C:16, C:17, C18, C19, C20) and insert them into a record in sql server. I took something I found to make it work for me...but it doesn't get past this line:

Set rSheet = wbIn.Worksheets("Sheet1").Range("A8:L20")




Sub FScan()

sPath = "C:\Audit Strategy\Projects\XLS to Data\"
sFile = Dir(sPath + "*.xls")
While sFile <> ""
sFile = Dir()
ReadWkBk (sPath + sFile)
Wend

End Sub

Sub ReadWkBk(sFile As String)

Dim sServer, sDBName As String
sServer = "USATL02PRSQ70"
sDBName = "STRATA"

Dim ConnectionString As String
ConnectionString = _
"Provider=SQLOLEDB;" & _
"Data Source=" + sServer + ";" & _
"Initial Catalog=" + sDBName + ";" & _
"Integrated Security=SSPI"

' Connection assumes you have permission to connect to the named database as part of an AD
' group. Early binding assumes you have references set to the appropriate active X lib
Set Connection = CreateObject("ADODB.Connection")
Connection.Open ConnectionString

Dim wbIn As Workbook
Set wbIn = Workbooks.Open(sFile)
sFile = Right(sFile, 12)
sFile = Left(sFile, 8)
sFile = "Claim#_" + sFile

Dim rSheet As Range
Set rSheet = wbIn.Worksheets("Sheet1").Range("A8:L20")
Dim iRow As Integer
iRow = 1
Dim sSQL As String

' Process rows while there is a value in the first column
While rSheet(iRow, 1).Value <> ""
' Build a SQL command
sSQL = "INSERT INTO ClaimsXLS (ClaimNo,ClaimStatus,TotalPayment,DateOfPayment,ClaimlessRebate,RequestedAmount,WarrantyAmount) VALUES ( " + _
rSheet(L8, L8).Value + "," + _
rSheet(L12, L12).Value + "," + _
rSheet(C16, C16).Value + "," + _
rSheet(C17, C17).Value + "," + _
rSheet(C18, C18).Value + "," + _
rSheet(C19, C19).Value + "," + _
rSheet(C20, C20).Value + _
" )"
' Excecute the SQL
Set Recordset = Connection.Execute(sSQL, recs, CommandTypeEnum.adCmdText)
' Move to next row
iRow = iRow + 1
Wend

Connection.Close
wbIn.Close

End Sub