Very big workbook as source

Hi,

i have one main workbook and second source is very big (it will have thousends of rows).

From main workbook i have formulas which are reading from source workbook using the same path as main workbook is.
On open workbook event i am updating links to source workbook but sometimes it can cause errors.
I can do error handlers but still somehow it can be not perfect.

What is the best solution to have one source data and main workbook?
You are putting all in one workbook or using buttons to read specific source workbook and open it.

Your best option is...?

Best,
Jacek

If the main book is too big to open, use ADO(but depends on data layouts/types) to read data to 2nd workbook is one idea.

Hi!

If the main book is too big to open, use ADO(but depends on data layouts/types) to read data to 2nd workbook is one idea.

You meant the source workbook is too big?
Yes it is big, and will be opening for example 10 seconds.

With Ado you can use source worbkook as recordset yes?
But how to refer to recordset from formulas ?

Now i have (from memory, there can be errros but i want to show you syntax) vlookup(B30, [C:/downloads/workbooks/Source workbook!Sheet1!A1:B5],2,0)

What do you think to approch to force user to point to source workbook to have all formulas caclulated properly?
Button --> choosing source workbook --> opening it --> formulas are updated automatically.

Best,
Jacek

You can use Join in sql to work like LookUp formula, but not sure if it is faster or not.

you mean assing to specific cell like .cells("B2").value = sqlStatement?

Hmm...

Jacek

Only a few cells?
You can still use it with the "Where" condition in sql.

from 10 to about 30 cells in one worksheet and i have about 6 worksheets, i do not know it would be good idea.

I think opening source workbook but from user side via button - seems theb est option for me now.

Best,
Jacek

It is up to you of course.

I just gave you an alternative idea.

thank you jindon,

maybe anybody else?

Best,
Jacek

By the way why do you need to open the source workbook?

If your formula contains full path, you don't need to open...

yes but this will required write code to change full path for specific user opening workbook.

And i have to remember about Mac users...

And simple and more safety way will be to point to specific source and do not this with macro i think on workbook opening.