asked on

How do I create a vb.net (Visual Studio 2008) function that will allow me to use one code for each sheet and just change the parameters?

Here is a sample of my code, which currently is populating a SQL query output in six different sheets. I am repeating the code in each sheet and would like a way to avoid that.

Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
Private Const connectionString As String = "Data Source=mddbsqlpfqa.loe.corp; Database=PowerFaids; " _
& "Trusted_Connection=Yes;"
Private Sub btnGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerate.Click

Dim i, j As Integer

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlSheet6 As Excel.Worksheet
Dim xlSheet5 As Excel.Worksheet
Dim xlSheet4 As Excel.Worksheet
Dim xlSheet1 As Excel.Worksheet
Dim xlSheet2 As Excel.Worksheet
Dim xlSheet3 As Excel.Worksheet
Dim xlSheet7 As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value

xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)

xlWorkBook.Sheets.Add(, , 4)

xlSheet6 = CType(xlWorkBook.Worksheets("Sheet6"), Excel.Worksheet)
xlSheet6.Activate()
xlSheet6.Name = "9-2-2014 S"
xlSheet6.Tab.ColorIndex = CType(3, Excel.XlColorIndex)

Using cnn As New SqlConnection(connectionString)
cnn.Open()

'Run SQL'
Using sheet6sda As New SqlDataAdapter()

Using sheet6cmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

sheet6cmd.CommandTimeout = 0
sheet6cmd.Parameters.AddWithValue("@PROGRAM_START", "9/2/2014")
sheet6cmd.Parameters.AddWithValue("@TERM_TYPE", "S")
sheet6sda.SelectCommand = sheet6cmd

Using ds As New DataSet

sheet6sda.Fill(ds)

'Display Headers, Columns, and Rows'

For i = 0 To ds.Tables(0).Columns.Count - 1
xlSheet6.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
Next
For i = 0 To ds.Tables(0).Rows.Count - 1
For j = 0 To ds.Tables(0).Columns.Count - 1
xlSheet6.Cells(i + 2, j + 1) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next

'Format Table'

xlSheet6.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet6.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
xlSheet6.ListObjects("Table1").TableStyle = "TableStyleMedium2"

'~~> Create Borders
With xlSheet6.UsedRange

With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With

With .Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
End With

xlSheet6.Columns.AutoFit()
End Using

End Using

End Using

''NAME EXCEL SHEET 2''
xlSheet5 = CType(xlWorkBook.Worksheets("Sheet5"), Excel.Worksheet)
xlSheet5.Activate()
xlSheet5.Name = "9-2-2014 Q"
xlSheet5.Tab.ColorIndex = CType(6, Excel.XlColorIndex)

'Run SQL'
Using sheet5sda As New SqlDataAdapter()

Using sheet5cmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

sheet5cmd.CommandTimeout = 0
sheet5cmd.Parameters.AddWithValue("@PROGRAM_START", "9/2/2014")
sheet5cmd.Parameters.AddWithValue("@TERM_TYPE", "Q")
sheet5sda.SelectCommand = sheet5cmd

Using ds As New DataSet

sheet5sda.Fill(ds)

'Display Headers, Columns, and Rows'

For i = 0 To ds.Tables(0).Columns.Count - 1
xlSheet5.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
Next
For i = 0 To ds.Tables(0).Rows.Count - 1
For j = 0 To ds.Tables(0).Columns.Count - 1
xlSheet5.Cells(i + 2, j + 1) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next

'Format Table'

xlSheet5.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet5.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
xlSheet5.ListObjects("Table1").TableStyle = "TableStyleMedium4"

'~~> Create Borders
With xlSheet5.UsedRange

With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With

With .Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
End With

xlSheet5.Columns.AutoFit()
End Using
End Using
End Using

''NAME EXCEL SHEET 3''
xlSheet4 = CType(xlWorkBook.Worksheets("Sheet4"), Excel.Worksheet)
xlSheet4.Activate()
xlSheet4.Name = "10-13-2014 Q"
xlSheet4.Tab.ColorIndex = CType(9, Excel.XlColorIndex)

'Run SQL'
Using sheet4sda As New SqlDataAdapter()

Using sheet4cmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

sheet4cmd.CommandTimeout = 0
sheet4cmd.Parameters.AddWithValue("@PROGRAM_START", "10/13/2014")
sheet4cmd.Parameters.AddWithValue("@TERM_TYPE", "Q")
sheet4sda.SelectCommand = sheet4cmd

Using ds As New DataSet

sheet4sda.Fill(ds)

