Reading a current Excel spreadsheet

I found an example in the VB5 manual of writing to an Excel file and then reading back from it - however - I need to be able to READ from an active worksheet.

The user would enter info into an Excel worksheet, Excel would do any calculations...the application would then read the cells from the worksheet and display in text boxes.

Any help would be greatly appreciated!

Thanks.

pixelperfect

[shanzek]

Just off the top of my head (flat as it is) how about using the Excel Object Library on a separate form, so you are using a 'mini-excel' application that may be easier to get data out of? Will you always be grabbing the same cells from Excel (ie A27, B52, etc) or will they vary?

I will more than likely be using the same cells, however, there is a small chance that it could vary to some degree. I will look into the Excel Object Library. Can I even use it on the same form?

[shanzek]

You can use it on the same form; unfortunately I can't tell you more than that yet. You see, I've never actually done this...
However, 'theoretically' it should be possible. I'll experiment tomorrow (Friday) at work and see if I can put together some sample code that works.
Steve

[Nitro]

Hello PixelPefect!

I am not sure what you are asking, but lets start off with these codes to connect to your active excel spreadsheet. It will start a new instance of that file.

Use something like this.

'Reference "Microsoft Excel 9.0 Library"
Dim xlsWorkBook As Excel.Workbook
Dim xlsWorkSheet As Excel.Worksheet

Set xlsWorkBook = GetObject("C:\City Charts.xls")
Set xlsWorkSheet = xlsWorkBook.Worksheets("Your activesheet name")
Text1.Text = xlsWorkSheet.Cells(1, 1).Value
xlsWorkBook.Close

xlsWorkSheet.Cells(1, 1).value = This represent cell A1.
xlsWorkSheet.Cells(2, 1).value = This represent cell A2
xlsWorkSheet.Cells(1, 2).value = This represent cell B2

or you can use

xlsWorkSheet.Range("A1").value

Program it exactly like the way how you would program in Excel, except put "xlsworksheet."

If you need more code, post your email address and I will send you samples of what you need.

Good Day!

[Edited by Nitro on 04-28-2000 at 01:05 AM]

[svskumar]

Hi Nitro

Iam also doing some thing similar
But i want to display the excel sheet within the form

Send me whatever u have to [email protected]
and iam using vb5 sp3 Excel 8.0 control

[Nitro]

Click on Project-Component-Insertable Object and check Microsoft Excel Worksheet.

[svskumar]

iam doing the same but i need to know how to associate it with an xls file because there seems to be no property.
If u can send me a sample form it will be nice

[Iceman10]

whats the version on vb? If your using vb 6 how about using a data bound control and grid. I know you can set the proprities to get data from an excel file. then bind the grid to data bound control. this all simple stuff that this site actually gos over. look at the databas section

[Iceman10]

i think you can do this i vb 5 too.

[svskumar]

Iam using VB 5 SP3 Please send me the code to [email protected]

[Nitro]

iam doing the same but i need to know how to associate it with an xls file because there seems to be no property.
If u can send me a sample form it will be nice

-Drop an Ole control on the form
-Right click on the control and select "Insert Obeject..."
-Select on the radio button "Create from file"
-Select your Excel files
-Check on the "Link"


There you have it. You can do this will any type of file.

You should use the following code to refresh during run time.
-"ole1.Refresh"

You can refresh it during design time, by right click on the control and open, close, then open it again and close.


Is that what you are looking for?

[svskumar]

Well I need to do it thru code that is attach a file at run time ..

Thanks

Satish

[Nitro]

Drop an OLE control on your form. You can hide it if you want.

use the following codes:

OLE1.CreateEmbed "C:\Report.xls"

or

OLE1.InsertObjDlg





You can also stick it into a class module if you wish.

[Edited by Nitro on 05-01-2000 at 10:49 AM]