export Excel Sql table column name & tableName as sheetName.

Question

simply export Sql table ' s column name in Excel as column and name of table as sheet name .

Tapan Patel · Answer

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/

Madhanmohan Devarajan · Answer

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

export Excel Sql table column name & tableName as sheetName.

Answers (2)