'Display Headers, Columns, and Rows'

For i = 0 To ds.Tables(0).Columns.Count - 1
xlSheet4.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
Next
For i = 0 To ds.Tables(0).Rows.Count - 1
For j = 0 To ds.Tables(0).Columns.Count - 1
xlSheet4.Cells(i + 2, j + 1) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next

'Format Table'

xlSheet4.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet4.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
xlSheet4.ListObjects("Table1").TableStyle = "TableStyleMedium1"

'~~> Create Borders
With xlSheet4.UsedRange

With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With

With .Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
End With

xlSheet4.Columns.AutoFit()
End Using
End Using
End Using

''NAME EXCEL SHEET 4''
xlSheet1 = CType(xlWorkBook.Worksheets("Sheet1"), Excel.Worksheet)
xlSheet1.Activate()
xlSheet1.Name = "10-27-2014 S"
xlSheet1.Tab.ColorIndex = CType(29, Excel.XlColorIndex)

'Run SQL'
Using sheet1sda As New SqlDataAdapter()

Using sheet1cmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

sheet1cmd.CommandTimeout = 0
sheet1cmd.Parameters.AddWithValue("@PROGRAM_START", "10/27/2014")
sheet1cmd.Parameters.AddWithValue("@TERM_TYPE", "S")
sheet1sda.SelectCommand = sheet1cmd

Using ds As New DataSet

sheet1sda.Fill(ds)

'Display Headers, Columns, and Rows'

For i = 0 To ds.Tables(0).Columns.Count - 1
xlSheet1.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
Next
For i = 0 To ds.Tables(0).Rows.Count - 1
For j = 0 To ds.Tables(0).Columns.Count - 1
xlSheet1.Cells(i + 2, j + 1) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next

'Format Table'

xlSheet1.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet1.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
xlSheet1.ListObjects("Table1").TableStyle = "TableStyleMedium3"

'Create Borders
With xlSheet1.UsedRange

With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With

With .Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
End With
End Using
End Using
End Using

xlSheet1.Columns.AutoFit()

''NAME EXCEL SHEET 5''
xlSheet2 = CType(xlWorkBook.Worksheets("Sheet2"), Excel.Worksheet)
xlSheet2.Activate()
xlSheet2.Name = "12-01-2014 Q"
xlSheet2.Tab.ColorIndex = CType(12, Excel.XlColorIndex)

'Run SQL'
Using sheet2sda As New SqlDataAdapter()

Using sheet2cmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

sheet2cmd.CommandTimeout = 0
sheet2cmd.Parameters.AddWithValue("@PROGRAM_START", "12/1/2014")
sheet2cmd.Parameters.AddWithValue("@TERM_TYPE", "Q")
sheet2sda.SelectCommand = sheet2cmd

Using ds As New DataSet

sheet2sda.Fill(ds)

'Display Headers, Columns, and Rows'

For i = 0 To ds.Tables(0).Columns.Count - 1
xlSheet2.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
Next
For i = 0 To ds.Tables(0).Rows.Count - 1
For j = 0 To ds.Tables(0).Columns.Count - 1
xlSheet2.Cells(i + 2, j + 1) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next

'Format Table'

xlSheet2.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet2.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
xlSheet2.ListObjects("Table1").TableStyle = "TableStyleMedium6"

'~~> Create Borders
With xlSheet2.UsedRange

With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With

With .Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
End With

xlSheet2.Columns.AutoFit()

End Using
End Using
End Using

''NAME EXCEL SHEET 6''
xlSheet3 = CType(xlWorkBook.Worksheets("Sheet3"), Excel.Worksheet)
xlSheet3.Activate()
xlSheet3.Name = "01-05-2015 S"
xlSheet3.Tab.ColorIndex = CType(22, Excel.XlColorIndex)

'Run SQL'
Using sheet3sda As New SqlDataAdapter()

Using sheet3cmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

sheet3cmd.CommandTimeout = 0
sheet3cmd.Parameters.AddWithValue("@PROGRAM_START", "1/5/2015")
sheet3cmd.Parameters.AddWithValue("@TERM_TYPE", "S")
sheet3sda.SelectCommand = sheet3cmd

Using ds As New DataSet

sheet3sda.Fill(ds)

'Display Headers, Columns, and Rows'

For i = 0 To ds.Tables(0).Columns.Count - 1
xlSheet3.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
Next
For i = 0 To ds.Tables(0).Rows.Count - 1
For j = 0 To ds.Tables(0).Columns.Count - 1
xlSheet3.Cells(i + 2, j + 1) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next

