Not exactly... better explanation:

I load an xla that manage excel file in some directories. the xla opens a
toolbar with some blocks to draw in these file and shows a menu where I can
create a new sheet (so, a new file), delete one of them ect ect. all these
file are listed in an access database, and everytime the xla shows in the
menu the files present it reads in the access database. all these files have
code in the sheet1 module, so when I create a new one, the xla creates the
code in the new sheet1 module.
but sometimes I have to modify the code in the sheet, so I open the menu via
xla and I delete the code and I rewrite it.

this last modification is what causes the automation error.

so, everything is managed by the xla, so it's not true that there is no link
between xla and excel file. but it's not true that the code added in the
sheet1 recall the xla or something like this.

I hope the structure is a little clearer for you now. : ) it's not very easy
to explain, i know.

thanks for your patience

mark



"Peter T" wrote:

I don't understand what you are saying here

I can say that a connection between the xla
and the file is there, because the xla opens a dialog menu that searches
in a
database and allows to open from the menu the selected file.

Are you saying code in the file that will be modified calls the xla which in
turn modifies the code. If so that is the reason for the problem (as I have
been trying to say all along), and there is probably a better solution.

Regards,
Peter T



"perf" wrote in message
...
Dear Peter,
Maybe I just solved the problem with a trivial solution. I noticed that
deleting the possible code present in sheet1, then save the file, close
it,
reopen it and at that stage write the new code from down to up seems to
work
well. at least so far.
I do not why, but the combination of saving, closing and writing in a
reverse way it's good.

answering to your question, posting all code would require too much time,
because the xla module are many. I can say that a connection between the
xla
and the file is there, because the xla opens a dialog menu that searches
in a
database and allows to open from the menu the selected file. and then it
modifies the code... it sounds a little complicated, I know...

but now it works, this is the most important thing!

thank to you all


mark

: )


"Peter T" wrote:

Explain clearly, is there any connection between the code workbook (the
one
that's writing the changes) and the workbook that's having its project
modified, that includes the "modified workbook" calling code in some
other
workbook.

If not, is there any problem to modify a brand new workbook.

Is there something else going on that we are not aware of.

If nothing above is of relevance post the ENTIRE code.

Regards,
Peter T


"perf" wrote in message
...
Hi peter!
I held my breath.. but it was not enough! I can write all the code
lines
but
the first. everytime I reach the LinesCount to write the line "sub
ZZZ()"
I
obtain an automation error, like before.
I this doesn't happen always, expecially it's ok when I do it in debug
mode,
proceeding line by line writing the code.


what a pity, this solution was very very clever...

thanks anyway for your support!

mark


"Peter T" wrote:

I haven't tested myself but try something like this -

Dim i as Long
Dim LinesCount as Long
Dim arrLines() as String

Redim arrLines(1 to LinesCount)

' populate the string array with lines of code to be inserted in
normal
order
arrLines(1) = "Public sub xxxxxx ()"
'' etc
arrLines(LinesCount) = "End Sub"

for i = ubound(arrLines) to 1 step -1
..InsertLines 1, arrLines(i)
next

' hold your breath!

Regards,
Peter T


"perf" wrote in message
...
oh thanks!!!!

this can help me a lot!

so, do you suggest to do this?

.InsertLines 1, "end sub"
.InsertLines 1, "code line n"
.InsertLines 1, "code line n-1"
...
.InsertLines 1, "private sub ()"


very clever. I'll try soon to do it and I let you know: )

mark

"Peter T" wrote:

That's a very interesting idea and makes perfect sense! It's also
entirely
consistent with what I suggested in my first post here, about the
project
attempting to recompile while other code was running.

Just one thing though, I would only expect the crash problem to
occur
when
code is writing changes to "self", if also the activeworkbook/sheet
also
may
make things even more prone for worse for some reason.

Thanks for posting.

Peter T


"Rene" wrote in message
...
Hi,

New to the forum, so forgive me if I duplicate.

I also had similar problems, where excel crashes when starting to
build up text in a sheet.

I found out the reason for it not working is that Excel screens
the
data you put in, and if not complete, it provides the error you
would
get when you were to manual enter in.

So, starting with "Public sub xxxxxx ()" will cause an error,
(missing
end sub).
(try "Public sub xxxxxx ():end sub" and she's okay, but that
doesn't
help us if we need code in the sub.

What I do now is start building the text from the back, and
always
do
an "InsertLines 1, text"
That seems to do the trick.


.



.



.



.