Load Excel sheet on VB6 form, then save it to a MDB.

Posted on 2005-04-08
Last Modified: 2008-02-01

I use VB6, and an access database using DAO (I know its old but the application is old too. I will soon migrate to the "new" ADO... I hope so...)

Well, I have a record that includes some fields, but I want to add an excel sheet to each row.

Because I need to add special information to each costumer...  graphics, tables, etc. And the best solution is to add an excel sheet to each costumer and personalize each document and keep it inside the MDB (Access database.)

I do not know if I would require an special component, or an Office version on each target machine.
I do know how to save a picture to an OLE field, so maybe I could use the same to save it to the MDB.... but.... how can I load it on the form?

(If anyone wants the way to save to an OLE field, just ask.)

And If anyone knows what to do with the loading-xls file to the form, please tell me. I'd like to have some toolbars and stuff available.

Thank you all,
Question by:jag_dev
    LVL 16

    Accepted Solution

    You want to add an Excel worksheet in your VB6 form?  Saving the file in the Access mdb?  That is pretty easy.  Making the excel worksheet behave nicely in a VB 6 is a trick I haven't conquered (I would switch to a spreasheet control like Formula1).

    Just drop an OLE control on your form, leave it unbound.  When you want to display a record, copy the Excel file out of the database into a temp file, and use
    OLE1.CreateLink "C:\MyTempFile.xls" 'Edits the actual file
    OLE1.CreateEmbed "C:\MyTempFile.xls" 'makes a copy using the file as a template

    When saving the record use OLE1.SaveToFile.

    But activating and allowing the user to edit within the spreadsheet gets dicey.
    LVL 1

    Author Comment


    I appreciate your comments, I had already used the OLE control, but I wanted a way to modify the xls file inside the form, maybe an activex control or something. Are there any options for this?

    ...So what you have done is displaying the xls inside the ole control, maybe putting some scroll bars to view part of the xls-file [I cannnot see all the sheet inside the ole control, even if I set its size mode to autosize, do u know why?] and when editing you use an excel instance and then save it .... and then come back to our application, right?

    I guess I have to accept your answer since you were the only one who helped ;)
    I'll leave it open a couple of days to see if I get more comments on this.

    Thanks 4 your help John.
    LVL 16

    Expert Comment

    To make changes programatically, you need (well you should) use Excel, e.g. add the "Microsoft Excel 11.0 Object Library" to your references, and once the ole control contains an excel workbook, you can do something like this....

        Dim wkbook As Excel.Workbook
        Dim wksht As Excel.Worksheet
        Set wkbook = OLE1.object
        Set wksht = wkbook.Worksheets(1)
        wksht.Cells(1, 1) = "Hi there"

    The size for in-place activation is dependent upon the size of the OLE control.  Typically one would change the control's size when the form is resized, thus allowing the user to control the size by resizing the form.  Alternatively you can activate it in a separate spreadsheet using vbOLEOpen

    No, you don't have to accept my answer.  If you question is un-answered, you may post a request in Community Support that your question be deleted and points refunded.
    LVL 1

    Author Comment


    Thanks a lot John,

    I will accept your answer since you provided good help. And no one seems to know a way to do it easily and smoothly....

    You said,
    "Making the excel worksheet behave nicely in a VB 6 is a trick I haven't conquered"
    When you achieve that, please let me know ;)

    If I do it first I'll post it here....


    (P.S. if someone finds an ActiveX control that allows excel files editing, let me know)
    LVL 1

    Author Comment


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Article by: Martin
    Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now