create excel file without excel installed in vb6.0-VBForums
Page 1 of 2 12 LastLast
Results 1 to 40 of 41

Thread: create excel file without excel installed in vb6.0

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Oct 2012
    Posts
    28

    create excel file without excel installed in vb6.0

    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.

  2. #2
    Lively Member
    Join Date
    Apr 2014
    Location
    Munich, Germany
    Posts
    79

    Re: create excel file without excel installed in vb6.0

    Quote Originally Posted by krunal.itaction View Post
    is it possible to create excel file without excel installed on machine in vb6 code?
    Set a reference to the "Microsoft DAO 3.6 Object Library", then try this code:

    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
    HTH,
    Wolfgang

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    17,528

    Re: create excel file without excel installed in vb6.0

    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.

  4. #4
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,118

    Re: create excel file without excel installed in vb6.0

    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.

  5. #5
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    2,945

    Re: create excel file without excel installed in vb6.0

    Quote Originally Posted by MarkT View Post
    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.
    In order to understand recursion, one must first understand recursion….

  6. #6
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,144

    Re: create excel file without excel installed in vb6.0

    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.

  7. #7
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,268

    Re: create excel file without excel installed in vb6.0

    Quote Originally Posted by DataMiser View Post
    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.

  8. #8
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    16,537

    Re: create excel file without excel installed in vb6.0

    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.
    Quote Originally Posted by TysonLPrice View Post
    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.
    A bit surprised myself. Appears the format is Excel alright. I tried it on a machine that does NOT have Excel installed.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VB and DPI Tutorial} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  9. #9
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,118

    Re: create excel file without excel installed in vb6.0

    Cool! I would have tested myself but I don't have a machine that doesn't have excel installed.

  10. #10
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    557

    Re: create excel file without excel installed in vb6.0

    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.

  11. #11
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    16,537

    Re: create excel file without excel installed in vb6.0

    Quote Originally Posted by Navion View Post
    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 system
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VB and DPI Tutorial} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  12. #12
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    16,537

    Re: create excel file without excel installed in vb6.0

    Quote Originally Posted by MarkT View Post
    Cool! I would have tested myself but I don't have a machine that doesn't have excel installed.
    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.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VB and DPI Tutorial} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  13. #13
    Fanatic Member
    Join Date
    Jan 2006
    Posts
    557

    Re: create excel file without excel installed in vb6.0

    Quote Originally Posted by LaVolpe View Post
    assuming functionality remains in future dll revisions.
    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.

    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

  14. #14
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,144

    Re: create excel file without excel installed in vb6.0

    Quote Originally Posted by SamOscarBrown View Post
    Same (almost) here.....I used to export my data (for Excel) as .csv (comma separated values) ASCII files....Excel easily imports them.
    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.

    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

  15. #15
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,268

    Re: create excel file without excel installed in vb6.0

    Quote Originally Posted by DataMiser View Post
    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.

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

  16. #16
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    12,144

    Re: create excel file without excel installed in vb6.0

    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

  17. #17
    Frenzied Member
    Join Date
    Mar 2008
    Posts
    1,055

    Re: create excel file without excel installed in vb6.0

    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.

  18. #18
    PowerPoster
    Join Date
    Oct 2013
    Posts
    2,847

    Re: create excel file without excel installed in vb6.0

    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

  19. #19
    Fanatic Member
    Join Date
    Jan 2013
    Posts
    567

    Re: create excel file without excel installed in vb6.0

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

  20. #20
    PowerPoster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    2,945

    Re: create excel file without excel installed in vb6.0

    Quote Originally Posted by Phill.W View Post
    (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.

    Occam's razor: Maybe it can be taken at face value. The OP just wondered if you could.
    In order to understand recursion, one must first understand recursion….

  21. #21
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,268

    Re: create excel file without excel installed in vb6.0

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

  22. #22
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    3,043

    Re: create excel file without excel installed in vb6.0

    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.CreateExcelTable.zip

    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

  23. #23
    Frenzied Member
    Join Date
    Dec 2008
    Posts
    1,045

    Re: create excel file without excel installed in vb6.0

    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

  24. #24
    Frenzied Member
    Join Date
    Jan 2010
    Posts
    1,085

    Re: create excel file without excel installed in vb6.0

    FYI, Paul Squires's excel class or Arnoutdv's xlsx doesn't support Unicode.

  25. #25
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,268

    Re: create excel file without excel installed in vb6.0

    Quote Originally Posted by Bobbles View Post
    PS Pity we don't allow PayPal donations
    Ah.....but I am sure you get rewarded just in satisfaction!


    (Haven't seen you on the site for awhile...welcome 'back'.)

  26. #26
    PowerPoster
    Join Date
    Oct 2013
    Posts
    2,847

    Re: create excel file without excel installed in vb6.0

    Quote Originally Posted by Jonney View Post
    FYI, Paul Squires's excel class or Arnoutdv's xlsx doesn't support Unicode.
    Updated to support Unicode -> UTF8

  27. #27
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    3,043

    Re: create excel file without excel installed in vb6.0

    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.

  28. #28
    PowerPoster
    Join Date
    Oct 2013
    Posts
    2,847

    Re: create excel file without excel installed in vb6.0

    Quick search revealed this C sample using the same API WideCharToMultiByte:
    http://www.codeproject.com/Articles/...g-based-conver

  29. #29
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    3,043

    Re: create excel file without excel installed in vb6.0

    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.

  30. #30
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    16,537

    Re: create excel file without excel installed in vb6.0

    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:
    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
    Apologize if any typos. And found one. Had a comma after the field name in Create Table clause. Removed it.
    Last edited by LaVolpe; Mar 19th, 2015 at 09:35 AM.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VB and DPI Tutorial} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  31. #31
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,268

    Re: create excel file without excel installed in vb6.0

    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
    Opened Cn (had error)
    Removed comma (field1, VARCHAR....) (Had error)

    Runs fine, but opening text.xls shows 'field1' in A1, 'hello!' in A2. (Intended?)

  32. #32
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    16,537

    Re: create excel file without excel installed in vb6.0

    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)
    Last edited by LaVolpe; Mar 19th, 2015 at 09:40 AM.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VB and DPI Tutorial} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  33. #33
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,268

    Re: create excel file without excel installed in vb6.0

    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.

  34. #34
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,268

    Re: create excel file without excel installed in vb6.0

    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

  35. #35
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    16,537

    Re: create excel file without excel installed in vb6.0

    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
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VB and DPI Tutorial} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  36. #36
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,268

    Re: create excel file without excel installed in vb6.0

    In Excel, the 1st row of data is the header row.
    --- (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.

    Think we were both thinking "Access" vs "Excel" with regard to field/column names
    YUP!

    SO, as long as we recognize that the 'table name' will be the 'column header', all is fine. :-)

  37. #37
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    16,537

    Re: create excel file without excel installed in vb6.0

    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"
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VB and DPI Tutorial} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  38. #38
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,268

    Re: create excel file without excel installed in vb6.0

    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.

  39. #39
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    16,537

    Re: create excel file without excel installed in vb6.0

    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.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VB and DPI Tutorial} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  40. #40
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    16,537

    Re: create excel file without excel installed in vb6.0

    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:
    Code:
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\test.xlsx; Extended Properties=""Excel 12.0 Xml;HDR=YES"""
    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.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
    ...
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Icon Organizer/Extractor}
    {VB and DPI Tutorial} {XP/Vista Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.