How can I read information from a specific cell in a closed workbook then paste it in a cell in my active worksheet using VBA in Microsoft Excel?

Question

I was wondering if anyone knew how one can reference a cell from a closed workbook using VBA.

I know how to reference a range of cells using ADO and SQL but I don't know how to create a SQL query for a specific cell.

While browsing the internet i came across some code that uses "ExecuteExcel4Macro" but I am unable to find any real documentation for this function/command. In fact the documentation on the MSDN website regarding this command is rubbish and vague and quit frankly not helpful at all.

Anyway I digress; Ideally, I would like to call on a cell from an external workbook without having to open said workbook. The code I am trying to get to work is as follows:

    Sub update_overview() 

Dim wbPath As String 
Dim wbName As String 
Dim wsName As String 
Dim cellRef As String 
Dim data As Variant 

wbPath = "C:\examplepath\" 
wbName = "Core (N)i.xls" 
wsName = "Sheet1" 
cellRef = "C5" 

'data = GetData(wbPath, wbName, wsName, cellRef) 

ThisWorkbook.Activate 
Sheets("Overview").Select 
With Selection 
ActiveSheet.Range("C5").Clear 
ActiveSheet.Range("C5").Select 
ActiveCell = GetData(wbPath, wbName, wsName, cellRef) 
End With 


End Sub 


Private Function GetData(ByVal wbPath As String, _ 
wbName As String, wsName As String, cellRef As String) As Variant 

Dim arg As String 

GetData = "" 
arg = "'" & wbPath & "[" & wbName & "]" & _ 
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1) 

GetData = ExecuteExcel4Macro(arg) 
End Function 

When i run the macro the only thing it returns is #REF

I have also tried:

Sub Sample() 
Dim wbPath As String, wbName As String 
Dim wsName As String, cellRef As String 
Dim Ret As String 

'wbPath = "C:\Documents and Settings\Siddharth Rout\Desktop\" 
wbPath = "C:\Users\my.name\Desktop\" 

wbName = "QOS DGL stuff.xls" 
wsName = "ACL" 
cellRef = "C3" 

Ret = "'" & wbPath & "[" & wbName & "]" & _ 
wsName & "'!" & Range(cellRef).Address(True, True, -4150) 

MsgBox ExecuteExcel4Macro(Ret) 
End Sub 

When the code reaches MsgBox I get a type missmatch error. If i get rid of the msgbox command and try to continue to paste to the cell with

ThisWorkbook.Activate 
Sheets("Overview").Select 
With Selection 
ActiveSheet.Range("C5").Clear 
ActiveSheet.Range("C5").Select 
ActiveCell = ExecuteExcel4Macro(Ret) 

I still get the #REF! error

Can anyone tell me: 1) is this the best technique to be using? 2) What is wrong with my code? and, 3) Is there a better way to reference a single cell from an external workbook using ADO or DOA or a technique i am unaware of.

Also does anyone know of any extensive documentation on how to use the ExecuteExcel4Macro function.

Please help; Thanks

FYI I am on excel 2003

Answer 1

You could try something like this:

arg = "='" & wbPath & "[" & wbName & "]" & wsName & "'!" & cellRef

Sub Test()

Dim wbName As String
Dim wbPath As String
Dim wsName As String
Dim cellRef As String
Dim calcState As Long

calcState = Application.Calculation

wbPath = "C:\users\david_zemens\"
wbName = "a report.xlsx"
wsName = "Sheet1"
cellRef = Range("B2").Address

Dim arg As String
arg = "='" & wbPath & "[" & wbName & "]" & wsName & "'!" & cellRef 'Range(cellRef).Address(True, True, xlR1C1)

Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
ActiveCell.Value = arg
ActiveCell.Value = ActiveCell.Value 'essentially a paste/values over the formula.
Application.DisplayAlerts = True
Application.Calculation = calcState



End Sub