Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2005-04-08
Medium Priority
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
  • 3
  • 2
LVL 16

Accepted Solution

JohnBPrice earned 1400 total points
ID: 13739672
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.

Author Comment

ID: 13888170

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

ID: 13888678
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.

Author Comment

ID: 13975157

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)

Author Comment

ID: 14532767

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

571 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