export Excel Sql table column name & tableName as sheetName.

Viv,

You can achieve that using Excel VBA Macro. In the Excel sheet cell A1 should have the database name and table name will be taken automatically from the active excel sheet. So make sure that you have opened the correct excel worksheet before executing the macro. Apart from that the column name should start from A2 cell. Here in this example i have just mentioned table column names from the excel sheet cells starting from A2 : E2.

Create a macro as shown below.

----------------------------------------------------

Private Sub CreateDatabaseFromExcel()

Dim dbConnectStr As String

Dim Catalog As Object

Dim cnt As ADODB.Connection

Dim dbPath As String

Dim tblName As String

'Set database name in the Excel Sheet

dbPath = ActiveSheet.Range("A1").Value 'Database Name

tblName = ActiveSheet.Name 'Table Name

dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"

'Create new database using name entered in Excel Cell ("A1")

Set Catalog = CreateObject("ADOX.Catalog")

Catalog.Create dbConnectStr

Set Catalog = Nothing

'Connect to database and insert a new table

Set cnt = New ADODB.Connection

With cnt

.Open dbConnectStr

.Execute "CREATE TABLE " & tblName & " ([" & ActiveSheet.Range("A2").Value & "] text(50) WITH Compression, " & _

"[" & ActiveSheet.Range("B2").Value & "] text(9) WITH Compression, " & _

"[" & ActiveSheet.Range("C2").Value & "] text(10) WITH Compression, " & _

"[" & ActiveSheet.Range("D2").Value & "] text(150) WITH Compression, " & _

"[" & ActiveSheet.Range("E2").Value & "] text(50) WITH Compression, "

End With

Set cnt = Nothing

End Sub

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Madhanmohan Devarajan10y ago
Viv,

You can achieve that using Excel VBA Macro. In the Excel sheet cell A1 should have the database name and table name will be taken automatically from the active excel sheet. So make sure that you have opened the correct excel worksheet before executing the macro. Apart from that the column name should start from A2 cell. Here in this example i have just mentioned table column names from the excel sheet cells starting from A2 : E2.

Create a macro as shown below.
----------------------------------------------------
Private Sub CreateDatabaseFromExcel()
Dim dbConnectStr As String
Dim Catalog As Object
Dim cnt As ADODB.Connection
Dim dbPath As String
Dim tblName As String
'Set database name in the Excel Sheet
dbPath = ActiveSheet.Range("A1").Value 'Database Name
tblName = ActiveSheet.Name 'Table Name
dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";"
'Create new database using name entered in Excel Cell ("A1")
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create dbConnectStr
Set Catalog = Nothing
'Connect to database and insert a new table
Set cnt = New ADODB.Connection
With cnt
.Open dbConnectStr
.Execute "CREATE TABLE " & tblName & " ([" & ActiveSheet.Range("A2").Value & "] text(50) WITH Compression, " & _
"[" & ActiveSheet.Range("B2").Value & "] text(9) WITH Compression, " & _
"[" & ActiveSheet.Range("C2").Value & "] text(10) WITH Compression, " & _
"[" & ActiveSheet.Range("D2").Value & "] text(150) WITH Compression, " & _
"[" & ActiveSheet.Range("E2").Value & "] text(50) WITH Compression, "
End With
Set cnt = Nothing
End Sub
Tapan Patel10y ago
I am not sure if there is any other extension available other than the one provided by Red Gate but that is something you can view it as SQL Server's perspective.
If we talk about doing it using c#, please take a look at my example below, you can modify it to your requirement.
http://www.c-sharpcorner.com/article/exporting-datagrid-to-excel-in-wpf/