'Format Table'

xlSheet3.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet3.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
xlSheet3.ListObjects("Table1").TableStyle = "TableStyleMedium9"

'~~> Create Borders
With xlSheet3.UsedRange

With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With

With .Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
End With

xlSheet3.Columns.AutoFit()
End Using
End Using
End Using

''NAME EXCEL SHEET 7''
xlSheet7 = CType(xlWorkBook.Worksheets("Sheet7"), Excel.Worksheet)
xlSheet7.Activate()
xlSheet7.Name = "Summary"
xlSheet7.Tab.ColorIndex = CType(14, Excel.XlColorIndex)

xlApp.Visible = True

End Using

releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlSheet6)
releaseObject(xlSheet5)
releaseObject(xlSheet4)
releaseObject(xlSheet1)
releaseObject(xlSheet2)
releaseObject(xlSheet3)
releaseObject(xlSheet7)

End Sub

Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
End Class

SStory

You could create a list of them:

'create a list to hold them
Dim xlSheets As New System.Collections.Generic.List(Of Excel.Worksheet)
dim tmpSheet as Excel.Worksheet
dim SheetNames(6)={"Sheet1Name","Sheet2Name","Sheet3Name","Sheet4Name", Sheet5Name","Sheet6Name"}
for i as integer= 1 to 7
        'get the reference to the new sheet
        tmpSheet=CType(xlWorkBook.Worksheets("Sheet" & i), Excel.Worksheet)
        tmpSheet.Activate()
        tmpSheet.Name = SheetName(i-1)
        tmpSheet.Tab.ColorIndex = CType(3, Excel.XlColorIndex)
        'add to our reference list
        xlSheets.add(tmpSheet)
next

'later on you can loop through it like this
For Each xlSheet as Excel.Worksheet in xlSheets
       'do whatever to xlSheet
Next

Open in new window

Does this help?

cmed

ASKER

SStory,

Thanks for the clarification (I am new to VB). I took the information that you sent and now I am getting two error messages. I am trying to see how it works on one sheet before i move on to the next. Any advice.

Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
Private Const connectionString As String = "Data Source=mddbsqlpfqa.loe.corp; Database=PowerFaids; " _
& "Trusted_Connection=Yes;"
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim i, j As Integer

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlSheets As New System.Collections.Generic.List(Of Excel.Worksheet)
Dim tmpSheet As Excel.Worksheet

Dim SheetNames(6) = {"Sheet1Name", "Sheet2Name", "Sheet3Name", "Sheet4Name", "Sheet5Name", "Sheet6Name"}
For i As Integer = 1 To 7
'get the reference to the new sheet
tmpSheet = CType(xlWorkBook.Worksheets("Sheet" & i), Excel.Worksheet)
tmpSheet.Activate()
tmpSheet.Name = SheetNames(i - 1)
tmpSheet.Tab.ColorIndex = CType(3, Excel.XlColorIndex)
'add to our reference list
xlSheets.Add(tmpSheet)
Next

'later on you can loop through it like this
For Each xlSheet As Excel.Worksheet In xlSheets
'do whatever to xlSheet

Using cnn As New SqlConnection(connectionString)
cnn.Open()

'Run SQL'
Using sheet6sda As New SqlDataAdapter()

Using sheet6cmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

sheet6cmd.CommandTimeout = 0
sheet6cmd.Parameters.AddWithValue("@PROGRAM_START", "9/2/2014")
sheet6cmd.Parameters.AddWithValue("@TERM_TYPE", "S")
sheet6sda.SelectCommand = sheet6cmd

Using ds As New DataSet

sheet6sda.Fill(ds)

'Display Headers, Columns, and Rows'

For i = 0 To ds.Tables(0).Columns.Count - 1
xlSheet6.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
Next
For i = 0 To ds.Tables(0).Rows.Count - 1
For j = 0 To ds.Tables(0).Columns.Count - 1
xlSheet6.Cells(i + 2, j + 1) = _
ds.Tables(0).Rows(i).Item(j)
Next
Next

'Format Table'

xlSheet6.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet6.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
xlSheet6.ListObjects("Table1").TableStyle = "TableStyleMedium2"

'~~> Create Borders
With xlSheet6.UsedRange

With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With

With .Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = Excel.XlLineStyle.xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = Excel.XlBorderWeight.xlThin
End With
End With

xlSheet6.Columns.AutoFit()
End Using

End Using

End Using
End Using
Next

End Sub
End Class

ASKER CERTIFIED SOLUTION

SStory

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cmed

ASKER

@SStory

Thanks.