Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Open a closed Excel workbook

Hi,

How do I access an Excel workbook that is closed using VB?
I need to access the value at a particular cell (I20)

Is it also possible to access the same cell across different workbooks and then sum up their value?

Please help!!

Thanks in anticipation of all you help.


Comments

  • dokken2dokken2 Posts: 532Member
    : Hi,
    :
    : How do I access an Excel workbook that is closed using VB?
    : I need to access the value at a particular cell (I20)
    :
    : Is it also possible to access the same cell across different workbooks and then sum up their value?
    :
    : Please help!!
    :
    : Thanks in anticipation of all you help.
    :
    :
    :
    Use Automation [you'll need to set a reference to excel], this example pulls the cell values from A1-A5 and I20. If your workbooks are saved to separate Excel files it would be trivial to open/close each file to get any cell value [you may want to move the form load/unload code to some type of loop for your files].


    [code]
    Option Explicit

    'VB6 MENU - PROJECT , REFERENCES, set a refernce to:
    'Microsoft Excel 10.0 Object Library

    Const cMyExcel = "MyExcel.xls"
    Dim cPath As String

    Private mvarExcel As Excel.Application
    Private Workbook As Object

    Private Sub Command1_Click()
    'SHOW EXCEL AND OPEN A FILE
    'workbook.Activate
    mvarExcel.Visible = True 'SHOW EXCEL
    mvarExcel.Workbooks.Open cPath & cMyExcel 'OPEN A FILE
    End Sub

    Private Sub Command2_Click()
    'GET VALUES FROM CELLS
    Dim row As Integer
    Dim wb As Object
    Dim ws As Worksheet

    Set wb = mvarExcel.Application.ActiveWorkbook
    Set ws = wb.ActiveSheet

    'DISPLAY SINGLE CELL "I20"
    MsgBox "Cell I20=" & ws.Cells(20, 9).Value
    'DISPLAY VALUES IN ROW OF EXCEL CELLS
    For row = 1 To 5
    MsgBox "Cell(" & row & ",1)=" & ws.Cells(row, 1).Value
    Next
    End Sub


    Private Sub Form_Load()
    'INIT EXCEL OBJECT
    Set mvarExcel = New Excel.Application
    cPath = App.Path + "" 'MyPath [where VB6 app is saved]
    End Sub

    Private Sub Form_Unload(Cancel As Integer)
    'QUIT EXCEL
    mvarExcel.Quit
    Set mvarExcel = Nothing
    End Sub
    [/code]
Sign In or Register to comment.