H!

I need to export Excel worksheet data into a SQL Server table. The Excel
worksheet has 5 cells in it and I need to insert 5 cells data into a
text field of a table. Is that possible? All I am looking for is ActiveX
code. I used this article (see the link) to set up my script (just to
make it work at this time) but it is not working. Can you offer any
help?

http://groups-beta.google.com/group/...rver.dts/brows
e_thread/thread/e8363a1386d25795/aa559b9041326488?q=Excel+Workbook+Activ
eX+Script+sql+server&rnum=21#aa559b9041326488

Thanks for your time.


... this is the script I am currently using

Function Main()


Dim SQLConn
Dim i,sFolder, fso, f, f1, fc, s

set SQLConn = createobject("ADODB.Connection")
SQLConn.connectionstring = "driver={SQL Server};
server=NAM;trusted_connection=True;database=Suppor t"
SQLConn.open

sFolder = "\\nas\filer_a1\"
sTemp = "\\nas\filer_a1\Reports\TMP_TaxWaiverDetail.xl s"

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(sFolder)

if fso.FolderExists(sTemp) then
fso.DeleteFolder sTemp, true
End If

' fso.CreateFolder sTemp

Set fc = f.Files
For Each f1 in fc
Set XL = CreateObject("Excel.Application")
XL.visible = false
Set MyWorkbook = XL.Workbooks.Open(sFolder & f1.name)
XL.ActiveWorkbook.SaveAs(sFolder & "\Reports" & f1.name)
Count = XL.ActiveWorkbook.Worksheets("Sheet1").UsedRange.C olumns.Count
CheckTable = "if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[" & Replace(f1.name,".xls","") & "] ') and
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[" &
Replace(f1.name,".xls","") & "]"

SQLConn.Execute(CheckTable)
Set CheckTable = Nothing
SQLQuery = "Create Table [dbo].[" & Replace(f1.name,".xls","") & "] ("
SQLSelectQuery = "Select "
For i = 65 to (Count + 64)
XL.Cells.Range(chr(i) & "1", chr(i) & "1").Select
If len(XL.ActiveCell.Value) > 0 then
SQLQuery = SQLQuery & "[" & XL.ActiveCell.Value & "] [varchar]
(255)"
SQLSelectQuery = SQLSelectQuery & "`" & XL.ActiveCell.Value & "` "
If i < Count + 64 then
SQLQuery = SQLQuery & ", "
SQLSelectQuery = SQLSelectQuery & ", "
End If
End If
Next


SQLQuery = SQLQuery & ") on [Primary]"
SQLSelectQuery = SQLSelectQuery & " from `Sheet1$`"
SQLConn.Execute(SQLQuery) 'Create SQL Table strFileName = sFolder &
f1.name
Set XLConn = CreateObject("ADODB.Connection*")
ConnStr = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq="
& (sFolder & f1.name) & ";DefaultDir=" & sFolder & ""
XLConn.Open (ConnStr)
Set XLRS = XLConn.Execute(Replace(SQLSelectQuery,"'","''"))
While Not XLRS.EOF
SQLQuery = "Insert Into [" & Replace(f1.name,".xls","") & "] ("
For i = 65 to (Count + 64)
XL.Cells.Range(chr(i) & "1", chr(i) & "1").Select
If len(XL.ActiveCell.Value) > 0 then
SQLQuery = SQLQuery & "[" & XL.ActiveCell.Value & "]"
If i < Count + 64 then
SQLQuery = SQLQuery & ", "
End If
End If
Next
SQLQuery = SQLQuery & ") values ("
For i = 65 to (Count + 64)
XL.Cells.Range(chr(i) & "1", chr(i) & "1").Select
If len(XL.ActiveCell.Value) > 0 then
If not isNull(XLRS.Fields.Item(XL.ActiveCell.Value).Value ) then
SQLQuery = SQLQuery & "'" &
Replace(XLRS.Fields.Item(XL.ActiveCell.Value).Valu e,"'","''") & "'"
Else
SQLQuery = SQLQuery & "''"
End If
If i < Count + 64 then
SQLQuery = SQLQuery & ", "
End If
End If
Next
SQLQuery = SQLQuery & ")"
SQLConn. Execute(SQLQuery)


XLRS.MoveNext()
Wend
XLRS.Close()
Set XLRS = Nothing
XLConn.Close
Set XLConn = Nothing
MyWorkbook.Close
Set MyWorkbook = Nothing
XL.Quit
Set XL = Nothing


Next
SQLConn.Close
Set SQLConn = Nothing



Main = DTSTaskExecResult_Success
End Function

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Why don't you just use an Excel connection as source and an SQL Server
connection as destination?

That way you could simply use a simple data transformation task.

HTH,
Axel Dahmen
www.sportbootcharter.com


------------
"Test Test" <farooqhs_2000 (AT) yahoo (DOT) com> schrieb im Newsbeitrag
news:eEz#ZhLMFHA.3512 (AT) TK2MSFTNGP15 (DOT) phx.gbl...