i've made a small tool that searches an access DB in back end & dumps the results in a Datagridview, now i want to export those results to excel, after googling i found a code that does it for me , it's working partially for me. It writes the Headers to a new excel file but then for some reason i getting below error (on the blue line in code):
Conversion from type 'DBNull' to type 'String' is not valid.
Code:Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click Dim objExcel As New Microsoft.Office.Interop.Excel.Application Dim oXL As New Microsoft.Office.Interop.Excel.Application Dim oWB As Microsoft.Office.Interop.Excel.Workbook Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet Dim oRng As Microsoft.Office.Interop.Excel.Range 'oXL = CType(CreateObject("Excel.Application"), a) oXL.Visible = True ' Get a new workbook. oWB = oXL.Workbooks.Add oSheet = CType(oWB.ActiveSheet, Worksheet) ' Add table headers going cell by cell. For Each cols As DataGridViewColumn In Me.DataGridView1.Columns oSheet.Cells.Columns.CurrentRegion.Columns(cols.Index + 1) = cols.HeaderText Next ' Format A1:D1 as bold, vertical alignment = center. With oSheet.Range("A1", "Z1") .Font.Bold = True .VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter End With ' Create an array to set multiple values at once. Dim rowcount As Integer = Me.DataGridView1.Rows.Count Dim colcount As Integer = Me.DataGridView1.Columns.Count Dim GridData(rowcount, colcount) As String For Each row As DataGridViewRow In Me.DataGridView1.Rows Dim rowindex As Integer = row.Index For Each cells As DataGridViewCell In row.Cells Dim cellindex As String = cells.ColumnIndex GridData(rowindex, cellindex) = CStr(cells.Value) Next Next ' Fill A2:B6 with an array of values (First and Last Names). oSheet.Range("A2", "G" & Me.DataGridView1.Rows.Count).Value = GridData ' Fill C2:C6 with a relative formula (=A2 & " " & B2). if you want to add a formula oRng = oSheet.Range("C2", "C6") oRng.Formula = "=A2 & "" "" & B2" ' Fill D2:D6 with a formula(=RAND()*100000) and apply format.if you want to apply a format oRng = oSheet.Range("D2", "D6") oRng.Formula = "=RAND()*100000" oRng.NumberFormat = "$0.00" ' AutoFit columns A:D. oRng = oSheet.Range("A1", "Z1") oRng.EntireColumn.AutoFit() ' Make sure Excel is visible and give the user control ' of Excel's lifetime. oXL.Visible = True oXL.UserControl = True ' Make sure that you release object references. oRng = Nothing oSheet = Nothing oWB = Nothing oXL.Quit() oXL = Nothing
pls help ?




Reply With Quote