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

 

[Odkundes]

Hello and sorry my english is not the best

i am trying to pull data from a closed worksheet


this is my macro
macro should search for this workbook.name in a range of closed workbook and give me back other cell value with index and match


meno = (Mid(ThisWorkbook.Name, 1, Len(ThisWorkbook.Name) - 5))
Range("f1") = meno

With Worksheets("strana").Range("A3")'<<2

.Formula = "=Index('C:\Users\xxxn.xxx\Desktop\[kreten.xlsx]bukva'!$C$1:$C$11,Match(f1,'C:\Users\xxx.xxx\Desktop\[kreten.xlsx]bukva'!$B$1:$B$11,0))"'<<3
.Value = .Value

End With

End Sub

IN this macro wokrs all well BUT =)
i want 2 things

1.)
I dont vant do use Range("f1") = meno
if i delete it and write the formula this way:
.Formula = "=Index('C:\Users\xxxn.xxx\Desktop\[kreten.xlsx]bukva'!$C$1:$C$11,Match(meno,'C:\Users\xxx.xxx\Desktop\[kreten.xlsx]bukva'!$B$1:$B$11,0))"
i get here Worksheets("strana").Range("A3") a #name (shoult get DATE like 02.02.2017)

2.)
I dont vant do use Worksheet("strana").Range("A3")
what i want is that the final value should be a Label.Caption in userform on activate

please help me to find a solution
thanks

 

[AlphaFrog]

1.)

.Formula = "=Index('C:\Users\xxxn.xxx\Desktop\[kreten.xlsx]bukva'!$C$1:$C$11,Match([COLOR="#FF0000"]" & meno & "[/COLOR],'C:\Users\xxx.xxx\Desktop\[kreten.xlsx]bukva'!$B$1:$B$11,0))"

2.)
I don't know

 

[Odkundes]

1.)

.Formula = "=Index('C:\Users\xxxn.xxx\Desktop\[kreten.xlsx]bukva'!$C$1:$C$11,Match([COLOR=#ff0000]" & meno & "[/COLOR],'C:\Users\xxx.xxx\Desktop\[kreten.xlsx]bukva'!$B$1:$B$11,0))"

2.)
I don't know

it doesnt work

after & meno & it shows an error awaiting end of instruction (sorry i have excel in German version)
but thank you

i had to add my own function

right now the whole macro in userform looks like this:

Private Sub UserForm_Initialize()
End Sub
____________________________________________
Function Meno()
Meno = (Mid(ThisWorkbook.Name, 1, Len(ThisWorkbook.Name) - 5))
End Function
____________________________________________
Function totok()
totok = "=Index('C:\Users\milan.halaj\Desktop\[kreten.xlsx]bukva'!$C$1:$C$11,Match(Meno(),'C:\Users\milan.halaj\Desktop\[kreten.xlsx]bukva'!$B$1:$B$11,0))"
End Function
____________________________________________

Private Sub Userform_Activate()
Label1.Caption = totok()
End Sub
____________________________________________

All is working but i get in Label1 the formula from "totok" but not the Value

 

[Odkundes]

it looks like that Labels cant calculate and give back the value as Caption.Do i rally have to use some cell to do the calculation?

 

[sheter]

Dmt32,
This code is beautiful! It was so clean & it runs lightning fast.

QUESTION: Is it possible to pull data from all excel files in a folder? (maybe in separate sheets?)

 

[massimobaran]

Hi,

This is exactly what I want to do in my excel sheet but I would like to take it one step further. I have a macro that allows me to add a file name and hyperlink to the sheet to a selected cell so we will call these cells "column A." Then I would like to populate the sheet with specific cells from separate excel sheets. To elaborate on this, I would like "column B" to be filled with cell "C1" from the sheet named in the same row. Then I would like "column C" to be filled with cell "C2" from the sheet in the same row as well.

Thanks,
Massimo

 

[luckyduck03]

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

Is there a way modify this to past mydata into the next available cell row instead of the specific range B2:F12?