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

 

[couveman]

Thank you for your quick reply. I created a new Workbook with data in a couple of random cells, adjusted the code to the new workbook, sheet and cells and it worked! What I have discovered is the original workbook "Mar 2015" is password protected (forgot about that). Anyway, how do I address the password situation?

 

[couveman]

AlphaFrog, thank you for your quick reply! And ignore my previous post. A password had nothing to do with my problem.

I did get it to run! I think my problem was in the strPath which included "C:\Users\Ron's-PC\...... ". I placed my workbooks at "C:\MMS\" and it worked. I'm wondering about the apostrophe.

 

[AlphaFrog]

AlphaFrog, thank you for your quick reply! And ignore my previous post. A password had nothing to do with my problem.

I did get it to run! I think my problem was in the strPath which included "C:\Users\Ron's-PC\...... ". I placed my workbooks at "C:\MMS\" and it worked. I'm wondering about the apostrophe.

The apostrophe (aka: single quote) is an issue. Replace one literal apostrophe with two apostrophes.

strPath = Replace(ActiveWorkbook.path & "\", "[COLOR=#ff0000]'[/COLOR]","[COLOR=#ff0000]'[/COLOR][COLOR=#800080]'[/COLOR]")

 

[mrlemmer11]

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)

Alphafrog, Here is what I am doing currently:

Dim sourcedoc
Dim oExcel As Excel.Application
Dim oWB As Workbook
Set oExcel = New Excel.Application
Application.ScreenUpdating = False
Set sourcedoc = oExcel.Workbooks.Open(FileName:="blahblahblha"\CSlistmrl.xlsx")
arLOB = Range("L2:L9")
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
Set oExcel = Nothing
Set oWB = Nothing

So am I to understand that I could do the same thing without ever actually opening the file and still obtain the data to use in vba? I believe I already understand the benefits of this, but could you cover any pros and cons of doing it your way vs. mine?

Thanks a bunch!

 

[Yuriy B]

Hi everyone,

The code below worked from me, but i need to add formulas so that only specified data gets pulled.
Where can I include formulas to replace Sheet1'!$B$2:$F$12

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

 

[beyounge]

Hello,
I wanted to create a VBA that will pull the values from a closed workbook into the open workbook and from certain ranges that are non-contiguous. The worksheets are identical in layout. For example, I wanted to copy whatever values were in cells D10, D16:D26, D31:D41 (and the same row/cells in columns J, P, and V) from SHEETx in FILEx, and have the values pulled into the exact same cells/rows into SHEETx in FILEy. I would need the user to be prompted to find FILEx when the macro button is pushed so it knows which file the data is coming from, and it could be located in a different directory. Any experts out there?

 

[MARK858]

Post #16 is a duplicate post of the link below...

http://www.mrexcel.com/forum/excel-...on-contiguous-cells-one-workbook-another.html

 

[mbhatt]

I am getting a #value! error while applying this code.

 

[Jadde]

another version:

Hi, how do I use the below code with the addition of lrow (as the last row may change) but I know the columns with data that need to be pulled are from column A to column S - please see my failed attempt in red:


Sub GetDataFromClosedBook()
Dim mydata As String
'data location & range to copy
mydata = "='C:\[Newbook.xls]Sheet1'!$A:$S"

lrow = "='C:\[Newbook.xls]Sheet1'!$A$1:$S$1".End(xldown).Row

For I = 2 to lrow

'link to worksheet
With ThisWorkbook.Worksheets(1).Range(("A" & I)"S" & I))
.Formula = mydata
'convert formula to text
.Value = .Value

End With

Next I

End Sub

Also will this macro automatically update when the data in mydata is changed and saved?

Many Thanks,

Jadde

 

[Antoniorp]

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

This works perfectly.

But what if I want to import all the data, for instance, 10000 cells? Or better yet, what if I want to import all the data in the cells but I don't know exactly how many rows and columns there are?

How could this be done?

Thank you