Insert values into ms sql server table from excel

[abenitez77]

I have many excel files in a directory that I want to read and update a table in ms sql server by inserting a new record with the values of a few cells in xls. the cell location in xls is the same for all excel files. I want to read the cells and insert them into my ms sql server. The worksheet name is part of the name of the file. ie filename = Claim_10008191.xls. The worksheet name = Claim#_10008191.



Here is what I have so far but can't get past this. I get an error with the dir() function (type mismatch dir()). I took this code from somewhere and changed it to fit my needs but can't get past this.



sPath = "C:\Audit Strategy\Projects\XLS to Data\"
sFile = dir(sPath + "*.xls")
while sFile <> ""
sFile = dir()
'ProcessFile(sPath + sFile)
Call ReadWkBk(sFile)
wend


Function ReadWkBk(sFile)

Dim sServer
Dim sDBName
sServer = "USATL02PRSQ70"
sDBName = "STRATA"

Dim ConnectionString
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
'dim wbIn as Workbook
Set wbIn = Workbooks.Open(sFile)

'dim rSheet1 as Range, rsheet2 as Range, rsheet3 as Range, rsheet4 as Range, rsheet5 as Range, rsheet6 as Range
dim rSheet1, rsheet2, rsheet3, rsheet4, rsheet5, rsheet6

' create the name of the worksheet, using the name of the file.
dim clmnum
clmnum = right(sFile,12)
clmnum = left(clmnum,8)
clmnum = "claim#_" & clmnum

Set rSheet1 = wbIn.Worksheets(clmnum).Range("L8:L8")
Set rSheet2 = wbIn.Worksheets(clmnum).Range("L16:L16")
Set rSheet3 = wbIn.Worksheets(clmnum).Range("L17:L17")
Set rSheet4 = wbIn.Worksheets(clmnum).Range("L18:L18")
Set rSheet5 = wbIn.Worksheets(clmnum).Range("L19:L19")
Set rSheet6 = wbIn.Worksheets(clmnum).Range("L20:L20")


Dim sSQL

' Build a SQL command
sSQL = "INSERT INTO ClaimsXLS (ClaimNo,ClaimStatus,TotalPayment,DateOfPayment,ClaimlessRebate,RequestedAmount,WarrantyAmount) VALUES ( " & _
rSheet1.value & "," & _
rSheet2.value & "," & _
rSheet3.value & "," & _
rSheet4.value & "," & _
rSheet5.value & "," & _
rSheet6.value & "," & _
" )"
' Excecute the SQL
Set Recordset = connection.Execute(sSQL)

connection.close
wbIn.Close

end Function

wscript.echo "done"

 

[PHV]

There is NO Dir() function in VBS (but its OK in VBA).
In VBS you may use the FileSystemObject stuff.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[abenitez77]

Is the rest of the code ok?

 

[PHV]

Set wbIn = Workbooks.Open(sFile)
Workbooks is a property of the "Excel.Application" object ...

Seems like you took this code from a VBA project ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[abenitez77]

Yes, can someone help me convert this to vbscript? I am new to vbscripting.

 

[PHV]

can someone help me convert this to vbscript?
Why not simply use Excel VBA to do the job ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[abenitez77]

either one would be fine..but i'm not familiar with both. Any help getting started would be appreciated.

 

[Geates]

aside from what PHV mentioned, the code is essentially vbs.
Modify the line he mentioned. This may work (I'm not to familiar with the Excel.Application object)

set objExcel = CreateObject("Excel.Application")
set wbIn = objExcel.Workbooks.Open(sFile)

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer