Here is the code...

'create connection

Set cnn = New ADODB.Connection

'set connection properties and open

With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & FileName & ";" & _
"Extended Properties=Excel 8.0;"
.Open
End With

'create a recordset with the spreadsheet shema so i can get the sheet name
(table name), store table name in wsName, close recordset

Set objRS = cnn.OpenSchema(adSchemaTables)
wsName = objRS.Fields.Item("table_name")
objRS.Close
Set objRS = Nothing

'build SQL string with spreadsheet headers and wsName (table name)

strSQL = "select [Beg], [End], [Length], [Type], [InsightFieldName],
[FieldDescription], [FieldCount], [SortOrder] from [" & wsName & "]"

'open recordset with SQL string

Set objRS = cnn.Execute(strSQL)

'create cnn2 for SQL server connection, create command object, set
properties for cnn2 and command object, insert data from spreadsheet into sql
server with command oject, close cnn, close recordset

Set cnn2 = New ADODB.Connection
Set objComm = New ADODB.Command

strConnect = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=MDTOutputA;Data Source=mdtdpp01;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for
Data=False;Tag with column collation when possible=False"

cnn2.Open strConnect

objComm.ActiveConnection = cnn2
objComm.CommandType = adCmdText

Do While Not objRS.EOF

strSQL = "INSERT INTO " & TblName & " ([Beg], [End], [Length], [Type],
[InsightFieldName], [FieldDescription], [FieldCount], [SortOrder]) VALUES ('"
& objRS("Beg") & "', '" & objRS("End") & "', '" & objRS("Length") & "', '" &
objRS("Type") & "', '" & objRS("InsightFieldName") & "', '" &
objRS("FieldDescription") & "', '" & objRS("FieldCount") & "', '" &
objRS("SortOrder") & "')"

objComm.CommandText = strSQL
objComm.Execute

objRS.MoveNext

Loop

objRS.Close
cnn.Close

'NOTE: just the 'end' field for the last row is not inserted correctly.
the end field turns out to be an empty string (""). here is a sample of the
spreadsheet (please let me know if there is a better way to insert this (the
fields are from Beg to SortOrder, it did not paste onto this box very well)...

Beg End Length Type InsightFieldName FieldDescription FieldCount SortOrder
1 12 12 A/N Keycode Keycode x
13 22 10 A/N MemAcctNum Membership Account Number
23 30 8 A/N NATitle Title
31 44 14 A/N NAFname First Name & Middle Initial
45 60 16 A/N NALname Last Name
61 64 4 A/N NASuffix Suffix
65 104 40 A/N NAAddr1 Address Line 1
105 132 28 A/N NAAddr2 Address Line 2
133 150 18 A/N NACity City
151 152 2 A/N State State x
153 157 5 A/N Zip Zip 1
158 161 4 A/N ZipPlus4 Zip Plus 4 2
162 163 2 A/N DPBarcode Delivery Point Barcode
164 167 4 A/N CRoute Carrier Route
168 200 33 X NA2 Filler


""Peter Huang" [MSFT]" wrote:

Hi

Thank you for your reply.
I think it is better to include the test excel file together with the code
which will help us to reproduce the sample.
Thanks!

Best regards,

Perter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.