Exporting data to Excel from vb6 [RESOLVED]

[Siddharth Rout]

I am writing a program where I am collecting data from different users. It could be say their contact details...

I have two questions...

QUESTION ONE: Every day a new excel file should be created. The name of that file could be the date, say ddmmyyyy.xls

QUESTION TWO: How do I transfer the data to DDMMYYYY.xls?

a)if it has to be exported from access database to excel then how do i do that programatically?

b)if different users update the form at the same time then how do i update the excel file at the same time?

rgds

koolsid

[si_the_geek]

1. Set your filename like this:

Dim sFileName as String
sFileName = "c:\my folder\" & Format(date, "ddmmyyyy") & ".xls"

2. a) Set up the filename as above, and get the data into a recordset. Then do this:

Dim oXLApp as Object       'Declare the object variables
Dim oXLBook as Object
Dim oXLSheet as Object
  Set oXLApp = CreateObject("Excel.Application")  'Create a new instance of Excel
  If Dir(sFileName) = "" Then
    Set oXLBook = oXLApp.Workbooks.Add  'File doesnt exist - add a new workbook
  Else
    Set oXLBook = oXLApp.Workbooks.Open(sFileName)  'File exists - load it
  End If
  Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet
 
  oXLSheet.UsedRange.Clear
'replace "oRecordset" with the name of your recordset
  oXLSheet.Range("A1").CopyFromRecordset [b]oRecordset[/b]
 
  Set oXLSheet = Nothing               'disconnect from the Worksheet
  oXLBook.SaveAs sFileName  'Save (and disconnect from) the Workbook 
  oXLBook.Close SaveChanges:= False
  Set oXLBook = Nothing
  oXLApp.Quit                                'Close (and disconnect from) Excel 
  Set oXLApp = Nothing

This assumes that you want all of the data to be replaced, if this isnt the case you'll need to use different code instead of the middle section.

2. b) erm.. basically you can't, but this is very quick so it shouldn't be an issue.

[Siddharth Rout]

Thanx si_the_geek

I'll try that code right away.

I can get the data into a recordset from an access table but i haven't tried the same with excel... any tips on that???

any components/references to be selected before i go ahead with the coding?

cheers mate

[si_the_geek]

I can get the data into a recordset from an access table but i haven't tried the same with excel... any tips on that???

I'm not sure what you mean.. I thought you wanted to get the data from Access? The code I posted will take a recordset (which you have got from anywhere you like) and put it into Excel.

any components/references to be selected before i go ahead with the coding?

As I have posted it, no. I did it this way to save issues when it is deployed to your users (different versions of Excel still work with this code, but not the "proper" version below).

If you want to make changes to the code yourself then I would recommend adding "Microsoft Excel X.X Object Library", and changing the "Dim .." lines to:

Dim oXLApp as Excel.Application       'Declare the object variables
Dim oXLBook as Excel.Workbook
Dim oXLSheet as Excel.Worksheet

..and changing it back when you deploy it to your users.

ps: I spotted an error in the Dim lines in my post above, I have now changed it!

[Siddharth Rout]

Sorry to confuse you... even i got confused for a sec

Amazing it works...

Thanks a ton si_the_geek

[si_the_geek]

No problem, I'm happy to help

[Vespertilio]

Hi to all. I've got the same problem... exporting data from vb 6.0 to excel... I'm new with programming and I really need help. I have made a form in visual basic with data for partnes from ms sql base - fields are ID, Name, Adress, City... and that information i have to put in excel file... I've tried to change the code from that you wrote it above, but it can't work... Please help me. Here is a picture how it must looks like...

sorry if my eng is not very good...

Edit: Problem is resolved!

[Antony S]

Dim WoStr As String
Dim WoCon As New ADODB.Connection
WoStr = "PROVIDER=MICROSOFT.JET.OLEDB.3.51; DATA SOURCE = "databasename.mdb;Jet OLEDB:"
WoCon.ConnectionString = WoStr
WoCon.Open
Dim Exl As New ADODB.Recordset
Exl.Open "Select Fld1, Fld2 from StpOrder", WoStr, 1, 2
Dim ObjExcel As New Excel.Application


Dim XLCoun As Integer
With ObjExcel
.Visible = True
.Workbooks.Add
XLCoun = 6
For i = 0 To Exl.RecordCount - 1

.Range("A" & XLCoun).Select: .ActiveCell.FormulaR1C1 = Format(Exl!Fld1, "dd-mm-yyyy"): .ActiveCell.ColumnWidth = 12
.Range("B" & XLCoun).Select: .ActiveCell.FormulaR1C1 = Exl!Fld2
XLCoun = XLCoun + 1
next
end with