• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

UDF > PULL function

1. i have a mother data workbook with the field which has the unique key ids.

2. A child data workbook is born as soon as new data entered in the mother workbook with a name same as the unique key in master data workbook.

3. i want to extract data from each of the child data workbooks into the mother data workbook - automatically, without opening them (while the child workbooks are closed). At the same time i do not want to get into the jugglery of linking each workbook manually. INDIRECT fuction does not work as it works only while the child workbooks are open. Variable cell reference in anther workbook does not work.

I have 2 options

A. A user defined function. I have read PULL() can work form me but i have not been able to trak any code which works for me.

B. A Macro


I woulr prefer "A" above
 
Hi Gulshan,


Below are two code snippets that you can use to build the type of formula you are looking for.


Sub Pull(DirNam, FilNam, SheetNam, CellAdd, TarCell)


'This places the formula in the cell of your choice


ConRange = SheetNam &; "'!" &; CellAdd

ConNam = DirNam &; "[ &; FilNam & ]"

Range(TarCell).Formula = "='" &; ConNam & ConRange


End Sub

Sub test()


'This gets the Directory Name, File Name, Sheet Name, Source cell and Target Cell from the Master file.


DirNam = Range("C3") 'E:AnalystKPIs & Reporting2010Faster line detailed reportingTransportCosts

FilNam = Range("D3") 'C2_C3_Nov2010.xlsm

SheetNam = Range("E3") 'ByLocation

CellAdd = Range("F3") 'C5

TarCell = Range("G3") 'K36


Call Pull(DirNam, FilNam, SheetNam, CellAdd, TarCell)


End Sub


Try these out and then change them to suit, you will note that I have not specified the type of variable (very bad practice)
 
Dear kchiba,

i am getting a complete error - syntex error at


ConRange = SheetNam &; "'!" &; CellAdd

ConNam = DirNam &; "[ &; FilNam & ]"

Range(TarCell).Formula = "='" &; ConNam & ConRange
 
Hi Gulshan,


There is a gremlin in the system, there should be no ; after the &. Below is the corrected version


Sub Pull(DirNam, FilNam, SheetNam, CellAdd, TarCell)


'This places the formula in the cell of your choice


ConRange = SheetNam & "'!" & CellAdd

ConNam = DirNam & "[ & FilNam & ]"

Range(TarCell).Formula = "='" & ConNam & ConRange


End Sub

Sub test()
 
Hi kchiba

the macro seems to work with fixed references.


what the system desires is that the macro should lookp through a column in the mother workbook for the file name (variable file names) of the daughter workbooks, copy contents of a range of cells and paste it against the file address (row of the file number) in the specified columns
 
That is correct, these are just snippets of code for showing how you can Pull data from closed files. It should be included within a loop that will read all the rows etc


Let me have your email and I will send you a file that I use to do what you require.


kanti
 
You must log in or register to reply here.
Back
Top