Array cleared when closing 2nd document

[Simon]

Hi, is anyone able to help me.

I have a macro that runs from Within a user form.
The user selects several documents (usually 6 to 8) which
contain information in a table of specific format.

Part of this information is pre-marked to go into a new
document. The order is scattered through the several
documents.

My macro opens the documents, and then stores the
information in a 4x100 array. The aray is then sorted.

It then places the information in the new document and
closes the old documents.

I would prefer to only open one document at a time,
extract the information, and then close it before openning
up the next document.
However, when I do this, the information in the array
associated with the documents is lost. Is there any way to
keep the information in the array after the document is
closed?

Thanks in advance

Simon

 

[Jezebel]

Opening and closing documents won't, in itself, clear an array within your
VBA app. Either something is happening to reset your project, or you've got
a problem with the design of the app. Where are your code modules actually
located?

 

[Simon]

Dear Jezebel

The code modules are located in the Normal.dot, in a
Userform. As far as I know, it is all operating from there.

The macro will behave itself only if I leave closing the
documents of the source information until I have finished
with the arrays that obtained information from them.
If I close the documents before the information in the
array is entered into the new document, I get an error
saying the information has been deleted.

The project is not being reset as it still runs through.

Thanks
Simon

 

[Cindy M -WordMVP-]

Hi Simon,

I agree with Jezebel. Once the data is IN an array, and the
array variable is not in any way associated with a document
file/project, the array shouldn't lose its values if a
document is simply closed.

But without seeing the actual project, it's really not
possible to say much more than that.

Best advice we can give at this point would be to set a
breakpoint in the code, then step through and watch the
values. I'm betting either
- the arrays aren't being loaded with the data before the
documents are being closed
- the data isn't being passed from the arrays into the
target; the target is getting the data directly from the
documents
- something is "Re-Dimming" the arrays if the documents
have been closed

The code modules are located in the Normal.dot, in a
Userform. As far as I know, it is all operating from there.

The macro will behave itself only if I leave closing the
documents of the source information until I have finished
with the arrays that obtained information from them.
If I close the documents before the information in the
array is entered into the new document, I get an error
saying the information has been deleted.

The project is not being reset as it still runs through.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Simon]

Hi Cindy,

I have done as suggested and paused through the array.
Then cycled through before closing the documents. This
information is there before had, an dgone when it closes.

Following is my code. If I am pulling the information
directly from the file, I don't understand why.

Thanks for the assistance,

Simon

Notes: Masterfile is a document with a table and 6 columns
in which information is stored. The information relates to
photos.


For numdocs = 1 To docs
Documents.Open FileName:=Masterfile(1, numdocs) &
Masterfile(2, numdocs), _
ConfirmConversions:=False, ReadOnly:=True,
AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
Revert:=False, _
WritePasswordDocument:="",
WritePasswordTemplate:="", Format:=wdOpenFormatAuto

Set masterdocument(numdocs) = ActiveDocument

extension = ".jpg"

NumRows = masterdocument(numdocs).Tables
(1).Range.Information(wdMaximumNumberOfRows)
For rowsa = 2 To NumRows
Set cellvalue = masterdocument(numdocs).Tables
(1).Cell(rowsa, 6).Range
cellvalue.MoveEnd Unit:=wdCharacter, Count:=-1
If cellvalue <> vbNullString Then

photonum = photonum + 1
Set photorecord(1, photonum) = masterdocument
(numdocs).Tables(1).Cell(rowsa, 6).Range
photorecord(1, photonum).MoveEnd
Unit:=wdCharacter, Count:=-1
Set photorecord(2, photonum) = photodate
Set photorecord(3, photonum) = masterdocument
(numdocs).Tables(1).Cell(rowsa, 1).Range
photorecord(3, photonum).MoveEnd
Unit:=wdCharacter, Count:=-1
photorecord(3, photonum) = Masterfile(1,
numdocs) & photorecord(3, photonum) & extension
Set photorecord(4, photonum) = masterdocument
(numdocs).Tables(1).Cell(rowsa, Descol).Range
photorecord(4, photonum).MoveEnd
Unit:=wdCharacter, Count:=-1

End If
Next rowsa
masterdocument(numdocs).close
Next numdocs

reportdocument.Activate

For Insert = 1 To photonum
For arraynum = 1 To photonum
If photorecord(1, arraynum) = "P" & Insert
Then
Selection.InlineShapes.AddPicture
FileName:= _
photorecord(3, arraynum),
LinkToFile:=False, SaveWithDocument:=True

and the array no longer contains the information at this
point. Any Ideas?

 

[Cindy M -WordMVP-]

Hi Simon,

I have done as suggested and paused through the array.
Then cycled through before closing the documents. This
information is there before had, an dgone when it closes.

Following is my code. If I am pulling the information
directly from the file, I don't understand why.

Thanks for checking, again

I see one thing in your code that could be causing problems:

Documents.Open FileName:=Masterfile(1, numdocs) &
Masterfile(2, numdocs), _ 'etc
Set masterdocument(numdocs) = ActiveDocument

It's not said that the ActiveDocument is the one you just
opened. This would be "safer":

Set masterdocument(numdocs) = Documents.Open FileName 'etc.

Also, why are you doing this:
reportdocument.Activate

and the array no longer contains the information at this
point.

At what point, though, does the change occur? Here?
masterdocument(numdocs).close

How is masterdocument() declared? And photorecord(? And why
would you use an array for masterdocument when the documents
are being opened and closed consecutively?

This could explain your problem:

Set photorecord(1, photonum) = masterdocument
(numdocs).Tables(1).Cell(rowsa, 6).Range

Depending on how you declare photorecord(), it may actually
be storing a pointer to the RANGE, and when the document
closes, that range is lost. Try using .Range.TEXT and see if
that makes a difference.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun
8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[Simon]

Thanks Cindy,

This could explain your problem:

Set photorecord(1, photonum) = masterdocument
(numdocs).Tables(1).Cell(rowsa, 6).Range

Depending on how you declare photorecord(), it may
actually
be storing a pointer to the RANGE, and when the document
closes, that range is lost. Try using .Range.TEXT and see
if that makes a difference.

Cindy you were close and it helped me solve the problem.
I think the problem lay in using the SET statement. It
stored a pointer to the Range. Using .TEXT was part of the
solution, as I could not get the information otherwise
without using SET.
After removing SET and adding .TEXT it al worked

Thanks again
Simon

 

[Cindy M -WordMVP-]

Hi Simon,

the problem lay in using the SET statement. It
stored a pointer to the Range. Using .TEXT was part of the
solution, as I could not get the information otherwise
without using SET.
After removing SET and adding .TEXT it al worked

Glad you're up and running

Cindy Meister