Read from another excel

[mr moe]

Hi,
Can someone help, does anyone know how I can read values from an excel file, basically I have an excel sheet that I'm doing some vba work and I need to go to the excel sheet which resides some where, read the table and apply the values in my current excel file. I hope that I didn't confuse you guys. Thanks a lot.

 

[JohnD]

Correct me if im wrong, but it seems like a simple formula:

=SUM('enter your worksheet here'!enter cell reference here)

Of course this is if your worksheets are located in the same workbook.

 

[mr moe]

I'm trying to build an array that pastes a value after it reads the table from another excel file. For example, I have Excel File "A", I want to read from File "B" this is similar to vlookup that goes to another excel file that is closed. But I want to do this in vba, all I'm looking for is the piece of code on how to look inside an excel file that is closed!!!!!!!!! Thanks.

 

[reclusivemonkey]

Have you thought about using the macro recorder to help you get started?

 

[Bat17]

In a normal sense VBA cannot look into closed files.
However try this article. look about 2/3 of the way down the page.
I have not tried this so cannot vouch for it though.

http://j-walk.com/ss/excel/eee/eee009.txt

HTH

Peter

 

[mr moe]

Thanks, I tried it but I'm still doing something wrong because I"m getting an error msg, here is my code.

FilePath = "C:\Test"
FileName = "table.xls" '<==this could change in a loop
SheetName = "Sheet1"
MsgBox FilePath & "\" & "[" & FileName & "]" & _
SheetName.Cells(3, 3).Value

All I want is to get for instance the value of row 3 and column 3

 

[Bat17]

you missed out the reference to 'ExecuteExcel4Macro' try

Private Function GetValue(path, file, sheet, range_ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
        If Dir(path & file) = "" Then
        GetValue = "File Not Found"
            Exit Function
    End If
'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(range_ref).Range("A1").Address(, , xlR1C1)
      Debug.Print arg
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
    End Function
    
    Sub test()
    Dim x
    
    x = GetValue("C:\", "myFile.xls", "sheet8", "A5")
    End Sub

HTH

Peter