running vb6 for displaying data from excel files

[finnyjoseph]

Hi

I would like to know whether a set of financial data files in excel could be actually queried using vb6 as an interface with appropriate gui?

Regards
Finny

[jvmills]

I think you can specify any thing from a relational database (mysql, sql server), a spreadsheet (excel), to a text file as a data source for your app.

Set the connection string for your recordset to point to a spreadsheet (rather than a db) eg:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

"HDR=Yes;" indicates that the first row contains columnnames, not data

"IMEX=1;" tells the driver to always read "intermixed" data columns as text

TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets.

PS the above was taken from connectionstrings.com

[finnyjoseph]

Thanx so much.

Could we also use vlookups and other formulas in VB 6.0 when accessing excel files the way we use in Excel.

Rgds Finny

[jvmills]

I would assume so, but not having it done it I cannot say for sure.

You could look at the option of importing your spreadsheet data into a relational db (Access, SQL), then connecting to that as your data source.

[finnyjoseph]

Thanx..!

I am presently working on this application to prgogarm the excel files using vba.

I would appreciate if you could help me out in my furtrher queries.

Than so much for yor time.

Rgds
Finny Joseph

[jvmills]

My VB6/VBA is little bit rusty now, but post here and someone should be able to answer your questions.

Best of luck!