[ram11]

Private Sub cmdexcel_Click()
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object


Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add

Dim DataArray(1 To 500, 1 To 5) As Variant

Dim r As Integer
Dim NumberOfRows As Integer
NumberOfRows = Adodc1.Recordset.RecordCount
Adodc1.Recordset.MoveFirst

For r = 1 To NumberOfRows
DataArray(r, 1) = Adodc1.Recordset.Fields("name")
DataArray(r, 2) = Adodc1.Recordset.Fields("Date")
DataArray(r, 3) = Adodc1.Recordset.Fields("address")
DataArray(r, 4) = Adodc1.Recordset.Fields("Work")
DataArray(r, 5) = Adodc1.Recordset.Fields("Details")

Adodc1.Recordset.MoveNext
Next
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1:E1").Font.Bold = True


oSheet.Range("A1 :E1").Value = Array("ID", "Date", "address", "Work", "Details")



oSheet.Range("A2").Resize(NumberOfRows, 5).Value = DataArray

oBook.SaveAs "C:\Report.xls"
oExcel.Quit
Adodc1.Recordset.MoveFirst
MsgBox "Report File Saved", 64, "Info"

End Sub

[raziel_]

so how can we help you do you get some errors. and that is not datagrid that is query from DB to excel

[ram11]

lol funny , its just a post for datagrid to excel

[raziel_]

lol. 1 more time your not sending data grid to excel. your sending query from your Data Base to excel. next time post your code in tags like so

This post has been edited by NoBrain: 29 January 2010 - 06:18 AM

[n19htmare09]

ram11, on 27 January 2010 - 11:18 PM, said:

Private Sub cmdexcel_Click()
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object


Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add

Dim DataArray(1 To 500, 1 To 5) As Variant

Dim r As Integer
Dim NumberOfRows As Integer
NumberOfRows = Adodc1.Recordset.RecordCount
Adodc1.Recordset.MoveFirst

For r = 1 To NumberOfRows
DataArray(r, 1) = Adodc1.Recordset.Fields("name")
DataArray(r, 2) = Adodc1.Recordset.Fields("Date")
DataArray(r, 3) = Adodc1.Recordset.Fields("address")
DataArray(r, 4) = Adodc1.Recordset.Fields("Work")
DataArray(r, 5) = Adodc1.Recordset.Fields("Details")

Adodc1.Recordset.MoveNext
Next
Set oSheet = oBook.Worksheets(1)
oSheet.Range("A1:E1").Font.Bold = True


oSheet.Range("A1 :E1").Value = Array("ID", "Date", "address", "Work", "Details")



oSheet.Range("A2").Resize(NumberOfRows, 5).Value = DataArray

oBook.SaveAs "C:\Report.xls"
oExcel.Quit
Adodc1.Recordset.MoveFirst
MsgBox "Report File Saved", 64, "Info"

End Sub

i want to use that code but it comes to error here
it says
Run-time error '91':
Object Variable or With block variable not set

-> NumberOfRows = Adodc1.Recordset.RecordCount

[maj3091]

The code is missing the part where it opens the database connection and queries the data, so you will get an error.

[dbfman]

I'm edit and make a new one below ;

Private Sub Command2_Click()

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object


Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add



Dim r, c As Integer
Dim NumberOfRows As Variant
Dim NumberOfColumns As Variant
Adodc1.Recordset.MoveFirst
NumberOfRows = Adodc1.Recordset.RecordCount

NumberOfColumns = Adodc1.Recordset.Fields.Count

Adodc1.Recordset.MoveFirst



Dim DataArray(1 To 500, 1 To 21) As Variant
Dim DataArray2(1 To 1, 1 To 21) As Variant

' 500 is estimate for number of record
' 21 is number of fields


'collect the name of fields into DataArray2
For c = 0 To NumberOfColumns - 1
DataArray2(1, c + 1) = Adodc1.Recordset.Fields.Item©.Name
Next


'collect data in each record into DataArray
For r = 1 To NumberOfRows
For c = 0 To NumberOfColumns - 1
DataArray(r, c + 1) = Adodc1.Recordset.Fields©
Next

Adodc1.Recordset.MoveNext
Next
Set oSheet = oBook.Worksheets(1)

oSheet.Range("A1:U1").Font.Bold = True ' a1:u1 = 21 columns ; related to number of fields

' input name of fields from DataArray2 to first row, or start at A1
oSheet.Range("A1").Resize(NumberOfRows, 21).Value = DataArray2

' input data record from DataArray to second row, or start at A2
oSheet.Range("A2").Resize(NumberOfRows, 21).Value = DataArray

