Linking Excel

[gvirden]

I want to link to excel, do some math, then write the cell to a label in my form.

    Dim XL As Excel.Workbook
    Set XL = GetObject("C:\cat1(current)\stocking.XLS", "Excel.Sheet")
    XL.Application.Visible = True
    XL.Application.Windows("Stocking.XLS").Visible = True
    Label1.Caption = XL.Range("C10")

Everything worked fine until the last line. How is that done?

[Clunietp]

try this:

Label1.Caption = xl.Application.Cells(6, 2).Value

[gvirden]

Tom I was wondering where you were off to.

This is what I am stuck on now. I've created a fairly complex template in excel. The user opens the worksheet from the vb form and fills in the appropriate cells. Excel performs all the necessary calculations. The worksheet is saved and the user goes back to the vb form. A click event writes the coded excel worksheet cells to the designated labels on the vb form.

This is the problem. When I open the excel worksheet template I need to do a save as. Then it can be filled out etc without ruining my template. But when I go back to the vb form to perform the click event I manually have to tell the program were to find this save as worksheet.

What I want to do is write code that will do a save as when the template is opened, write to a specific location as a variable, save the location, and when I save the new worksheet the code picks up the location in the click event and writes the cells to my labels on the form.

[Clunietp]

use a commondialog box (via the control or API), retrieve the path to the file in a variable. Use the SAVE command in EXCEL (or maybe it's SAVEAS) and pass the path as a parameter to it. Now that you have the variable, you can store it and use it later.

yes?

[JHausmann]

In VB you can save the Excel worksheet with

XLobject.Workbook.SaveAs "C:\savefile.xls"