create excel file without excel installed in vb6.0

[SamOscarBrown]

Excellent....(rs.movefirst)...
Thanks for all the work you did....I may never use this, but it ill sit in my bag in case.

Sam

[Wolfgang Enzinger]

A follow up to this post. Actually came upon a need to create an XLS format without having MS Office installed. In post #2 above a DAO example exists. Here is a similar example using ADO:

Sub Main()
Dim cn As ADODB.Connection
Dim rs As  ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; data source=c:\test.xls; Extended Properties=""Excel 8.0;HDR=YES"""
cn.Open 

cn.Execute "Create Table Table1(field1 VARCHAR(255))"
Set rs = New ADODB.RecordSet
rs.Open "[Table1$]", cn, adOpenDynamic, adLockOptimistic, adCmdTable

rs.AddNew
rs.Fields(0).Value = "hello!"
rs.Update
rs.Close: cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

Diggin' out an old thread ... it looks like this solution, as well as my DAO code in post #2, doesn't work anymore. They somehow broke it.

[dilettante]

Working fine here. Somewhat more elaborate example.

[Wolfgang Enzinger]

Working fine here. Somewhat more elaborate example.

You're right, this works, thank you.

However, did you try the examples in posts #2 and #30, too? These used to work as well, but they do not anymore (tested in Win7 and 8.1 so far). Creating the XLS file actually happens, but in the same step error 3274 occurs (something like "the external table does not have the expected format" - translated from German here).

Anyway, your code shows that ADOX still does the job. Thanks for that.

Wolfgang

[wqweto]

To counter the spam here are links to free resources:

https://gist.github.com/wqweto/4eb3ecee2961ec2f60bf -- can export only tabular data in .xls/.xlsx/.xlsb/.csv files
https://github.com/wqweto/Biff12Writer -- can export pictures, multiple sheets, text formatting, etc. in BIFF12 (.xlsb) files

cheers,
</wqw>

[Calcu]

I know it's and old post, but if someone can help..

I'm using the first link from wqweto to export to excel an adodb recordset (https://gist.github.com/wqweto/4eb3ecee2961ec2f60bf -- can export only tabular data in .xls/.xlsx/.xlsb/.csv files)

It's working fine, I modified all the "stable" variables to this: [" & sTable & "], this way i can create table names with dots, and more chars.

But i have a problem when creating the destination recordset.

Here:

   '--- dump source recordset
    If Not rsSrc.BOF And Not rsSrc.EOF Then
        rsSrc.MoveFirst
    End If

    Do While Not rsSrc.EOF
        rsDest.AddNew
        For Each oFld In rsSrc.Fields
            rsDest.Fields(oFld.Name).Value = oFld.Value
        Next
        rsDest.Update
        rsSrc.MoveNext
    Loop

If I want to use a name of the field something llike "Cod. Postal" is not working due the "."

How can i change this line: rsDest.Fields(oFld.Name).Value = oFld.Value in order to accept the . and save the values? any Ideas?

Thanks for all


Edit:

I must check all possibilities but changing the name for the position it seems to be working fine, the code:

    Dim aa As Single
    
    Do While Not rsSrc.EOF
        rsDest.AddNew
        aa = 0
        For Each oFld In rsSrc.Fields
            rsDest.Fields(aa).Value = oFld.Value
            aa = aa + 1
        Next
        rsDest.Update
        rsSrc.MoveNext
    Loop

[wqweto]

Yes, escaping Workbook name needs some more attention in the code. Currenly the procedure expects this name to be quoted from the caller, i.e. use "[Cod. Postal]" when calling WriteToExcel, not "Cod. Postal" for Workbook name but obv. this is cumbersome and error prone.

I have no idea what is the problem with field names though and why would these need quoting when accessing Fields collection. Using numeric indexes is a solution to a problem that should never happen.

Btw, Dim aa As Single is dubious -- better use Long.

cheers,
</wqw>

[Calcu]

Thanks! did it with Long.