oBook.SaveAs "C:\report.xls"
oExcel.Quit

Adodc1.Recordset.MoveFirst
MsgBox "file was save to C:\report.xls ", 64, "Confirm Data"

End Sub

---------
enjoy
hitman_asp@hotmail.com

[dbfman]

dbfman, on 18 November 2011 - 04:09 AM, said:

I'm edit and make a new one below ;

Private Sub Command2_Click()

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object


Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add



Dim r, c As Integer
Dim NumberOfRows As Variant
Dim NumberOfColumns As Variant
Adodc1.Recordset.MoveFirst
NumberOfRows = Adodc1.Recordset.RecordCount

NumberOfColumns = Adodc1.Recordset.Fields.Count

Adodc1.Recordset.MoveFirst



Dim DataArray(1 To 500, 1 To 21) As Variant
Dim DataArray2(1 To 1, 1 To 21) As Variant

' 500 is estimate for number of record
' 21 is number of fields


'collect the name of fields into DataArray2
For c = 0 To NumberOfColumns - 1
DataArray2(1, c + 1) = Adodc1.Recordset.Fields.Item©.Name
Next


'collect data in each record into DataArray
For r = 1 To NumberOfRows
For c = 0 To NumberOfColumns - 1
DataArray(r, c + 1) = Adodc1.Recordset.Fields©
Next

Adodc1.Recordset.MoveNext
Next
Set oSheet = oBook.Worksheets(1)

oSheet.Range("A1:U1").Font.Bold = True ' a1:u1 = 21 columns ; related to number of fields

' input name of fields from DataArray2 to first row, or start at A1
oSheet.Range("A1").Resize(NumberOfRows, 21).Value = DataArray2

' input data record from DataArray to second row, or start at A2
oSheet.Range("A2").Resize(NumberOfRows, 21).Value = DataArray

oBook.SaveAs "C:\report.xls"
oExcel.Quit

Adodc1.Recordset.MoveFirst
MsgBox "file was save to C:\report.xls ", 64, "Confirm Data"

End Sub

---------
enjoy
hitman_asp@hotmail.com

some problem in html code
if you see © in my code that means ( c )
then I show you belw

For c = 0 To NumberOfColumns - 1
DataArray2(1, c + 1) = Adodc1.Recordset.Fields.Item( c ).Name
Next


For r = 1 To NumberOfRows
For c = 0 To NumberOfColumns - 1
DataArray(r, c + 1) = Adodc1.Recordset.Fields( c )

[ushmoe]

hi, am trying to get data displayed on datagridview to an excel document using vb6 but keep getting the error message: Object Variable or With Block Variable Not Set

My code:

Private Sub cmdExport_Click()

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add



Dim r, c As Integer
Dim NumberOfRows As Variant
Dim NumberOfColumns As Variant

'Adodc1.RecordSource = "SELECT * FROM wrong_data where Affiliate = '" & Trim(Combo1.Text) & "' "

Adodc1.Recordset.MoveFirst
NumberOfRows = Adodc1.Recordset.RecordCount

NumberOfColumns = Adodc1.Recordset.Fields.Count

Adodc1.Recordset.MoveFirst



Dim DataArray(1 To 500, 1 To 21) As Variant
Dim DataArray2(1 To 1, 1 To 21) As Variant

' 500 is estimate for number of record
' 21 is number of fields


'collect the name of fields into DataArray2
For c = 0 To NumberOfColumns - 1
DataArray2(1, c + 1) = Adodc1.Recordset.Fields.Item(c).Name
Next


'collect data in each record into DataArray
For r = 1 To NumberOfRows
For c = 0 To NumberOfColumns - 1
DataArray(r, c + 1) = Adodc1.Recordset.Fields(c)
Next

Adodc1.Recordset.MoveNext
Next
Set oSheet = oBook.Worksheets(1)

oSheet.Range("A1:U1").Font.Bold = True ' a1:u1 = 21 columns ; related to number of fields

' input name of fields from DataArray2 to first row, or start at A1
oSheet.Range("A1").Resize(NumberOfRows, 21).Value = DataArray2

' input data record from DataArray to second row, or start at A2
oSheet.Range("A2").Resize(NumberOfRows, 21).Value = DataArray

oBook.SaveAs "C:\report.xls"
oExcel.Quit

Adodc1.Recordset.MoveFirst
MsgBox "file was save to C:\report.xls ", 64, "Confirm Data"





End Sub

[ushmoe]

The error normally comeup on this line:

'Adodc1.RecordSource = "SELECT * FROM wrong_data where Affiliate = '" & Trim(Combo1.Text) & "' "  

   
Adodc1.Recordset.MoveFirst