Click here to Skip to main content
14,426,997 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have the following code and I am trying to export information from datagridview to an excel document based on the number of records and starting with a specific row and column. Please help!

Dim rowNo1 As Integer = 1
Dim colNo1 As Integer = 1
Dim colNo2 As Integer = 1
Dim numrow As Integer = 36
'Import Information
objExcelApp = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
objExcelBook = CType(objExcelApp.Workbooks.Add, Microsoft.Office.Interop.Excel.Workbook)
objExcelSheet = CType(objExcelBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
objExcelBook = objExcelApp.Workbooks.Open(sWorkbook)

For rowNo1 = 0 To DataGridView1.RowCount - 1
    For colNo1 = 1 To DataGridView1.ColumnCount - 1
        If DataGridView1.Columns(colNo1).Width > 0 Then
           If Not IsDBNull(DataGridView1.Item(colNo1, rowNo1).Value) Then ' Added this if statement to prevent DBNull to String error
                If Trim(DataGridView1.Item(colNo1, rowNo1).Value) <> "" Then
                     objExcelApp.Cells(numrow + 1, colNo2) = DataGridView1.Item(colNo1, rowNo1).Value
                End If
           End If
           If colNo2 >= DataGridView1.ColumnCount Then
                colNo2 = 1
           Else
                colNo2 = colNo2 + 1
           End If
        End If
    Next colNo1
    numrow = numrow + 1
Next rowNo1


Basically I want it to start at row 37 and column "d".

What I have tried:

I have tried numerous ways but all start at Column "A" not Column "D".
Posted
Updated 24-Jun-16 15:53pm
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

You should learn to use the debugger as soon as possible. Rather than guessing what your code is doing, It is time to see your code executing and ensuring that it does what you expect.

The debugger allow you to follow the execution line by line, inspect variables and you will see that there is a point where it stop doing what you expect.
Debugger - Wikipedia, the free encyclopedia[^]
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]

Assuming your code work, I would simplify that way
Dim rowNo1 As Integer = 1
Dim colNo1 As Integer = 1
Dim colNo2 As Integer = 1
Dim numrow As Integer = 36
'Import Information
objExcelApp = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)
objExcelBook = CType(objExcelApp.Workbooks.Add, Microsoft.Office.Interop.Excel.Workbook)
objExcelSheet = CType(objExcelBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
objExcelBook = objExcelApp.Workbooks.Open(sWorkbook)
 
For rowNo1 = 0 To DataGridView1.RowCount - 1
    For colNo1 = 1 To DataGridView1.ColumnCount - 1
        If DataGridView1.Columns(colNo1).Width > 0 Then
           If Not IsDBNull(DataGridView1.Item(colNo1, rowNo1).Value) Then ' Added this if statement to prevent DBNull to String error
                If Trim(DataGridView1.Item(colNo1, rowNo1).Value) <> "" Then
                     objExcelApp.Cells(rowNo1 + 37, colNo1+3) = DataGridView1.Item(colNo1, rowNo1).Value
                End If
           End If
           If colNo2 >= DataGridView1.ColumnCount Then
                colNo2 = 1
           Else
                colNo2 = colNo2 + 1
           End If
        End If
    Next colNo1
    numrow = numrow + 1
Next rowNo1


Use the debugger to see what your code is really doing.
   
Comments
Member 12602818 25-Jun-16 6:25am
   
It started at the correct position (row and column) but there are 6 columns of information and it only populated the last 5. The first column was blank. Any ideas?
Patrice T 25-Jun-16 15:04pm
   
Are you sure "colNo1" must start at 1 in the loop ?
Patrice T 25-Jun-16 15:06pm
   
Put different values everywhere in the datagrid and see which one are copied.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100