Solved: Document File Size

[Dave]

I'd would like to know how many pages can be in 1 Word document file. Is there a limit and what is the limit dependent upon? On a related matter, I'd also like to know how many lines of a Word document can be stored in a string variable. Is there a way of calculating this or is it dependent upon what characters are in each line? Also, is it possible to check how much remaining storage "space" is available in a string variable before adding more info to it? Many questions with no answers. Any help will be appreciated. Dave

[MOS MASTER]

Hi, :D

Like a lot off things the count off pages in a word document depends entirely on the system resources you're running.
So you can not say Word can hold 400 Pages Max! (on this system maybe 400 and on the other perhaps 500)

Problem with documents with a lot of pages in them is that they corrupt easily!

In you're VBE editor just type in String in the help box and look up the string Data Type. The spec's of what a string can hold is in there. (I would have copy paste it for you but mine is in dutch so that's no help for you) :rofl:

If you wanna check the string for it's contents then use the "LEN" function to retrieve the length off the characters in it and compare that to a number you say is the Maxlength!
Enjoy! :thumb

[Howard Kaikow]

I'd would like to know how many pages can be in 1 Word document file. Is there a limit and what is the limit dependent upon? On a related matter, I'd also like to know how many lines of a Word document can be stored in a string variable. Is there a way of calculating this or is it dependent upon what characters are in each line? Also, is it possible to check how much remaining storage "space" is available in a string variable before adding more info to it? Many questions with no answers. Any help will be appreciated. Dave

No limit on number of pages, other than that imposed by the memory available on your system. Tho, I expect that Word uses an internal counter for the page number, so it is likely limited to eiter 64K or 2GB.

While VBA does allow for using fixed length strings, no programmer in their right mind would use them except for very special purposes. Indeed, VB .NET drops fixed-length string support.

So a string is always variable length.
There's no such thing as unused space.
You could fill a string to a particular size and programmatically determine how much has been used, but that is usually useful for more complex string handling using, say the MID$ function to insert substrings.

[Dave]

Many thanks and much stuff to mull over. I may post a follow-up after I try a few things out. Dave

[MOS MASTER]

Hi Dave, :D

You're welcome!...looking forward to you're results..:thumb

[Dave]

In this example, the string contains the entire file contents. Perhaps waiting on the error and then managing the specific error (overload?) would be better? I'm thinking that if an error occurs, new temporary file(s) could be created in an orderly direction using copyfile, a copy of the string (as it existed before the error) could be copied to the new file, and the string could be reset to vbnullstring. This followed by a return to program code from the error code. Is this only half baked or is it worth kicking around? Dave

[fumei]

May I ask what exactly are the circumstances that require the contents of the file to made into a string variable. What precisely are you trying to do?

[MOS MASTER]

Hi Dave, :D

I'm also curious on what you're trying to do.

To make one point it ussualy is very slow to load the entire range of a document in to a string and it depends on what you're trying to do if there are better ways.

Enjoy! :thumb

[Howard Kaikow]

Hi Dave, :D

I'm also curious on what you're trying to do.

To make one point it ussualy is very slow to load the entire range of a document in to a string and it depends on what you're trying to do if there are better ways.

Enjoy! :thumb

Not to mention, but I will anyway, working witha doc in a string is far less effecient as one loses thre ability to use methods for navigation and modification of the document, and there may be hidden stuff in a document that just does not get saved in the string.

[Dave]

Thanks for your interest. I'm re-thinking my whole data management strategy. Previous to my new found ability to extract specific records from a Word document, I had made seperate files each with 1 document to contain supporting records for seperate expense accounts. For example, I would have a variable expense file containing a seed document. In XL, I would store the total seed expense and then store the relevent details of each purchase in this seed expense file. As such, I have created many files with seperate documents many of which have multiple pages. Having learned that it is possible to find and extract specific records from a Word document, I am now wondering why I don't just have 1 file with 1 document for all the records. The reason I think is that probably at some point in time there may be too many pages for 1 document in a file. Also, I use a string variable to temporarily hold extracted records before placing them on a single document for presentation. It seems to me that at some point there will be too many records to temporarily store them in a string. So, before I jump in to making exhaustive changes I thought I should address these potential error issues. Any help towards this outcome would greatly be appreciated. Dave

[Killian]

Hi Dave,
I just thought I'd chip in with a couple of comments...
I'm not completely clear on how much other data you're storing, but from you're last post it seems to me that you're existing strategy isn't too far off the mark. An individual expense file for each account is a nice safe secure way to manage things - I assume they are liked in some way to the Excel spreadsheet so you can reference them easily? (if not, there's a project!)
At the risk of offending those who hold MS Word close to their hearts, my experience has taught me that the last place you should keep large amounts of frequently used and important data is in one gigantic Word document.
I certainly don't want to discourage you from using your new found abilities - but a large Word document will take a while to open (longer still if if it has fields, etc to update) and take up a lot of resource - it's quicker and more efficient to link to (or even filesearch) and open a small doc than it is to open an very large one especially if you only want to extract one record.

