Runtime connecting Excel file to VB6 application

[vbhvw]

Hello,

After two full days of continuous searching/trying/reading/experimenting I hope to find an answer to my problem in this forum. ;-)

I like to connect an arbitrary Excel file to my VB6 application at RUNTIME and automatically connect textboxes or any other data-modifying-object to Excel fields to modify the Excel data.

I manged to do this at designtime but I do not find any solution to connect/disconnect an Excel file at runtime.

Any tip is appreciated very well.

Regards,

Henk

 

[SkipVought]

Hi,

Have you checked out GetObject? Take a look ath the HELP on it for an example.

Skip,
[sub]
[red][/red]
[/sub]

 

[vbhvw]

Hello,

I think GetObject opens an Excel Application/Worksheet.

But I only want to use the Excel Worksheet as a kind of 'invisible' database that can be updated from VB6.

As my VB6 application is used to control mobile testers the serviceman get his working addresses from an Excel sheet that is supplied by our mother company. If there are some errors in the custum database (excel sheet) then the service man must be able to modify it directly at location with the VB6 testapplication.

regards,

Henk

 

[fredericofonseca]

It does open an excel application, but this does not need to be visible.

But why not use an Access MDB instead? using ADO you can do that easily, and you do NOT need to have a full license of Access to either develop or distribute, as you only need to use the MDB as storage.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SkipVought]

Unlike the folks that stated, "Do not attempt to adjust your monitor... we control the horizontal... we control the vertical..."

YOU are in control!

Just assign the Visible property of the Excel Object.

Skip,
[sub]
[red][/red]
[/sub]

 

[vbhvw]

Hello,

but how do I implement an MDB-Database into VB6 and import data from an Excel-file at RUNTIME in this MDB-database?

This Excel-file must be selectable from the VB6 application because this Excel-file will be subject to updates.

I just need some hints what objects and functions to use to accomplish this (avoiding again searching for days for a solution...) and I will find out myself with use of the Help-functions ...;-)

For example to connect an Excel-sheet to VB6 you will need: ADODB.Recordset, ADODB.Connection, (.Provider, .ConnectionString, .Cursorposition) ADODB.Open and most important the ADODB.Connection.OpenSchema.

regards,

Henk

 

[JerryKlmns]

Connecting to excel with ADODB.Connection (OLE DB)

http://www.connectionstrings.com

scroll down for excel and also read the tips!

 

[vbhvw]

okay,

but then,

how do I connect a textbox to this database so I can edit a specific field of that database?

As I'm used in MSAccess, just connect a textbox during RUNTIME with a specific database/excel field and voila, you can edit the data. I did not find anything like that with VB.

regards,

Henk

 

[SkipVought]

What's your objective here?

Are you doing a parameter query?

What database table are you trying to modify?

Skip,
[sub]
[red][/red]
[/sub]

 

[vbhvw]

Hello,

Maybe I did not explain my problem clear enough.

My VB6 application must be able to modify existing Excel files.
All these Excel files have the same structure (1 WorkSheet, 5 fixed Columns)
only the number of rows (Records) may differ from file to file.

The VB6 application must be able to step through the Excel rows
as records and each individual column-field (5 in total) in that
Excel file must be connected to a textbox in the VB6 application
so the user can modify the 'current record' data via these 5
textboxes.

The Excel file itself must not be visible.
The modified Excel file later must be visually inspected in
the Excel application itself.

The VB6 application must be able to select another Excel file
(with the same fixed structure) to be modified.

So more or less I want each Excel-file to act as a kind of
database.

I know from MSAccess and Borland Builder C++ that you can
easily BIND a textbox to a specific database field.

So you don't need something like:
MyTextBox.Text = Database.Field(2).Value
or something like that to copy the data from the Excel field
into the textbox.

When BINDING that textbox to a specific field (if possible)
that field value is immediately displayed in the textbox and
can be edited and is automatically updated in the underlying
database.

I hope this long story makes things more clear.

My problem is, I can open an Excel file but I see no solution how
to BIND a specific textbox to any database field (Excel column)
because a textbox needs a 'DataSource' and I cannot find a way to
combine for example an ADODB object with a 'DataSource'...;-)

regards,

Henk

 

[JerryKlmns]

The easy way would be to use an ADODC control on your VB app, and use the appropiate connection string for the excel file to open. Then bind your controls to the ADODC.

In your app you should provide a way, in a form, for the user to select the excel file (not exactly browse for the file but a name) and then create an connection string for the ADODC and then open it. Another form that edits the data in excel, has the ADODC on and the bound controls.

The ADODC control is the fast - easy - less controlable way to do it. The other way would be to code the operations that this control is supporting using
-ADO.Connection for the connection
-ADO.Command for parameter queries
-ADO.Recordset to play with records

The best way should be an MS-Access where you can do things better. MS-Access is not necessary to be installed on user's PC as Frederico has stated before.

 

[vbhvw]

>
>Then bind your controls to the ADODC.
>

Well, this is my actual question which I keep repeating over and over again but get no answers only echo's. ;-)

I know I have to bind, but my question is simply HOW do I bind my TextBox to an ADODC object?

A precise code example would make me very glad.
Just a few essential code lines will do.

Regards,

Henk

 

[fredericofonseca]

If you wish to keep using ADODC and bound controls (which is bad by the way!!, just search these forums and the net for BOUND vs UNBOUND controls), then one way for you to get a sample code is to use the VB6 wizard to create a data application.

This will create at least one form with a ADODC control, and it will also ask you for the DB to use (Excel through ODBC on your case), along with form fields bound to individual names on the spreadsheet.

Try it.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[JerryKlmns]

vbhvw,

I don't use bound controls.-
The first time I did... I had a headache.

I believe to bind a text box to an ADODC you use the DataSource property of the textbox and the DataField for defining the field of returned recordset

But I could be wrong. Try the very helpful key next to your Esc key. Usually... gets you started

 

[BobRodes]

To bind to an ADODC you need to do the following (assumed that you have a design time instance of the control already on a form):

1. Set the data control's connectionstring property to a valid connection string. You can use the builder. An example would be "C:\Program Files\Microsoft Visual Studio\VB98\NWIND.mdb". This one will work for you, too, if NWIND.mdb is in the default location for a VB IDE install.

2. Set the data control's recordsource property. e. g. "select * from customers"

3. Set the text box's datasource property to the ado data control (default name adodc1).

4. Set the text box's datafield property to one of the field names that will be returned by executing the statement in the data control's recordsource property. For example, "customerid".

That's it. Now, that said, you can do all of that with ADO objects, and with more control over your process. The process of binding is pretty much universally panned in VB6 for production level applications.

HTH

Bob