MS Access, MS Excel and VB6

Migrating from Access to Excel in VB6

Page 1 of 1

8 Replies - 4657 Views - Last Post: 19 February 2010 - 12:45 PM Rate Topic: -----

#1 paulmurley  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 17-February 10

MS Access, MS Excel and VB6

Posted 18 February 2010 - 07:54 PM

this proj gets data from a DataBase(Acess) and puts it in multiple sheets on a Spreadsheet(Excel)
OK I cant put this in snippets co's it's a whole project, references.jpg shows the references for the project, the only one I had to add manually was for "Microsoft Excell 11.0 Object Library" that is where your MS Excel is installed - (You will need to change the file type to 'Executable files" when you are in browse mode in Project>References - Also you will need to email me for the password to open the project - I don't mind giving it away but it would be nice to know who has got it :bananaman: if anybody can make the proj work with OpenOffice I would be eternaly grateful

Attached image(s)

  • Attached Image

Attached File(s)



Is This A Good Question/Topic? 0
  • +

Replies To: MS Access, MS Excel and VB6

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: MS Access, MS Excel and VB6

Posted 18 February 2010 - 08:14 PM

Send me a personal message via DIC with the password.
Was This Post Helpful? 0
  • +
  • -

#3 paulmurley  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 17-February 10

Re: MS Access, MS Excel and VB6

Posted 18 February 2010 - 08:26 PM

i can't PM yet - is my email adress not in my profile ??? :?:
Was This Post Helpful? 0
  • +
  • -

#4 ram11  Icon User is offline

  • D.I.C Head

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

Re: MS Access, MS Excel and VB6

Posted 18 February 2010 - 10:38 PM

For Access to Excel

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

#5 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

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

Re: MS Access, MS Excel and VB6

Posted 19 February 2010 - 12:32 AM

View Postpaulmurley, on 18 February 2010 - 06:54 PM, said:

this proj gets data from a DataBase(Acess) and puts it in multiple sheets on a Spreadsheet(Excel)
OK I cant put this in snippets co's it's a whole project, references.jpg shows the references for the project, the only one I had to add manually was for "Microsoft Excell 11.0 Object Library" that is where your MS Excel is installed - (You will need to change the file type to 'Executable files" when you are in browse mode in Project>References - Also you will need to email me for the password to open the project - I don't mind giving it away but it would be nice to know who has got it :bananaman: if anybody can make the proj work with OpenOffice I would be eternaly grateful


hello dude :)
i found this :
http://www.kalitech...._APIOOo_en.html

i think it will help you take a look :)
Was This Post Helpful? 0
  • +
  • -

#6 paulmurley  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 17-February 10

Re: MS Access, MS Excel and VB6

Posted 19 February 2010 - 08:29 AM

View PostNoBrain, on 19 February 2010 - 06:32 AM, said:

hello dude :)
i found this :
http://www.kalitech...._APIOOo_en.html

i think it will help you take a look :)


its written in StarBasic it won't run in VB Thanks anyway.
i've managed to open and close the openoffice calc files but can't get past that :(

This post has been edited by paulmurley: 19 February 2010 - 08:31 AM

Was This Post Helpful? 0
  • +
  • -

#7 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

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

Re: MS Access, MS Excel and VB6

Posted 19 February 2010 - 08:52 AM

ah excuse me.
see this
http://www.dreaminco...showtopic=82693
thanks to ram11 :)
Was This Post Helpful? 0
  • +
  • -

#8 paulmurley  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 17-February 10

Re: MS Access, MS Excel and VB6

Posted 19 February 2010 - 12:26 PM

View PostNoBrain, on 19 February 2010 - 02:52 PM, said:

ah excuse me.
see this
http://www.dreaminco...showtopic=82693
thanks to ram11 :)


Thanks NoBrain that was just what I needed only thing left is saving the damned sheet when I've put all the data in - i'll ask Ram11
Was This Post Helpful? 0
  • +
  • -

#9 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

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

Re: MS Access, MS Excel and VB6

Posted 19 February 2010 - 12:45 PM

no problem dont forget to thank him on the link i send you :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1