I have very strong implications that the operation i perform uses memory in such a way that the computer reports errors. The strange part is that the operation is to repeteadly copy a sheet to another workbook after it has updated the sheet using a database.
In my opinion, it shouldn't provide that much grief and memory cry outs. By other words, my suspicion is that Excel saves a lot of undo's or something like that and i wonder how i can clear the buffer so it runs smoothly.
??? where's your code??? if you mean to clear the clipboard - it depends on exactly how your copy/paste is being done, but [VBA]Application.CutCopyMode = False[/VBA]usually suffices
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Wait a minute, now. I just remembered that somebody told me once that Excel (or was it all of the Office application) had a multiple clipboards, hence capable to store many different chunks of "thingies". I thought he was yanking my chain but perhaps i was mistaken.Originally Posted by johnske
I'll try that right away. Thanks.
< 10 hopeful minutes later >
I'm sorry but it didn't work. I'll prepare the code and post it so you can see for yourself what kind of dummy approach i've used.
Last edited by chamster; 10-04-2007 at 10:57 PM.
Here it is. First, a few things if you're going to run it.
Does the error occur on your computer as well? Remedy?
- Run the MultiMain ().
- On my computer, the problem occurs before April (see direct window) producing a total of 58 sheets or so.
- You need to have a file called "Summaries.xls" open. You can create it yourself.
- It is the same page copied over and over again. In the real version, a different page is created.
P.S.
This erropr is of such complicated nature that i'll be willig to offer a small thank you amount. Nothing big, really. Still, something for reading the code i believe somebody should get.
You could try to save the workbook after 5 copy operations. According to this kb from Microsoft : http://support.microsoft.com/?kbid=210684
can't open or extract your file. getting an 'unknown compression method' message
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
true, excel 2000 has 12, later versions have more - but still limited, not infinite
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
I'm saving every 2 iterations, actually, so it shouldn't be it. As i execute a for-loop, the program starts to complain about #7 (memory) after about 60 iterations. If i go, say, 40 iterations, then exit Excel and continue for that point, it works for another 40+ iterations. As long as i restart Excel, that is.
That's a surprise. I used WinZip 9.0 to put it in, maximum compression. That was the only way to get it below the limit. If you give me your e-mail i can send it to you.
What about closing the workbook you're copying to (in coding) and reopening it to continue the copying ?
if it's really a memory problem (in this case i suspect it's not, the repeated saves would only be compounding the problem) just closing the workbooks won't clear the buffer, you have to actually quit excel to do that.
it may help you to read this chamster
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
I've tried that too. To no avail, sadly...
had a quick look at your file and yes, am getting that error too. it's too late to look for why, but someone'll probably get it for you before i come back.
it's also excrutiatingly slow with all the 'saves', move them outside the loop and save once just before End Sub
just a couple of FYIs, in visual basic you must explicitly state each variable type or it will be declared as type Variant by default (the slowest and most memory-intensive type). so statements such as [vba]Private rName, mName, yName As String [/vba]means rName and mName are declared as type Variant and only yName is declared as type string. if you need for them all to be string you need to use[vba]Private rName As String, mName As String, yName As String[/vba]
also, type Integer is converted to type Long by visual basic so it's generally conceded that type Integer is really a 'non-type' and you may as well just declare integer variables as Long
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Hehe, i wouldn't be so sure, my friend from Down Under. It seems you're the only one able to come with something on this one. I hope i'm mistaken but it sure looks bad for me...
I was told to save regularly to save the memory. I'll be more than happy to move it.
Ah, didn't realise that. There will be a lot of typing of "as this/ as that" on Monday, hehe.
This is a surprise. I've read in the documentation somewhere that Byte was 1 byte large, Integer 2 byte large and Long 3 or 4. That seems to be supported also by the fact that i got errors when i'd reached 2^16 while there were no errors in the same situation for declaration as Long.
Looking forward to see if you can kill the memory issue.
As Johnske says it is considered "non-optimal" to use integers, they do only occupy 2 bytes but every time they are used they are converted to longs (conversion time loss) and then then occupy 2 bytes for the integer and 4 for the long (so it actually, in some wierd circumstances, take up more memory. plus all the pickup and drop out of these temporary longs 'may' cause memory leaks (though this is unproven). Either way it's not ideal to use integers and the documentaion "should" point this out and tell people to discard integers, but ...
2+2=9 ... (My Arithmetic Is Mental)
This was very informative, if yet strange. Well, given that i know it now, i'll switch to Long all the way. Is something similar valid for other data types as well?
I don't know about the memory leaks, never heard that claim or any evidence to such, but there are some places where you have to use Integer, Long doesn't work. For instance, if an API declaration uses Integer, you have to pass an integer, not a long.
Other than that, I agree in principle with what has been said, it is just more efficient to use Long.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
it appears that this issue may have been resolved... the workbook is too large to post so i've emailed a copy to chamster first for testing on his machine. if ok, i will try and reduce the size of the workbook and post it here...
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
First of all - cautious thanks to you. Cautious because i need to see it flow with my own eyes, hehe. Nevertheless, you're not exactly a rookie so if you say so, there's a really good chance the sucker is down.
Now, to the other matter. In my fury about the above mentioned sucker, i offered a limited monetary compensation (or an equivalent thereof). That offer is of course still valid provided that:
a) the issue is actually solved, killed, hung and then killed again,
b) you wish to receive such a compensation the exact extent of which we can discuss more privately.
I'll take a look first thing on Monday as you've sent the file to my work address, i guess.
the technique you used in your ExportData procedure viz: [vba]wbSrc.Worksheets("Summary").Copy After:=.Worksheets(1)[/vba]
in
[vba]With wbDst
wbSrc.Worksheets("Summary").Copy After:=.Worksheets(1)
.Worksheets(2).Name = nameToBe
.Worksheets(2).Move After:=.Worksheets(endOfSummaries + 1)
End With[/vba]
seemed to be reaching some sort of inbuilt limit and was raising an error at about sheet t1060 (~60 sheets). by erasing your error handling and using a simple On Error Resume Next just to see what happened, i found the code continued running, but then appeared to be in an infinite loop and was first adding, then deleting sheets. tweaking the code, i found it then started to do some pretty weird things - at one stage it was even acting quite illegally and putting a number of sheets with the same name in the workbook!!
i then decided on a rewrite using a more conventional approach, i.e. explicitly adding sheets, then doing a copy/paste using
[vba]
With Workbooks("Summaries.xls")
.Sheets.Add after:=.Sheets(.Sheets.Count)
.Sheets(.Sheets.Count).Name = "t" & .Sheets.Count + 1000
Workbooks(docName).Worksheets("Summary").Cells.Copy .Sheets(.Sheets.Count).Range("A1")
End With
[/vba]
this allowed me to add and copy up to around sheet t1209 (~209 sheets), and i'd then get an Excel out of memory error message (in Office 2000) but this appeared to be (possibly) partly dependent on running background processes because a couple of times it actually completed the task (at sheet t1235 ~ 235 sheets), however it would quite possibly complete the task in (your) office 2003
looking further, i saw you were activating each sheet and screen updating was being left turned on, as a consequence, each individual sheet was being painted and an image of each individual sheet (and its embedded chart) was thus being retained in memory. so by turning off screen updating (and also not activating each sheet) the code then ran to completion every time (6-7 times) it was run in office 2000. however, note that you may still have a problem with sheets with more data on them.
try the attached, i've deleted a lot of your code modules to upload it, it's also been heavily modified, and so may not do exactly what you want it to and you'll probably need to tweak it.
BTW, the method you're using is inherently memory intensive and i would suggest you look at other ways of accomplishing the task i.e. do you really need ~235 similar sheets? can your data not be condensed to a lesser number of sheets, or perhaps analyzed differently?
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Posting Permissions
Reply With Quote
