Macro - Read a cell value from external file.xls

[doron_angel]

Sorry if this is the wrong forum & if has been asked before.

Is there a way to read a specific Cell value from a specific sheet (let's say "total") which is in another woorkbook (let's say file.xls) using VBA. Is it possible not to have this file opened and displayed?

Thanks in advance,

Doron.

[Mark O'Brien]

This is indeed the correct Forum.

For my example, I am trying to read the contents of Cell "A1" on "Sheet1" in "Book1.xls" that is saved in the "C:Temp" directory.

  Dim XL As Excel.Application

  Dim WBK As Excel.Workbook

  

  Set XL = CreateObject("Excel.Application")

  Set WBK = XL.Workbooks.Open("C:TempBook1.xls")

  MsgBox WBK.Sheets("Sheet1").Range("A1").Value

  

  WBK.Close

  Set XL = Nothing

  

HTH

_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-02-25 15:42 ]

[ This Message was edited by: Mark O'Brien on 2002-02-25 15:55 ]

[gplhl]

On 2002-02-25 15:29, doron_angel wrote:
Sorry if this is the wrong forum & if has been asked before.

Is there a way to read a specific Cell value from a specific sheet (let's say "total") which is in another woorkbook (let's say file.xls) using VBA. Is it possible not to have this file opened and displayed?

Thanks in advance,

Doron.

You can read the cell value no problem by using something like:

Sub readCellValue()
'Assigns the value to cellValue
cellValue = Range("[file.xls]total!A1").Value

End Sub

I'm not so sure about not having the book open, maybe someone else knows more about that?

If the book the value is in isn't too big then open it using vba, read the value and close it again. You can speed things up by turning off screen updating whilst running the script.

Sub readCellValue2()

Application.ScreenUpdating = False
Workbooks.Open ("C:file1.xls")
cellValue = Range("[file1.xls]total!A1").Value
ActiveWorkbook.Close
Application.ScreenUpdating = True

End Sub

Regards,

Gary Hewitt-Long

[]-[ /-\ \/\/ /-\ | | /-\]

Many ways to extract data from closed workbook. I chose to use the following function written by David Hager (I'm not smart enough to come with this kind of stuff!):

_____________________________________________

Function CWRIA(fPath As String, fName As String, sName As String, rng As String)
Dim sRow As Integer
Dim sColumn As Integer
Dim sRows As Integer
Dim sColumns As Integer
Dim vrow As Integer
Dim vcol As Integer
Dim fpStr As String
Dim cArr()
On Error GoTo NoArr
If Right(fPath, 1) <> "" Then fPath = fPath & ""
If Dir(fPath & fName) = "" Then
CWA = CVErr(xlErrValue)
Exit Function
End If
sRow = Range(rng).Row
sColumn = Range(rng).Column
sRows = Range(rng).Rows.Count
sColumns = Range(rng).Columns.Count
ReDim cArr(sRows, sColumns)
For vrow = 1 To sRows
For vcol = 1 To sColumns
fpStr = "'" & fPath & "[" & fName & "]" & sName & "'!" & _
"r" & sRow + vrow - 1 & "c" & sColumn + vcol - 1
cArr(vrow, vcol) = ExecuteExcel4Macro(fpStr)
Next
Next
CWRIA = cArr
Exit Function
NoArr:
CWRIA = CVErr(xlErrValue)
End Function


Sub CWRIR(fPath As String, fName As String, sName As String, rng As String, destRngUpperLeftCell As String )
Dim sRow As Integer
Dim sColumn As Integer
Dim sRows As Integer
Dim sColumns As Integer
Dim vrow As Integer
Dim vcol As Integer
Dim fpStr As String
Dim cArr()
On Error GoTo NoArr
If Right(fPath, 1) <> "" Then fPath = fPath & ""
If Dir(fPath & fName) = "" Then
CWA = CVErr(xlErrValue)
Exit Function
End If
sRow = Range(rng).Row
sColumn = Range(rng).Column
sRows = Range(rng).Rows.Count
sColumns = Range(rng).Columns.Count
ReDim cArr(sRows, sColumns)
Set destRange = ActiveSheet.Range(destRngUpperLeftCell)
For vrow = 1 To sRows
For vcol = 1 To sColumns
fpStr = "'" & fPath & "[" & fName & "]" & sName & "'!" & _
"r" & sRow + vrow - 1 & "c" & sColumn + vcol - 1
destRange.Offset(vrow - 1, vcol - 1) = ExecuteExcel4Macro(fpStr)
Next
Next
NoArr:
End Sub

_____________________________________________

Cut and paste the above code into a module and then write your macro similar to below (in your case):

Sub ExtractDataSample()
Dim Filepath$, Filename$, Sheetname$, Src$, Dest$
'Source Information
Filepath = "C:pathwhereeveryourfileis"
Filename = "file.xls"
Sheetname = "total"
'Extract data from source range:
Src = "G5"
'Destination cell
Dest = "A1"
'insert line here if you need to activate your workbook/sheet
CWRIR Filepath, Filename, Sheetname, Src, Dest
End Sub


Change source info (path, filename, sheetname, & source cell range) to meet your needs. You can specify a single cell or an array as range. Code assumes that the destination cell is in the activeworkbook--which is why you only specify the cell--so you may have to add a line of code specifying which workbook worksheet to activate if it's not the activesheet at runtime. Hope it helps.

Aloha!

[brettvba]

Or you could just create a link and enable automatic link updates under edit links







Realize

[Ivan F Moala]

On 2002-02-25 15:29, doron_angel wrote:
Sorry if this is the wrong forum & if has been asked before.

Is there a way to read a specific Cell value from a specific sheet (let's say "total") which is in another woorkbook (let's say file.xls) using VBA. Is it possible not to have this file opened and displayed?

Thanks in advance,

Doron.

Hi Doran

Here is yet another way

Sub test()
MsgBox GetValue("C:ExcelFilesUseful", "###.xls", "Sheet1", "A1")
End Sub


Private Function GetValue(Path, File, Sheet, Ref)

Dim sArg As String

If Right(Path, 1) <> "" Then Path = Path & ""

If Dir(Path & File) = "" Then
GetValue = "File Not Found"
Exit Function
End If

sArg = "'" & Path & "[" & File & "]" & Sheet & "'!"

MsgBox sArg

sArg = sArg & Range(Ref).Range("A1").Address(, , xlR1C1)

MsgBox sArg

GetValue = ExecuteExcel4Macro(sArg)

End Function

Ivan

[doron_angel]

Mark,

Thanks a million. It worked. However, do you happen to know how can I do a "quiet" workbook close (answer NO) to save changes?

Doron.

On 2002-02-25 15:41, Mark O'Brien wrote:
This is indeed the correct Forum.

For my example, I am trying to read the contents of Cell "A1" on "Sheet1" in "Book1.xls" that is saved in the "C:Temp" directory.

  Dim XL As Excel.Application

  Dim WBK As Excel.Workbook

  

  Set XL = CreateObject("Excel.Application")

  Set WBK = XL.Workbooks.Open("C:TempBook1.xls")

  MsgBox WBK.Sheets("Sheet1").Range("A1").Value

  

  WBK.Close

  Set XL = Nothing

  

HTH

_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-02-25 15:42 ]

[ This Message was edited by: Mark O'Brien on 2002-02-25 15:55 ]

[Russell Hauf]

Just use

WBK.Close False

To not save changes.

-rh

[doron_angel]

Thanks, it worked!

On 2002-02-26 12:57, Russell Hauf wrote:
Just use

WBK.Close False

To not save changes.

-rh