PDA

View Full Version : Get data from closed workbook



Dibbley247
01-26-2013, 08:15 AM
I have currently running a code which will open workbooks and paste into one sheet.

I have however come up with a problem which I can't quite code right!

I want to open up a file which is a known name, then a sheet which I know PART of the name PLUS what ever is in A1, and I know the Range.

So the Sheet im taking from is called: 3 Days 25.1.13 so I know the start of this sheet is called 3 Days, but the DATE will be in Cell A1
so effectively I want Sheets ("3 days & A1 value")

Here is the code which i'm trying to change:
Sub Staff()

Sheets("3 days week 1").Range("H5") = GetData("\\calfp03\Lynher (file://\\calfp03\Lynher) Bakery\3 days\", "staffing trial.xls", Sheets("Summary") & Range("A1").Value, "H5")
End Sub

Private Function GetData(Path, File, Sheet, Address)
Dim Data$
Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & Range(Address).Range("A1").Address(, , x1R1C1)
GetData = ExecuteExcel4Macro(Data)
End Function


What am i doing wrong?

Dibbley247
01-26-2013, 08:25 AM
Sorry the Sheets in the code should be 3 Days not Summary pastd wrong line on this

GTO
01-26-2013, 02:05 PM
A total stab and not tested whatsoever, but I think you want to concatenate the part you know with the cell's value. In using Sheets(x) with the ampersand, I believe you are tring to "combine" an object with a string, which would likely fall over...

Maybe:

Sub Staff()

ThisWorkbook.Worksheets("3 days week 1").Range("H5") _
= GetData(Path:="\\calfp03\Lynher (file://\\calfp03\Lynher) Bakery\3 days\", _
File:="staffing trial.xls", _
Sheet:="3 days" & Chr(32) & ThisWorkbook.Worksheets("Summary").Range("A1").Value, _
Address:="H5")
End Sub

Hope that helps,

Mark

Dibbley247
01-26-2013, 02:17 PM
When it runs, I get a window open for me to select file path, and then it puts REF in place

I've changed it for home use as the file path is on a a network in work
Sub Staff()

ThisWorkbook.Worksheets("Data").Range("C5") _
= GetData(Path:="C:\Users\Duane\Documents\Work", _
File:="book1.xls", _
Sheet:="3 days" & Chr(32) & ThisWorkbook.Worksheets("Data").Range("A1").Value, _
Address:="C5")
End Sub

GTO
01-26-2013, 07:45 PM
When it runs, I get a window open for me to select file path, and then it puts REF in place

Greetings Dibbley,

I do not have time to make what I would consider an unlikely to be accurate replication; and I admit, I am mystified as to any dialog box popping up. Would you be so kind as to zip two workbooks and attach? Of course the first one should use your current code as to GetData(), in retriving a cell's value from the second workbook.

Thank you so much,

Mark