insert excel table into SQLServer using VBA

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

insert excel table into SQLServer using VBA

Author

Topic

asafg
Starting Member

39 Posts

Posted - 2008-11-18 : 09:03:04

I want to copy a column/table or what ever from excel to SQLServer:

I found a code in the internet that lets me insert a table into a recordset and after ajusting the code...:

Sub QueryWorkSheet()
Dim rs As ADODB.Recordset
Dim ConnectionString As String

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";" & "Extended Properties=Excel 8.0;"

Dim SQL As String
Dim rngName As Name
'verifing that the range name does not exist
For Each rngName In ActiveWorkbook.Names
If rngName = "rngExcelTable" Then
ActiveWorkbook.Names("rngExcelTable").Delete
Exit For
End If
Next
' Selecting the table area (including titles)
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
' Giving a name to the range
ActiveWorkbook.Names.Add Name:="rngExcelTable", RefersToR1C1:="=data!R2C1:R" + Trim(Str(Selection.Rows.Count + 1)) + "C" + Trim(Str(Selection.Columns.Count)) 'strRNG

SQL = "SELECT * FROM rngExcelTable;"
Set rs = New ADODB.Recordset

On Error GoTo Error
rs.Open SQL, ConnectionString, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText

' Displaying the output
Range("z10").CopyFromRecordset rs
rs.Close
Set rs = Nothing
Exit Sub
Error:
Debug.Print Err.Description
If (rs.State = ObjectStateEnum.adStateOpen) Then
rs.Close
End If

Set rs = Nothing

End Sub

now I need a vba code that allowes mw to connect to SQLServer and insert rows that I get from the excel recordset:

Pseudocode:
dim conn = ...somthing with sqlserver
open conn
insert into mySQLServerTable my rs ' from excel

* sorry for posting again - but the previous topic was not accurate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 09:10:03

Any special reason for asking for vba code? you could simply use OPENROWSET or export import wizard for this.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-18 : 09:18:26

moved from script library.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

asafg
Starting Member

39 Posts

Posted - 2008-11-18 : 09:38:45

Hi visakh16,

First I want to thank you for answering all my questions during the past few days...

About your question, I have been working with VBA for some time now and I feel safer there.
I have never worked with SQLServer and if I have a job that connects a Microsoft office environment(VBA) with SQLServer environment. I prefer starting from something I understand .

About the OPENROWSET I think I tried it yesterday and if I understood correctly it is part of T-SQL script that calls an excel workbook.

At the moment I just want to get the feeling so if the OPENROWSET will work I'll be just as happy :)

Thanks again
Asaf

I even think that I tried it and failed due to the fact that the excel should be on the server

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources