Re: Export DataGrid to Excel
Welcome to th Forums.
I hardly ever use datagrids but isnt there a columns collection "Cols". The loop is just using the Col property for the upper bounds of the inner loop where it loops throught the rows columns
Code:
Public Sub subGridToExcel()
On Error GoTo ErrorLine
Screen.MousePointer = vbHourglass
Dim xl As Object
Dim wb As Object
Dim ws As Object
Dim i As Long
Dim x As Long
Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Add()
Set ws = xl.ActiveWindow.ActiveSheet
ws.Columns().ColumnWidth = 10
For x = 0 To frmFind.DataGrid1.Row - 1
frmFind.DataGrid1.Row = x
frmFind.DataGrid1.Col = 1
For i = 1 To frmFind.DataGrid1.Cols '<===Here changed?
frmFind.DataGrid1.Col = i
If frmFind.DataGrid1.Text <> "" Then
ws.Cells(x, i) = frmFind.DataGrid1.Text
ws.Cells(x, i).HorizontalAlignment = -4131
'xlLeft
End If
Next
Next
xl.Visible = True
ErrorLine:
Screen.MousePointer = vbDefault
End Sub
Re: Export DataGrid to Excel
The DataGrid is a strange beast. It does not know exactly how many Rows it actually has. There is an ApproxCount property that seems to work but I have no idea if it is 100% reliable. You should use the RecordCount property of the underlying recordset object instead.
Also, accessing a Row (without changing the current cell) is always relative to the current row. Typically you need to ensure the datagrid is on the first row before looping through the entire grid.
Anyways, here is an example of looping through the entire grid.
Code:
Dim lngRow As Long
Dim lngCol As Long
For lngRow = 0 To DataGrid1.ApproxCount - 1
For lngCol = 0 To DataGrid1.Columns.Count - 1
'can also use the CellValue method instead of the CellText
Debug.Print DataGrid1.Columns(lngCol).CellText(DataGrid1.GetBookmark(lngRow)),
Next
Next
Re: Export DataGrid to Excel
Hi RobDog888/brucevde,
Thanks for the code. However, these code does not seems to Work. it just opens the Excel Application with a new Workbook.
Anyways Thank you for your time for giving a try and I really appreciate the unconditional help you provide to the beginners through this forum.
Please have a Wonderful day!!!
Warm Regards,
Nirmal Udupa.
India.
Re: Export DataGrid to Excel
Its just the inner loop of your original code. Try merging Brucevde's code into yours.
Re: Export DataGrid to Excel
Hi RobDog888/brucevde, :wave:
Hope you are all doing Great!!
Thanks for the reply RobDog888.
Please find the tested code which will export datagrid data to Excel.
Note: Please change the path as required in the code and create Excel Sheet and specify the name of the Workbook and Worksheet in the code below
Code:
Public Sub export2excel()
On Error GoTo er
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")
Dim oWorkBook As Object
Dim oWorkSheet As Object
Dim i As Integer, k As Integer
Dim lRow As Long
Dim LastRow As Long
Dim LastCol As Long
oExcel.Visible = False
oExcel.Workbooks.Open App.Path & "\Nirmal.xls"
Set oWorkSheet = oExcel.Workbooks("Nirmal.xls").sheets("Batch")
i = 2 'Row in Excel
LastRow = frmFind.DataGrid1.Row 'Save Current row
LastCol = frmFind.DataGrid1.Col 'and column
frmFind.DataGrid1.Row = 0 'Fixed Row is -1
Do While frmFind.DataGrid1.Row <= frmFind.DataGrid1.VisibleRows - 1
For k = 1 To frmFind.DataGrid1.Columns.Count - 1
frmFind.DataGrid1.Col = k 'Fixed Column is -1
oWorkSheet.Cells(i, k).Font.Bold = False
oWorkSheet.Cells(i, k).Font.Color = vbBlack
oWorkSheet.Cells(i, k).value = frmFind.DataGrid1.Text
Next
i = i + 1
If frmFind.DataGrid1.Row < frmFind.DataGrid1.VisibleRows - 1 Then
frmFind.DataGrid1.Row = frmFind.DataGrid1.Row + 1
Else
Exit Do
End If
Loop
frmFind.DataGrid1.Row = LastRow 'Restore original Row
frmFind.DataGrid1.Col = LastCol 'and Column
oExcel.Workbooks("Nirmal.xls").Save
oExcel.Workbooks("Nirmal.xls").Close savechanges:=True
oExcel.Quit
er:
If Err.Number = 1004 Then
Exit Sub
End If
End Sub
Thank you and Please have a Wonderful Weekend. :)
Warm Regards,
Nirmal Udupa.
India.
Re: Export DataGrid to Excel
what is frmFind?
is it a textbox or what?
reply urgent!!!
because im a noob brain
Re: Export DataGrid to Excel
The prefix frm usually indicates the name of a Form.
txt would indicate the name of a textbox.
Re: Export DataGrid to Excel
so if i dont have any textbox just the datafields of my datagrid to will be inserted to excel
Re: Export DataGrid to Excel
Use your datagrids object name and reference down to the cell needed.
Re: Export DataGrid to Excel
can u give me a exsample so that i can understand better..
pls. help me!!!
Re: Export DataGrid to Excel
you can export from dataset
For i = 0 To ds.Tables(0).Rows.Count - 1
For j = 0 To ds.Tables(0).Columns.Count - 1
xlWorkSheet.Cells(i + 1, j + 1) = ds.Tables(0).Rows(i).Item(j)
Next
Next
http://vb.net-informations.com/excel...e_to_excel.htm
you can export from datagridview
For i = 0 To DataGridView1.RowCount - 2
For j = 0 To DataGridView1.ColumnCount - 1
xlWorkSheet.Cells(i + 1, j + 1) = DataGridView1(j, i).Value.ToString()
Next
Next
http://vb.net-informations.com/excel...w_to_excel.htm
Re: Export DataGrid to Excel
Quote:
Originally Posted by
nirmaludupa
Hi RobDog888/brucevde, :wave:
Hope you are all doing Great!!
Thanks for the reply RobDog888.
Please find the tested code which will export datagrid data to Excel.
Note: Please change the path as required in the code and create Excel Sheet and specify the name of the Workbook and Worksheet in the code below
Code:
Public Sub export2excel()
On Error GoTo er
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")
Dim oWorkBook As Object
Dim oWorkSheet As Object
Dim i As Integer, k As Integer
Dim lRow As Long
Dim LastRow As Long
Dim LastCol As Long
oExcel.Visible = False
oExcel.Workbooks.Open App.Path & "\Nirmal.xls"
Set oWorkSheet = oExcel.Workbooks("Nirmal.xls").sheets("Batch")
i = 2 'Row in Excel
LastRow = frmFind.DataGrid1.Row 'Save Current row
LastCol = frmFind.DataGrid1.Col 'and column
frmFind.DataGrid1.Row = 0 'Fixed Row is -1
Do While frmFind.DataGrid1.Row <= frmFind.DataGrid1.VisibleRows - 1
For k = 1 To frmFind.DataGrid1.Columns.Count - 1
frmFind.DataGrid1.Col = k 'Fixed Column is -1
oWorkSheet.Cells(i, k).Font.Bold = False
oWorkSheet.Cells(i, k).Font.Color = vbBlack
oWorkSheet.Cells(i, k).value = frmFind.DataGrid1.Text
Next
i = i + 1
If frmFind.DataGrid1.Row < frmFind.DataGrid1.VisibleRows - 1 Then
frmFind.DataGrid1.Row = frmFind.DataGrid1.Row + 1
Else
Exit Do
End If
Loop
frmFind.DataGrid1.Row = LastRow 'Restore original Row
frmFind.DataGrid1.Col = LastCol 'and Column
oExcel.Workbooks("Nirmal.xls").Save
oExcel.Workbooks("Nirmal.xls").Close savechanges:=True
oExcel.Quit
er:
If Err.Number = 1004 Then
Exit Sub
End If
End Sub
Thank you and Please have a Wonderful Weekend. :)
Warm Regards,
Nirmal Udupa.
India.
Thanks Nirmal It works
Re: Export DataGrid to Excel
Originally Posted by nirmaludupa View Post
Hi RobDog888/brucevde,
Hope you are all doing Great!!
Thanks for the reply RobDog888.
Please find the tested code which will export datagrid data to Excel.
Note: Please change the path as required in the code and create Excel Sheet and specify the name of the Workbook and Worksheet in the code below
Code:
Public Sub export2excel()
On Error GoTo er
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")
Dim oWorkBook As Object
Dim oWorkSheet As Object
Dim i As Integer, k As Integer
Dim lRow As Long
Dim LastRow As Long
Dim LastCol As Long
oExcel.Visible = False
oExcel.Workbooks.Open App.Path & "\Nirmal.xls"
Set oWorkSheet = oExcel.Workbooks("Nirmal.xls").sheets("Batch")
i = 2 'Row in Excel
LastRow = frmFind.DataGrid1.Row 'Save Current row
LastCol = frmFind.DataGrid1.Col 'and column
frmFind.DataGrid1.Row = 0 'Fixed Row is -1
Do While frmFind.DataGrid1.Row <= frmFind.DataGrid1.VisibleRows - 1
For k = 1 To frmFind.DataGrid1.Columns.Count - 1
frmFind.DataGrid1.Col = k 'Fixed Column is -1
oWorkSheet.Cells(i, k).Font.Bold = False
oWorkSheet.Cells(i, k).Font.Color = vbBlack
oWorkSheet.Cells(i, k).value = frmFind.DataGrid1.Text
Next
i = i + 1
If frmFind.DataGrid1.Row < frmFind.DataGrid1.VisibleRows - 1 Then
frmFind.DataGrid1.Row = frmFind.DataGrid1.Row + 1
Else
Exit Do
End If
Loop
frmFind.DataGrid1.Row = LastRow 'Restore original Row
frmFind.DataGrid1.Col = LastCol 'and Column
oExcel.Workbooks("Nirmal.xls").Save
oExcel.Workbooks("Nirmal.xls").Close savechanges:=True
oExcel.Quit
er:
If Err.Number = 1004 Then
Exit Sub
End If
End Sub
Thank you and Please have a Wonderful Weekend.
Warm Regards,
Nirmal Udupa.
India.
yeah baby... it really works... thanks guys... :))