word <-> Excel automation - Too many named ranges?

[Guest]

Hi! 2 weeks effort down the drain unless you have an answer...

[Office XP SP3 running under XP Pro, SP2, 1GB mem)

I have written VBA in MS Word to generate a concordance for a book <=100k
words. Because of bookmark limitations in word (max ~16387) I have written
automation code to pipe data to an Excel spreadsheet, creating named ranges
on the fly - the name containing the range start and end for the target in
the word document. When opened, the workbook recovers the source doc name
from its own and creates a new automation session to goto words/phrases in
the word document based on the range names (and hence range start/end values)

Everything is fine for small documents, but having just processed the book
(24 hours run-time!) whilst no errors were encountered* (* see below), when
attempting to open the resulting 23MB Excel workbook, Excel fails to open the
file, going through a process of repair before deciding it's all hopeless
(for an intermediate snapshot of ~6MB the log file says eventually "Damage to
the file was so extensive that repairs were not possible. Excel attempted to
recover your formulas and values, but some data may have been lost or
corrupted." - yes, there's data but the workbook is functionally completely
messed up; for the full file I don't even get that!).

Question: specifications say that named ranges are limited only by memory,
but when running the automation xl didn't take more than ~50MB-ish (don't
remember exactly) but much less than the available RAM without even
considering virtual memory. However, given that I have approximately 200k
named ranges, is that the source of the problem? One sheet has the full
65536 rows and a full 256 columns for *some* rows (the others have ~8,000 and
45,000 rows respectively).

Any ideas? Am at my wits end... I tried doing the concordance in Word alone
in the first place, then going via XML output from Word into Excel before
direct automation links... I see no other way ahead!

Best regards

Jules

* there was an error early on when attempting to set cell value and name
range, but invoking xlDoc.save from word and resuming allowed execution to
proceed - MS please note!

 

[Tim Williams]

Is this what you mean by concordance ?
"An alphabetical index of all the words in a text or corpus of texts,
showing every contextual occurrence of a word: a concordance of
Shakespeare's works."

Since you seem to be more or less using Excel just as a database, have you
considered using Access to store the bookmarks ?
I would just insert records with the word, maybe an "autonumber" unique
field, and fields for the range information.

Tim.


--
Tim Williams
Palo Alto, CA


"Resignation of an Excel Guru "

 

[Guest]

Yes, that's what I meant by concordance; I knew someone would suggest Access!
Alas, whilst I can make Excel sit up and beg, Access and I do not get
along...

Resolving this issue would give me the data that I want in a tool I know how
to use... and tell me more about the tool.

But the thought is appreciated.

Jules

Is this what you mean by concordance ?
"An alphabetical index of all the words in a text or corpus of texts,
showing every contextual occurrence of a word: a concordance of
Shakespeare's works."

Since you seem to be more or less using Excel just as a database, have you
considered using Access to store the bookmarks ?
I would just insert records with the word, maybe an "autonumber" unique
field, and fields for the range information.

Tim.


--
Tim Williams
Palo Alto, CA


"Resignation of an Excel Guru "

Hi! 2 weeks effort down the drain unless you have an answer...

[Office XP SP3 running under XP Pro, SP2, 1GB mem)

I have written VBA in MS Word to generate a concordance for a book <=100k
words. Because of bookmark limitations in word (max ~16387) I have written
automation code to pipe data to an Excel spreadsheet, creating named ranges
on the fly - the name containing the range start and end for the target in
the word document. When opened, the workbook recovers the source doc name
from its own and creates a new automation session to goto words/phrases in
the word document based on the range names (and hence range start/end values)

Everything is fine for small documents, but having just processed the book
(24 hours run-time!) whilst no errors were encountered* (* see below), when
attempting to open the resulting 23MB Excel workbook, Excel fails to open the
file, going through a process of repair before deciding it's all hopeless
(for an intermediate snapshot of ~6MB the log file says eventually "Damage to
the file was so extensive that repairs were not possible. Excel attempted to
recover your formulas and values, but some data may have been lost or
corrupted." - yes, there's data but the workbook is functionally completely
messed up; for the full file I don't even get that!).

Question: specifications say that named ranges are limited only by memory,
but when running the automation xl didn't take more than ~50MB-ish (don't
remember exactly) but much less than the available RAM without even
considering virtual memory. However, given that I have approximately 200k
named ranges, is that the source of the problem? One sheet has the full
65536 rows and a full 256 columns for *some* rows (the others have ~8,000 and
45,000 rows respectively).

Any ideas? Am at my wits end... I tried doing the concordance in Word alone
in the first place, then going via XML output from Word into Excel before
direct automation links... I see no other way ahead!

Best regards

Jules

* there was an error early on when attempting to set cell value and name
range, but invoking xlDoc.save from word and resuming allowed execution to
proceed - MS please note!

 

[Tim Williams]

So, if you really want to stick with Excel, is there some way to avoid the
named ranges ? (though I'd suspect the major problem may just be the sheer
bulk of data).

Why do you need a named range? Can you rework your data to substitute a
column entry instead (and maybe use Find to locate the required ranges) ?

Tim

--
Tim Williams
Palo Alto, CA


"Resignation of an Excel Guru "

Yes, that's what I meant by concordance; I knew someone would suggest Access!
Alas, whilst I can make Excel sit up and beg, Access and I do not get
along...

Resolving this issue would give me the data that I want in a tool I know how
to use... and tell me more about the tool.

But the thought is appreciated.

Jules

Is this what you mean by concordance ?
"An alphabetical index of all the words in a text or corpus of texts,
showing every contextual occurrence of a word: a concordance of
Shakespeare's works."

Since you seem to be more or less using Excel just as a database, have you
considered using Access to store the bookmarks ?
I would just insert records with the word, maybe an "autonumber" unique
field, and fields for the range information.

Tim.


--
Tim Williams
Palo Alto, CA


"Resignation of an Excel Guru "

Hi! 2 weeks effort down the drain unless you have an answer...

[Office XP SP3 running under XP Pro, SP2, 1GB mem)

I have written VBA in MS Word to generate a concordance for a book <=100k
words. Because of bookmark limitations in word (max ~16387) I have written
automation code to pipe data to an Excel spreadsheet, creating named ranges
on the fly - the name containing the range start and end for the target in
the word document. When opened, the workbook recovers the source doc name
from its own and creates a new automation session to goto words/phrases in
the word document based on the range names (and hence range start/end values)

Everything is fine for small documents, but having just processed the book
(24 hours run-time!) whilst no errors were encountered* (* see below), when
attempting to open the resulting 23MB Excel workbook, Excel fails to

open
the

file, going through a process of repair before deciding it's all hopeless
(for an intermediate snapshot of ~6MB the log file says eventually

"Damage
to

the file was so extensive that repairs were not possible. Excel

attempted
to

recover your formulas and values, but some data may have been lost or
corrupted." - yes, there's data but the workbook is functionally completely
messed up; for the full file I don't even get that!).

Question: specifications say that named ranges are limited only by memory,
but when running the automation xl didn't take more than ~50MB-ish (don't
remember exactly) but much less than the available RAM without even
considering virtual memory. However, given that I have approximately 200k
named ranges, is that the source of the problem? One sheet has the full
65536 rows and a full 256 columns for *some* rows (the others have

~8,000
and

45,000 rows respectively).

Any ideas? Am at my wits end... I tried doing the concordance in Word alone
in the first place, then going via XML output from Word into Excel before
direct automation links... I see no other way ahead!

Best regards

Jules

* there was an error early on when attempting to set cell value and name
range, but invoking xlDoc.save from word and resuming allowed execution to
proceed - MS please note!