VBA code for pulling data from a closed workbook w/o opening it

[nadaraza]

Hi,

Looking to pull the data in one cell from a closed workbook say A1, without actually opening the workbook or prompting excel to open and close it. Ideally if it can be live, change as it changes in the (closed) workbook without it needing to be opened ...otherwise just pull in the information from that cell and I'll just add an update button so it refreshes on command.

Thanks,
Nada

 

[AlphaFrog]

Try something like this...

    strPath = "C:\Test\"
    strFile = "Book1.xls"
    strSheet = "Sheet1"
    strRng = Range("A1").Address(1, 1, xlR1C1)
    
    strRef = "'" & strPath & "[" & strFile & "]" & strSheet & "'!" & strRng
    
    Result = ExecuteExcel4Macro(strRef)

 

[nadaraza]

Try something like this...

    strPath = "C:\Test\"
    strFile = "Book1.xls"
    strSheet = "Sheet1"
    strRng = Range("A1").Address(1, 1, xlR1C1)
    
    strRef = "'" & strPath & "[" & strFile & "]" & strSheet & "'!" & strRng
    
    Result = ExecuteExcel4Macro(strRef)

Wow Thanks for the quick response. So that pulls the info from Book1...is there a part that I have to put in the wookbook i'm pulling it into. How does it know where to put the info in the active workbook?

 

[AlphaFrog]

You wouldn't need VBA to put the value in a cell. You can put the link directly in the cell as a formula. I only provided the VBA solution because that's what you had asked for.

In the destination cell put something like this.
='C:\Test\[Book1.xls]Sheet1'!A1

 

[nadaraza]

You wouldn't need VBA to put the value in a cell. You can put the link directly in the cell as a formula. I only provided the VBA solution because that's what you had asked for.

In the destination cell put something like this.
='C:\Test\[Book1.xls]Sheet1'!A1

True.. i had that initially but the problem is that number does not update unless the spreadsheet is opened....so i was hoping to link it in a way where it pull the information from that sheet and i can refresh it without opening the actual sheet and then closing it. Reason is these spreadsheets are huge so it kills the computer speed and things freeze up.

Thanks anyway though!

 

[AlphaFrog]

The Links can update from the closed workbook. Perhaps this will help...

[h=1]Control when external references (links) are updated[/h]

 

[dmt32]

Hi,

Looking to pull the data in one cell from a closed workbook say A1, without actually opening the workbook or prompting excel to open and close it. Ideally if it can be live, change as it changes in the (closed) workbook without it needing to be opened ...otherwise just pull in the information from that cell and I'll just add an update button so it refreshes on command.

Thanks,
Nada

another version:

Sub GetDataFromClosedBook()
Dim mydata As String
'data location & range to copy
mydata = "='C:\[Newbook.xls]Sheet1'!$B$2:$F$12" '<< change as required

'link to worksheet
With ThisWorkbook.Worksheets(1).Range("B2:F12") '<< change as required
.Formula = mydata
'convert formula to text
.Value = .Value

End With
End Sub

 

[nadaraza]

Awesome. That works! Thanks Guys.

 

[couveman]

Sub readdata()


Dim strPath, strFile, strSheet, strRng, strRef, Result As String

strPath = ActiveWorkbook.path & "\"
strFile = "Mar 2015.xlsm"
strSheet = "MMS-BeavEx"
strRng = Range("DW127").Address(1, 1, xlR1C1)

strRef = "'" & strPath & "[" & strFile & "]" & strSheet & "'!" & strRng

Result = ExecuteExcel4Macro(strRef)


End Sub

I get a Runtime Error 1004 at ** Result = ExecuteExcel4Macro(strRef) ** I am in Excel 2013, maybe ExecuteExcel4Macro isn't applicable in 2013 version!??!
Is there another way I could pull data from a closed Workbook?

 

[AlphaFrog]

ExecuteExcel4Macro is compatible with 2013
https://msdn.microsoft.com/en-us/library/office/ff193589.aspx

Can you double-check the sheet name?

Alternative:
Pull Function