Export table to SQL

[Guest]

I want to write some VBA code to export a worksheet in excel to a table in
SQL. Is that possible? Thanks a lot!

 

[Guest]

Sure it is possible. You need to understand ADO, SQL (enough to create a
query) and have the ODBC drivers.

I can give you some code that I Use to update a table in Access. The only
difference is the Connection...

If you want the code let me know...

 

[Guest]

Thanks a lot, I almost thought it was possible. I know how to run a query in
sql and return the results to excel, but don't know the opposite. If you
could show me, it will be great.

 

[Jamie Collins]

I know how to run a query in
sql and return the results to excel, but don't know the opposite.

Are you using 'SQL' as a contraction for Microsoft SQL Server?

I think by mentioning ODBC drivers, Jim probably meant something like
this:

Sub just_four_lines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyWorkbook.xls;" & _
"Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"
con.Execute _
"INSERT INTO [ODBC;Driver={SQL Server};" & _
"SERVER=MYSERVER;DATABASE=MyDatabase;" & _
"UID=;Pwd=;].MyTable (data_col)" & _
" SELECT MyMemoCol AS data_col FROM [MySheet$];"
End Sub

However, it can be done with OLE DB alone e.g.

Sub four_lines_more()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=SQLOLEDB.1;Data Source=MYSERVER;" & _
"Initial Catalog=MyDatabase;User Id=;password=;"
con.Execute _
"INSERT INTO MyTable (data_col) " & _
" SELECT data_col FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & _
"'Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyWorkbook.xls'," & _
"'SELECT MyMemoCol AS data_col FROM [MySheet$]');"
End Sub

Jamie.

--