9 Replies - 19984 Views - Last Post: 23 August 2012 - 09:17 AM Rate Topic: -----

#1 ram11  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 86
  • Joined: 30-November 08

Export from Datagrid to Excel

Posted 27 January 2010 - 11:18 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Export from Datagrid to Excel

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: Export from Datagrid to Excel

Posted 28 January 2010 - 05:16 AM

so how can we help you do you get some errors. and that is not datagrid that is query from DB to excel :P
Was This Post Helpful? 0
  • +
  • -

#3 ram11  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 86
  • Joined: 30-November 08

Re: Export from Datagrid to Excel

Posted 28 January 2010 - 06:03 AM

lol funny , its just a post for datagrid to excel
Was This Post Helpful? 0
  • +
  • -

#4 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: Export from Datagrid to Excel

Posted 28 January 2010 - 08:56 AM

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
:code:

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

Was This Post Helpful? 0
  • +
  • -

#5 n19htmare09  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 14-October 11

Re: Export from Datagrid to Excel

Posted 14 October 2011 - 12:28 AM

View Postram11, 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
Was This Post Helpful? 0
  • +
  • -

#6 maj3091  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 303
  • View blog
  • Posts: 1,797
  • Joined: 26-March 09

Re: Export from Datagrid to Excel

Posted 14 October 2011 - 03:52 AM

The code is missing the part where it opens the database connection and queries the data, so you will get an error.
Was This Post Helpful? 0
  • +
  • -

#7 dbfman  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 18-November 11

Re: Export from Datagrid to Excel

Posted 18 November 2011 - 04:09 AM

I'm edit and make a new one below ; :bigsmile:

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
Was This Post Helpful? 0
  • +
  • -

#8 dbfman  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 18-November 11

Re: Export from Datagrid to Excel

Posted 18 November 2011 - 04:14 AM

View Postdbfman, on 18 November 2011 - 04:09 AM, said:

I'm edit and make a new one below ; :bigsmile:

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 )
Was This Post Helpful? 0
  • +
  • -

#9 ushmoe  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 23-August 12

Re: Export from Datagrid to Excel

Posted 23 August 2012 - 09:09 AM

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



Was This Post Helpful? 0
  • +
  • -

#10 ushmoe  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 23-August 12

Re: Export from Datagrid to Excel

Posted 23 August 2012 - 09:17 AM

The error normally comeup on this line:



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

   
Adodc1.Recordset.MoveFirst  



Was This Post Helpful? 0
  • +
  • -

Page 1 of 1