Hello Everyone,
is it possible to create excel file without excel installed on machine in vb6 code?
is there any code, dll, link is available?
Thanks.
Printable View
Hello Everyone,
is it possible to create excel file without excel installed on machine in vb6 code?
is there any code, dll, link is available?
Thanks.
Set a reference to the "Microsoft DAO 3.6 Object Library", then try this code:Quote:
Originally Posted by krunal.itaction
HTH,Code:Sub Main()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim t As DAO.TableDef
Set db = DBEngine.Workspaces(0).OpenDatabase("c:\test.xls", False, False, "Excel 5.0;")
Set t = db.CreateTableDef("table1")
t.Fields.Append t.CreateField("field1", dbText)
db.TableDefs.Append t
Set rs = db.OpenRecordset("SELECT * FROM table1", dbOpenDynaset, dbAppendOnly)
rs.AddNew
rs.Fields(0).Value = "hello!"
rs.Update
rs.Close
db.Close
End Sub
Wolfgang
Or you can do the same thing using ADO, which (a.) is still supported, unlike DAO, and (b.) comes with everything you need as part of Windows, so no DAO library to deploy.
Can you really create an Excel file using ADO or DAO even if Excel isn't installed? I didn't know that ADO or DAO loaded the necessary drivers needed to create Excel files.
I tested the code and it created the spreadsheet and then I could open it with Excel. Now I have Excel but I think that would have worked anyway.Quote:
Originally Posted by MarkT
I've never tried creating them using DAO or ADO.
I do sometimes have the need to create them in my apps and I generally use the export feature of my report tool to handle this task. Not only does it create the rows and columns needed but also formats the visual aspects as well so that when the xls file is opened in excel it looks very much the same as it does in the report preview of the program that generates it.
Same (almost) here.....I used to export my data (for Excel) as .csv (comma separated values) ASCII files....Excel easily imports them.Quote:
Originally Posted by DataMiser
Quote:
Can you really create an Excel file using ADO or DAO even if Excel isn't installed? I didn't know that ADO or DAO loaded the necessary drivers needed to create Excel files.
A bit surprised myself. Appears the format is Excel alright. I tried it on a machine that does NOT have Excel installed.Quote:
Originally Posted by TysonLPrice
Cool! I would have tested myself but I don't have a machine that doesn't have excel installed.
I stopped short of mentioning the .csv format. Most all my data files for most any of my programs are just plain csv. They can be shared by anyone, anytime anywhere, without even a second thought. KISS always works best.
Good point and agree completely. But... This could be a viable alternative, assuming functionality remains in future dll revisions. Like you, I'll stick with csv as I don't see a huge advantage of using an xls format if MS Office isn't installed on the systemQuote:
Originally Posted by Navion
Does kinda make sense. Would have to have basic Excel parsing ability to support xls format even if Office not installed on system. Think we all knew it could read xls, just wasn't sure it would export to xls. ADO can read/update mdb files if Office not installed; now, if someone wants to test it, may even create mdb files from scratch; we know it can read & update them.Quote:
Originally Posted by MarkT
No worry about that. In the CNC world, while there are many formats for driver programs to work with, the PLT format (HPGL ver 1) remains to this day the most standard way for file exchange, even more so than .dxf or G-code.Quote:
Originally Posted by LaVolpe
There are a few things in life that can't be killed ever, the 8 bits byte, .csv, .plt, wonderbread and VB6 are amongst those ;)
I do that also more often than not but of course that does not carry any formatting info over, just the data so the sheet has to be formatted after import.Quote:
Originally Posted by SamOscarBrown
The export tool I use allows for a very nice looking formatted file and is good for when they need to send a report via email and open it in excel.
I also use a PDF export tool for the report generator
Good point (formatting).Quote:
Originally Posted by DataMiser
I still do export some stuff in .csv, but the majority is straight to an Excel document file. Like you say, this keeps formats (like your report generator), and the folks with whom I deal are not experts in any computer-related field, so highlighted rows, cells, multiple tabs, etc are what I generally deal with so they can simple 'open the file'.
Curious if the ADO/DAO approaches allows the sending of format information....Haven't tried what was posted above (and probably won't), but was just curious.
(beautiful high 70's weather here on the US East Coast....going to get off the computer and go enjoy!!!).
Sam
A doubt that you would be able to do any formatting using DAO or ADO either, just create the columns and write the data possibly adjust the column widths but I would not count on it.
Little cooler here, was raining up until a little bit ago but still decent weather in the mid 60s
While you can squirt the data into a new xls with default/ minimal formatting, I understand it is possible to send it to an existing/ preformatted xls.
I posted a sample in the code library to create a XSLS file without Excel installed.
It does some cell formatting too.
http://www.vbforums.com/showthread.p...7-Excel-Writer
(OK, here's my candidate for "Dumb Question of the Week" ... )
You've had many good answers about how to create an Excel spreadsheet without the MS Excel application. Excellent.
My [dumb] question is this:
What's the point?
OK, you can create an Excel spreadsheet in these ways, but what is the User supposed to do with it, given that they don't have MS Excel installed with which to open it?
(I guess I'm missing something really, really obvious, here)
Regards, Phill W.
Just spitballing...maybe you would want to use it as some kind of data repository independant of the PC having Excel. Maybe you want data from remote sites sent to the "home" site in a common format.Quote:
Originally Posted by Phill.W
Occam's razor: Maybe it can be taken at face value. The OP just wondered if you could.
Or perhaps, PERHAPS, OPs program is distributed to various users, some of whom may or may not have Excel.....in case of those who didn't, and attempt an export in Excel format, OP would not want their program to 'crash'. Also, those without excel, may be just an itermediary, that is, they could CREATE the .xls(x) file and then email to someone who has Excel and needs that file....again, just perhaps. Phil, that is what I first wondered as well, but then, it is impossible to figure out everything about what OPs post here, so just offered my 2.5 cents (inflation).
I once wrote some code to do it in the raw. Let me see if I can find it. Yayy, I actually found it (or at least one version of it). See the attached ZIP file.Attachment 119685
This was actually written many moons ago as a VB6 type script for Adobe InDesign (before they went to Java as their scripting language).
The program will load just fine in the VB6 IDE, but it won't run as is (unless you also have a very old copy of InDesign also installed on your computer). However, all the basics for creating an Excel file from scratch are in the code. I'll let you sort that part out.
Just as an FYI, it's an older Excel format, but it'll open with any version of Excel.
Have Fun,
Elroy
This won an award at PSC
Excel Class - write to an XLS file without DLL's or Excel automation
http://www.planet-source-code.com/vb...11898&lngWId=1
It allows formatting
Rob
PS Pity we don't allow PayPal donations
FYI, Paul Squires's excel class or Arnoutdv's xlsx doesn't support Unicode.
Ah.....but I am sure you get rewarded just in satisfaction! ;)Quote:
Originally Posted by Bobbles
(Haven't seen you on the site for awhile...welcome 'back'.)
Updated to support Unicode -> UTF8 :)Quote:
Originally Posted by Jonney
Ahhh, hey, this is a bit off-topic, but does that code have a UTF-8 to UTF-16 converter in it? I was just discussing that very issue in another thread. I certainly wouldn't mind having that piece of code in my toolbox.
Quick search revealed this C sample using the same API WideCharToMultiByte:
http://www.codeproject.com/Articles/...g-based-conver
Ahhh, cool beans, Arnoutdv. It looks like these API calls will totally get it done. Something to do today for more procrastination of "real" work. I'll post my results in the codebank if I get it going.
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:
Apologize if any typos. And found one. Had a comma after the field name in Create Table clause. Removed it.Code: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
Opened Cn (had error)Code: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= " & App.Path & "\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
Removed comma (field1, VARCHAR....) (Had error)
Runs fine, but opening text.xls shows 'field1' in A1, 'hello!' in A2. (Intended?)
Thanx for fixing the typos.
As far as the field name being a cell value, that was not intended. May need to simply close the connection after creating the table & then re-opening. I'm assuming that HDR=YES is being ignored since the file & sheet didn't exist. Once closed/saved, just re-open & starting adding data. But probably more to it. I'll play with it when I get back on a VB machine, for my purposes, no header was needed. But I'd like to ensure an example exists that does create the header appropriately.
Tip: If needing a field with undetermined length, i.e., 255 may not be big enough, consider using: LONGTEXT vs. VARCHAR(255)
Played around a bit....can't seem to 'fix' that, however, if I place the 'value' of cell A1 to be, as the field name, it is fine. However, only table field naming conventions are allowed...like 'Hello!' throws an error.
Closing and opening connection had no impact. Closing and reopening recordset placed a blank field in A2, kept 'field1' in A1, and placed 'hello!' in A3.
Looking at it more.
It is definitely ignored, as cn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0; data source= " & App.Path & "\test1.xls; Extended Properties=""Excel 8.0""" produces same results...
Interesting, tho.
Thanks for posting this...may help others
Wait a second.
In Excel, the 1st row of data is the header row. Opening the file in Excel and if clicking on the option for a custom sort, Excel recognizes that the sheet does have a header row as indicated by the checked checkbox "My data has headers"
Think we were both thinking "Access" vs "Excel" with regard to field/column names
--- (Sometimes). When I open this test file, and go to sort, the 'has headers' is not checked, so sorting works with all rows. Then, of course, if I select that option, you are correct.Quote:
In Excel, the 1st row of data is the header row.
YUP!Quote:
Think we were both thinking "Access" vs "Excel" with regard to field/column names
SO, as long as we recognize that the 'table name' will be the 'column header', all is fine. :-)
Sam, the Table name is the sheet name, not the column header. In the sample I posted, the sheet should be titled "Table1" and cell A1 (column 1 header) should be titled "field1"
Yup....sure 'tis. I misunderstood your explanation in #25.
I DO see an issue though....let's say I want my Header in cell A1 to be "LAVOLPE!". What you posted would result in an error (field name) when the code is run because of the special character (!).
Anyway...you have definitely answered OP's question. Hope he is still around to benefit from your wisdom.
To answer your point, we would want to create a worksheet without headers, i.e., HDR=NO or have table created without writing the field names to the file. That would require more playtime ;)
I think the OP had this resolved with using DAO. Needing this ability, I didn't want to use DAO. Remembered this post & just wanted to update it with an ADO example for future readers.
Likely my final contribution to this thread...
Regarding the sample code in post #30 & 31 above...
1. To enable Sorting/Filtering, include this line of code before opening the recordset:
rs.CursorLocation=adUseClient
2. If needing more than 65536 rows, you can't use Jet.4.0, instead this connection string could be used if the provider (ACEOLEDB.DLL) is on your system. Notice the file extension changed to xlsx:
3. Seems you cannot create a new sheet without a header row. Whatever columns/fields are provided in the "Create Table" clause will be added to the 1st row of the new spreadsheet. However, there is nothing saying you can't simply edit that first row & change all the values, i.e.,Code:cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\test.xlsx; Extended Properties=""Excel 12.0 Xml;HDR=YES"""
Code:...
cn.Execute "Create Table Table1(F1 Char(255), F2 Char(255))" ' etc, add as many columns as you will need
Set rs = New ADODB.Recordset
rs.Open "[Table1$]", cn, adOpenDynamic, adLockOptimistic, adCmdTable
' modify the first row, getting rid of the F1, F2, etc entries
rs.MoveFirst
rs.Fields(0).Value = "Hello: rs.Fields(1).Value = "World"
' same result: rs.Fields("F1").Value = "Hello: rs.Fields("F2").Value = "World"
rs.Update
...
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
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. :mad:Quote:
Originally Posted by LaVolpe
Working fine here. Somewhat more elaborate example.
You're right, this works, thank you.Quote:
Originally Posted by dilettante
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
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>
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:
If I want to use a name of the field something llike "Cod. Postal" is not working due the "."Code:'--- 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
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:
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
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>
Thanks! did it with Long.