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

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,
Who is Participating?
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.
jag_devAuthor Commented:

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.
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.
jag_devAuthor Commented:

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)
jag_devAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.