[Dave]

Thanks Killian and others for this discussion and for your experience in the matter. It's exactly the kind of info that I thought that I should consider before undertaking changes to improve a program which may instead crap it out. The Word information is not linked to XL. It is a supporting record for XL's stored totals. No real need to link is there? I think that I will stick with my original design with some modifications. I need to add another commodity expense to the same file storage locations so I will use the record extraction routine to seperate the expenses by commodity from the same Word doc. I don't believe that this will produce too many pages or overload my string variable (I'm not sure if I followed the discussion on this point?) I still may invest some effort towards preventing this unknown. Dave

[MOS MASTER]

At the risk of offending those who hold MS Word close to their hearts, my experience has taught me that the last place you should keep large amounts of frequently used and important data is in one gigantic Word document.

Aaaaaaaaah that hurts! :devil:

No really I would never use Word as a database so couldn't agree with you more...:rofl:

@Dave,

Good luck on redesigning...

[Howard Kaikow]

Hi Dave,
I just thought I'd chip in with a couple of comments...
I'm not completely clear on how much other data you're storing, but from you're last post it seems to me that you're existing strategy isn't too far off the mark. An individual expense file for each account is a nice safe secure way to manage things - I assume they are liked in some way to the Excel spreadsheet so you can reference them easily? (if not, there's a project!)
At the risk of offending those who hold MS Word close to their hearts, my experience has taught me that the last place you should keep large amounts of frequently used and important data is in one gigantic Word document.
I certainly don't want to discourage you from using your new found abilities - but a large Word document will take a while to open (longer still if if it has fields, etc to update) and take up a lot of resource - it's quicker and more efficient to link to (or even filesearch) and open a small doc than it is to open an very large one especially if you only want to extract one record.

it does seem that storing the information in excel or in a database (via access or not) would be a better approach.

i maintain data for each client in a separate excel workbook, and i generate reports within excel.

doing so in word would be cumbersome, at best.

[Dave]

Thanks all! Word as a data base? It seems to be a simple way to store all the useless supporting information outside of XL... and the information can be easily printed. From XL, you don't need to recall or use the Word information, you only have to find where it exists. Not really a data base, just a data storage process. I thought XL general efficiency would be better with less clutter. I don't know access but I'm not sure if I need too? Dave

[Killian]

Hi Dave,
The only concern here is the inefficiency of retrieving a record from a (potentially large) word doc. If you keep your background data in individual word docs and have each path (or hyperlink) as a field in the Excel record, you'll be in good shape for quick retrieval and background printing.
Also, if the docs are all in the same folder and the filenames are derived from an existing field in the record (like a ref number or company name), it'll only take a couple of lines of code to implement it.

[Howard Kaikow]

Thanks all! Word as a data base? It seems to be a simple way to store all the useless supporting information outside of XL... and the information can be easily printed. From XL, you don't need to recall or use the Word information, you only have to find where it exists. Not really a data base, just a data storage process. I thought XL general efficiency would be better with less clutter. I don't know access but I'm not sure if I need too? Dave

One of the advantages of an integrated suite of programs, such as MSFT Office, is that each app in the suite offers advantages over the others for particular types of applications, more so, when efficiency of execution is considered.

Word just is not designed to be a storage recepticle or to be used as a database.

