We have about 5000 recipes in separate spreadsheets and i have been asked to
compile a list of the recipe names into a new spreadsheet.
I could open every individual recipe and copy/paste the filename into the
new spreadsheet but that will take forever.
Is there a way to tell the spreadsheet to look at the xls files and extract
the info required (the recipe name is always in the the same cell ref),
without openeing the files?
I have been informed from another group that VBA would be required and
that you can't get information out of a spreadsheet without opening it, but
that
opening can be done with screen updating off, so the user doesn't see it
happening.

Any help would be gratefully recieved
Thanks

Hi Nelly,

well... if they are within the same workbook or at least
within a few workbooks, the solution would be very simple.
but if they are all in another workbook which have
completely different names, this would be a little fuzzy...

However, I would use it that way:


Is there a way to tell the spreadsheet to look at the xls
files and extract
the info required (the recipe name is always in the the
same cell ref),
without openeing the files?

Not without opening, but with screen.updating off it would
work (as you write below)

i = 1
For Each cell In Range("targetrange")

cell.Value = Workbooks("sourcebook.xls").Sheets(i).Range
("sourcecell").Value
i = i + 1

Next cell

targetrange is the range where you want to write the
recipe names.
sourcecell is the cell within all the sheets where the
name is in.

If you have more than one workbook, you should use that
loop in a modified way..

Best

Markus



I have been informed from another group that VBA would be
required and
that you can't get information out of a spreadsheet
without opening it, but
that
opening can be done with screen updating off, so the user
doesn't see it
happening.

Any help would be gratefully recieved
Thanks

.

Hi Markus

First off, thanks for a swift reply.
Every single recipe is in it's own spreadsheet, is this going to make a big
difference to solving the problem?

Regards
Nelly

"Markus Scheible" wrote:

Hi Nelly,

well... if they are within the same workbook or at least
within a few workbooks, the solution would be very simple.
but if they are all in another workbook which have
completely different names, this would be a little fuzzy...

However, I would use it that way:


Is there a way to tell the spreadsheet to look at the xls
files and extract
the info required (the recipe name is always in the the
same cell ref),
without openeing the files?

Not without opening, but with screen.updating off it would
work (as you write below)

i = 1
For Each cell In Range("targetrange")

cell.Value = Workbooks("sourcebook.xls").Sheets(i).Range
("sourcecell").Value
i = i + 1

Next cell

targetrange is the range where you want to write the
recipe names.
sourcecell is the cell within all the sheets where the
name is in.

If you have more than one workbook, you should use that
loop in a modified way..

Best

Markus



I have been informed from another group that VBA would be
required and
that you can't get information out of a spreadsheet
without opening it, but
that
opening can be done with screen updating off, so the user
doesn't see it
happening.

Any help would be gratefully recieved
Thanks

.

Hi Nelly,


Every single recipe is in it's own spreadsheet, is this
going to make a big
difference to solving the problem?

Depends... the problem is that you cannot use a For Each
loop with workbooks like

For Each Workbook In Dir(pathname:="c:\whatever")...

so you should try to automatically open all workbooks
within the folder and then run the routine with every open
workbook... but for 5000 workbooks this may be hard...

I'm sorry to must tell you that I don't really know how to
go on... but I keep thinking about it.

Best

Markus


Regards
Nelly

"Markus Scheible" wrote:

Hi Nelly,

well... if they are within the same workbook or at
least
within a few workbooks, the solution would be very
simple.
but if they are all in another workbook which have
completely different names, this would be a little
fuzzy...

However, I would use it that way:


Is there a way to tell the spreadsheet to look at the
xls
files and extract
the info required (the recipe name is always in the
the
same cell ref),
without openeing the files?

Not without opening, but with screen.updating off it
would
work (as you write below)

i = 1
For Each cell In Range("targetrange")

cell.Value = Workbooks("sourcebook.xls").Sheets(i).Range
("sourcecell").Value
i = i + 1

Next cell

targetrange is the range where you want to write the
recipe names.
sourcecell is the cell within all the sheets where the
name is in.

If you have more than one workbook, you should use that
loop in a modified way..

Best

Markus



I have been informed from another group that VBA would
be
required and
that you can't get information out of a spreadsheet
without opening it, but
that
opening can be done with screen updating off, so the
user
doesn't see it
happening.

Any help would be gratefully recieved
Thanks

.


.

So are all the spreadsheets in 1 directory.

If not, how can it be determined which spreadsheets have recipes and where
to look for them.

This sounds like a one time adventure. Why do you care whether users can
see them being opened? Aren't you the only user?

--
Regards,
Tom Ogilvy

"Nelly" wrote in message
...
Hi Markus

First off, thanks for a swift reply.
Every single recipe is in it's own spreadsheet, is this going to make a
big
difference to solving the problem?

Regards
Nelly

"Markus Scheible" wrote:

Hi Nelly,

well... if they are within the same workbook or at least
within a few workbooks, the solution would be very simple.
but if they are all in another workbook which have
completely different names, this would be a little fuzzy...

However, I would use it that way:


Is there a way to tell the spreadsheet to look at the xls
files and extract
the info required (the recipe name is always in the the
same cell ref),
without openeing the files?

Not without opening, but with screen.updating off it would
work (as you write below)

i = 1
For Each cell In Range("targetrange")

cell.Value = Workbooks("sourcebook.xls").Sheets(i).Range
("sourcecell").Value
i = i + 1

Next cell

targetrange is the range where you want to write the
recipe names.
sourcecell is the cell within all the sheets where the
name is in.

If you have more than one workbook, you should use that
loop in a modified way..

Best

Markus



I have been informed from another group that VBA would be
required and
that you can't get information out of a spreadsheet
without opening it, but
that
opening can be done with screen updating off, so the user
doesn't see it
happening.

Any help would be gratefully recieved
Thanks

.

Hi Nelly

It isn't all that difficult to do this and the advice you received is
largely correct but if the files are scattered around different folders thats
more problematic, anyway other than opening them all together you can use the
file system to search for XLS files and open each one in turn checking its
name.

A few questions
A) Are they all in the same folder ?
B) Are they named similarly (EG Menu001, Menu002, etc) or in a pattern (EG
Menu*.XLS) ?


"Nelly" wrote:

We have about 5000 recipes in separate spreadsheets and i have been asked to
compile a list of the recipe names into a new spreadsheet.
I could open every individual recipe and copy/paste the filename into the
new spreadsheet but that will take forever.
Is there a way to tell the spreadsheet to look at the xls files and extract
the info required (the recipe name is always in the the same cell ref),
without openeing the files?
I have been informed from another group that VBA would be required and
that you can't get information out of a spreadsheet without opening it, but
that
opening can be done with screen updating off, so the user doesn't see it
happening.

Any help would be gratefully recieved
Thanks