12 Replies - 33424 Views - Last Post: 17 February 2013 - 08:48 PM Rate Topic: -----

#1 kartikchoudhary  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 15-June 10

how export data to MS excel through vb

Posted 21 July 2010 - 05:50 AM

Hi
i am building an app.. it is kind of calculator it calculates gross margin,, so it consists of many text boxes like for revenue, cost etc...(moral of the story is that i have too many text boxes)....

and i am storing the values of these text boxes in an ms access database....

Now i want to transfer this large data to excel through vb, I tried a bit googling about it but i found the code either very complicated or they are sending very little amt of data...

can you plz suggest me something so that i pull the data form access and send it to an excell sheet...
I dont need to maintain database once it sent to excel..

Help!!!!!!!!!!! :surrender:

Is This A Good Question/Topic? 0
  • +

Replies To: how export data to MS excel through vb

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

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

Re: how export data to MS excel through vb

Posted 21 July 2010 - 05:56 AM

i can suggest you our tutorial section:
Visual Basic 6 Tutorials

EDIT: i see you have look the tutorials on excel. then look at the tutorials about ADODB

This post has been edited by NoBrain: 21 July 2010 - 05:57 AM

Was This Post Helpful? 0
  • +
  • -

#3 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 817
  • Joined: 16-September 09

Re: how export data to MS excel through vb

Posted 21 July 2010 - 08:22 PM

DoCmd.TransferText acExportDelim, "MySpec", "MyTable", "NameOfExportFile", True



I don't know if you know how to create a spec but the easy way is as follows.

Export your file from access to csv(this will open with excel automatically)
View the list of tables in the access window
Right click and check import
When the import wizard opens, click Advanced
Name your columns if you want(only if you're going to keep a header row)
save the spec (if you save under a dif name than MySpec, change the name in the code at the top)
Once that's done, the above code has a "Spec" to refer to when exporting and knows the format.

Don't forget to name with the extension ".CSV". this is comma delimited and should work for you.

Hope this helps!!
Was This Post Helpful? 0
  • +
  • -

#4 kartikchoudhary  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 15-June 10

Re: how export data to MS excel through vb

Posted 21 July 2010 - 11:14 PM

@All
Actually I want some code which can just send the data form MS Access to excel rest of the things I have done and working well
Was This Post Helpful? 0
  • +
  • -

#5 ram11  Icon User is offline

  • D.I.C Head

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

Re: how export data to MS excel through vb

Posted 21 July 2010 - 11:36 PM

Private Sub cmdprint_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 200, 1 To 4) As Variant
   
      Dim r As Integer
      Dim NumberOfRows As Integer
       NumberOfRows = adoDRF.Recordset.RecordCount
      adoDRF.Recordset.MoveFirst

      For r = 1 To NumberOfRows
      DataArray(r, 1) = adoDRF.Recordset.Fields("SrNo")
      DataArray(r, 2) = adoDRF.Recordset.Fields("date")
      DataArray(r, 3) = adoDRF.Recordset.Fields("name")
      DataArray(r, 4) = adoDRF.Recordset.Fields("address")

      adoDRF.Recordset.MoveNext
      Next
      Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1:D1").Font.Bold = True
   
   oSheet.Range("A1:D1").Value = Array("Sr.no", "Date", "Name", "Address")                                                 ' Put headers of fields to excel file

   
    oSheet.Range("A2").Resize(NumberOfRows, 4).Value = DataArray
   
   oBook.SaveAs "C:\drf.xls"
   oExcel.Quit
   
   adoDRF.Recordset.MoveFirst
   
   MsgBox "Report File format  File Saved", 64, "Info"
End Sub 

Was This Post Helpful? 1
  • +
  • -

#6 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 817
  • Joined: 16-September 09

Re: how export data to MS excel through vb

Posted 22 July 2010 - 01:03 AM

If the last one is too complicated, try this

Sub ExportToExcel()

Dim RS As Recordset
Dim OPRec As String
Dim OPFile As String
Dim FileNum As Integer

Set RS = CurrentDb.OpenRecordset("YourTable") 'Enter the name of your table here, exactly as it appears in the DB
OPFile = "NameOfYourExportFile.CSV" 'Remember .CSV opens with Exce
FileNum = FreeFile 'Obtain free file number

Open OPFile For Output As #FileNum

RS.MoveFirst
Do Until RS.EOF

    OPRec = RS.Fields(1) & "," & RS.Fields(2) & "," & RS.Fields(3) & "," & RS.Fields(4) & "," _
        & RS.Fields(5) & "," & RS.Fields(6) & "," & RS.Fields(7) & "," & RS.Fields(8) & ","
    
    Print #FileNum, OPRec
    
    RS.MoveNext
Loop

Close #FileNum

MsgBox "Done!"

End Sub



Good luck!
Was This Post Helpful? 0
  • +
  • -

#7 kartikchoudhary  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 15-June 10

Re: how export data to MS excel through vb

Posted 23 July 2010 - 02:21 AM

@ ram 11
YOU ROCK MAN!!!!!!!!!! your code worked with a bit changes
THNKS A TON!!!!!!! :^: :clap: :clap: :clap:

@ guyfromri

it shows error at "Set RS = CurrentDb.OpenRecordset("YourTable")"
Was This Post Helpful? 1
  • +
  • -

#8 gen009  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 28
  • Joined: 15-May 09

Re: how export data to MS excel through vb

Posted 26 July 2010 - 11:18 PM

View Postkartikchoudhary, on 23 July 2010 - 04:21 PM, said:

@ ram 11
YOU ROCK MAN!!!!!!!!!! your code worked with a bit changes
THNKS A TON!!!!!!! :^: :clap: :clap: :clap:

@ guyfromri

it shows error at "Set RS = CurrentDb.OpenRecordset("YourTable")"



?? ahaha,, your so funny, actually if you already have that in MSAccess, you can easily paste it in excel, just drag the table name (from MSAccess) to an excel spreadsheet.. ^_^
Was This Post Helpful? 0
  • +
  • -

#9 kartikchoudhary  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 15-June 10

Re: how export data to MS excel through vb

Posted 02 August 2010 - 02:06 AM

View Postgen009, on 26 July 2010 - 10:18 PM, said:

View Postkartikchoudhary, on 23 July 2010 - 04:21 PM, said:

@ ram 11
YOU ROCK MAN!!!!!!!!!! your code worked with a bit changes
THNKS A TON!!!!!!! :^: :clap: :clap: :clap:

@ guyfromri

it shows error at "Set RS = CurrentDb.OpenRecordset("YourTable")"



?? ahaha,, your so funny, actually if you already have that in MSAccess, you can easily paste it in excel, just drag the table name (from MSAccess) to an excel spreadsheet.. ^_^

:withstupid:
Ohhh rather i should ahve done my work in excell it self i think iam giving too much pain to my mind
Was This Post Helpful? 0
  • +
  • -

#10 kartikchoudhary  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 15-June 10

Re: how export data to MS excel through vb

Posted 02 August 2010 - 02:12 AM

View Postram11, on 21 July 2010 - 10:36 PM, said:

Private Sub cmdprint_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 200, 1 To 4) As Variant
   
      Dim r As Integer
      Dim NumberOfRows As Integer
       NumberOfRows = adoDRF.Recordset.RecordCount
      adoDRF.Recordset.MoveFirst

      For r = 1 To NumberOfRows
      DataArray(r, 1) = adoDRF.Recordset.Fields("SrNo")
      DataArray(r, 2) = adoDRF.Recordset.Fields("date")
      DataArray(r, 3) = adoDRF.Recordset.Fields("name")
      DataArray(r, 4) = adoDRF.Recordset.Fields("address")

      adoDRF.Recordset.MoveNext
      Next
      Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1:D1").Font.Bold = True
   
   oSheet.Range("A1:D1").Value = Array("Sr.no", "Date", "Name", "Address")                                                 ' Put headers of fields to excel file

   
    oSheet.Range("A2").Resize(NumberOfRows, 4).Value = DataArray
   
   oBook.SaveAs "C:\drf.xls"
   oExcel.Quit
   
   adoDRF.Recordset.MoveFirst
   
   MsgBox "Report File format  File Saved", 64, "Info"
End Sub 

hey ram by the above code i am getting data in horizontal form.. is it possible to do it in vertical form thanks in advance....
:dozingoff:
Was This Post Helpful? 0
  • +
  • -

#11 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

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

Re: how export data to MS excel through vb

Posted 02 August 2010 - 04:39 AM

yes there is.
'Portrait'
oSheet.PageSetup.Orientation = 1
'Landscape'
oSheet.PageSetup.Orientation = 2


Was This Post Helpful? 0
  • +
  • -

#12 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: how export data to MS excel through vb

Posted 02 August 2010 - 07:04 AM

use the copyrecordset function it is much easier than ram11's code
Was This Post Helpful? 0
  • +
  • -

#13 angeliquev2+  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 17-February 13

Re: how export data to MS excel through vb

Posted 17 February 2013 - 08:48 PM

Hi the line codes here doesn't work to me,

[code]Dim NumberOfRows As Integer
[code]13 NumberOfRows = adoDRF.Recordset.RecordCount

the highlighted error is in line 13. it gives me the Run time error '91 "object variable or with block not set"

please, please reply to my query. I am doing a program which saves the report to excel .

thank you
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1