[kartikchoudhary]

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!!!!!!!!!!!

[raziel_]

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

[guyfromri]

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!!

[kartikchoudhary]

@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

[ram11]

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 

[guyfromri]

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!

[kartikchoudhary]

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

@ guyfromri

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

[gen009]

kartikchoudhary, on 23 July 2010 - 04:21 PM, said:

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

@ 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..

[kartikchoudhary]

gen009, on 26 July 2010 - 10:18 PM, said:

kartikchoudhary, on 23 July 2010 - 04:21 PM, said:

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

@ 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..

Ohhh rather i should ahve done my work in excell it self i think iam giving too much pain to my mind

[kartikchoudhary]

ram11, 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....

[raziel_]

yes there is.

'Portrait'
oSheet.PageSetup.Orientation = 1
'Landscape'
oSheet.PageSetup.Orientation = 2

[thava]

use the copyrecordset function it is much easier than ram11's code

[angeliquev2+]

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