Excel can be used to create a database that can be accessed via most database software, including Word itself. Ditto for Access.

Note that Excel VBA is actually a lot easier than Word VBA and the books for Excel VBA are quite superior to those for Word VBA.

Access VBA is more difficult. But, one need not even use Access to use a database. One can create the database howsoever and access the data base using, say, DAO or ADO, via Word or Excel or ...

Often, a database in Excel or Access or ..., is utilized by retrieving the data using Word VBA.

[Dave]

Thought I'd do a little string error testing. A string can be overloaded. In my following example, the cnt is 32767 loops (it takes a while). Interestingly the message box doesn't contain this many (-1) repititions of the test phrase represented by "b"? Is this loop number determined by my system resources or by a standard available amount of storage space for a string variable? Can someone give this a quick trial and post the loop cnt? Also, any explanations why the message box doesn't contain the string present before the error occurred which is what "b" was supposed to do? Dave
Private Sub CommandButton1_Click()
Dim a As String, b As String, c As String, cnt As Integer
On Error GoTo Errcntrl
c = "I'm gonna break"
Do
a = a + c
b = a
cnt = cnt + 1
Loop
Exit sub
Errcntrl:
Select Case Err.Number
Case 6
MsgBox "Overflow " & cnt & b
Case Else
MsgBox Err.Description
End Select
End Sub

[MOS MASTER]

Hi, :D

You're string doesn't overflow it's you're counter witch is declared as Integer!

Integer can hold: -32.768 to 32.767

So use Long type instead!
Long can hold: -2.147.483.648 to 2.147.483.647

Enjoy! :whistle:

[Howard Kaikow]

Thought I'd do a little string error testing. A string can be overloaded. In my following example, the cnt is 32767 loops (it takes a while). Interestingly the message box doesn't contain this many (-1) repititions of the test phrase represented by "b"? Is this loop number determined by my system resources or by a standard available amount of storage space for a string variable? Can someone give this a quick trial and post the loop cnt? Also, any explanations why the message box doesn't contain the string present before the error occurred which is what "b" was supposed to do? Dave
Private Sub CommandButton1_Click()
Dim a As String, b As String, c As String, cnt As Integer
On Error GoTo Errcntrl
c = "I'm gonna break"
Do
a = a + c
b = a
cnt = cnt + 1
Loop
Exit sub
Errcntrl:
Select Case Err.Number
Case 6
MsgBox "Overflow " & cnt & b
Case Else
MsgBox Err.Description
End Select
End Sub

What are you trying to prove?
You've already been told that a string can hold about 2 billion characters.

[MOS MASTER]

What are you trying to prove?
You've already been told that a string can hold about 2 billion characters.
Haha that's true..good one Howard, forgot about the previous bitt off this questionne...:rofl:

@Dave
So if you use the LEN function on the text you want to load in to the string you can deside to use the Mid (Left, Right, etc..) Function if need be to pass Substrings in to other string variabels to hold the values en concenate them in the document.

But I think you should just code you're program and come over here with the code if you run into problems! :whistle:

[Dave]

"You're string doesn't overflow it's you're counter witch is declared as Integer!"
Now that is funny. :rotlaugh: Appearances can be deceiving. Some days you learn more than others. Not trying to prove anything. Just trying to prevent crashes if an overflow error occurs. This was just a feeble start and somewhat amusing I'll admit. The notion was to always have a backup copy of the string so when it overflowed you could copy the backup to Word, reset the error, set the original string to vbnullstring, then return to the routine and continue on with what you were doing like nothing happened. It needs a bit of work. However, it's been about 15 minutes since I set the above code loose on a long variable and still no message. It's hard to grasp what 2 billion characters will do for you. More than likely adequate for what I'm doing. I think I'll mark this as solved as the effort isn't worth the gain. Very much appreciate all the help. Dave

[MOS MASTER]

"You're string doesn't overflow it's you're counter witch is declared as Integer!"
Now that is funny. :rotlaugh: Appearances can be deceiving. Some days you learn more than others.
Very much appreciate all the help. Dave
Hi Dave, :D

You're welcome and glad you have as much fun as I have! :rofl: