VBA clear memory

Too little information about the code.

Kind of hard to help without seeing at least some of the code.

How exactly are you copying?

Are you using Select?

Are you turning of CutCopyMode in the appropriate places?

Originally Posted by Norie

Kind of hard to help without seeing at least some of the code.

How exactly are you copying?

Are you using Select?

Are you turning of CutCopyMode in the appropriate places?

Didn't use select.

Range (). Copy
wbB. Cells(lastrow +1, 1).paste

Basically, copy four tables from each sheet. I do have PasteSpecial paste as value

Something like that, hard to type in cellphone.

What is cutcopymode used off? If I turn it off, can the code continue to copy?

Thanks.

Copy Paste, Somefink, somefinkElse, Clipboard Flicker, FLICk OFF !

Hi niuyuer,

Originally Posted by niuyuer

.....where to insert the code Application.CutCopyMode = False ? What is purpose of the code Application.CutCopyMode = False? What is the default value of CutCopyMode (do I need to set it to True at the end of program) ?......

If you copy anything to the clipboard you will see that the Clipboard symbol up there top left goes “highlighted” orange/yellow showing that there is something in it. And if you copied a Range, then that Range will “flicker” in the Worksheet.

After
Application.CutCopyMode = False
The flicker will go away and the Clipboard Symbol is no longer highlighted yellow/ orange. This indicates that the clipboard is empty.

As Rory said you usually put that line after a “Paste” ( or “PasteSpecial”). Often it is missed out as sometimes other actions clear the clipboard by the way. Also you will see he added it after your last Copy / Paste, as there is no need to do it in between ( as Far as I can think ? ) as every Copy overwrites what is in the Clipboard.

It can also get a bit confusing due to the various Copy and Paste ways that you have available in VBA.

If you do not empty the clipboard you can merrily go on pasting it out in different places, and in different forms at that!!!!

I have never seen it do any harm to use_....
Application.CutCopyMode = False
_..... if the clipboard is already empty. So i have often used it in a code when the Clipboard may or may not have been empty. Mainly I just used it to get the annoying “Flicker” Off!! ( I do not know what the purpose of_...
Application.CutCopyMode = True
_... is - never seen it used ?? . You do not seem to need to use it before you Put something in the Clipboard with .Copy – possibly .Copy does that by the way ?? )
I do not think or know of any situation when you would need to set Application.CutCopyMode to True
_... at the end of program , or anywhere.... .. so It „….doesn't need to be reset to anything….“… may be ?

_........

The following demo code is not extensive, but will give a bit of an idea of copying to clipboard, pasting out and emptying the clipboard. The code will work on the first Worksheet of the File the Code is in. Best is to step through it in Debug ( F8 ) mode, should you feel so inclined, and see what happens in the Worksheet. It is explained a bit in the ‘comments

Alan
“Flicking – off” for a while.....

Demo Code

Please Login or Register  to view this content.

P.s. You had a couple of new answers to your last Thread
http://www.excelforum.com/showthread...t=#post4413279

Even if I add Application.CutCopyMode = False, it seems it makes no difference to my original code, it can still crash. Anyone know why it keeps crashing when dealing with large number of files. Actually, it is not large, it crashes when there are more than 50 files, only one or two sheets in each file, don't really copy a large range from each sheet. But if I split the number of files into multiple folders (each folder has less number of files), then it can run successfully.

All files are not saved locally on the hard disc, they are on the network location. Actually, it is remote desktop from cloud service.

It is something related to clipboard, I guess.

cb.jpg

Originally Posted by niuyuer

Why excel keeps crashing?

Sorry, I cannot help further on that one.

I know no simple all encompassing answwer to why Excel Keeps crashing...: )

I never had a crash as a result of anything to do with the clipboard ( as far as i know ) . Codes often crash if I run after correcting them. Usually running once after a correction in Debug F8 Mode cures that.
http://www.mrexcel.com/forum/general...l-crashes.html
I sometimes also find my VBA crashes if I try to do too much too quick. Possibly a case of VBA running ahead of itself ant tripping over if something is not quite finished internally yet. Maybe add a few message boxes or wait statements like
Application.Wait(Now + TimeValue("0:00:03")) ' Wait 3 seconds
To slow things down a bit

But crashes are a pain in the bum, especially when you are dealing with big things...I sympathise.
Try to reduce stuff bit by bit, to narrow down where the problem is coming...
( And no one can really help too much if you do not give more detail as Norie said in Post # 2 )

I hit an unanswerable brick wall crash myself.. Excel always crashed when I opened a very large File with code. It never crashed when I opened it manually.
http://www.excelforum.com/excel-prog...orm-issue.html

I reduced and reduced my Files until the Excel never crashed.....I found a possible culprit, and workaround:-
The workaround was to delete a UserForm, but as I was thinking of wanting one at the time, that was not too helpful... Lol...

Alan

.Copy . Cut Modey Read Write, Right Mode . Wrong Mode Case Is. Flick Off___#A14

_1)

Originally Posted by rorya

There are three valid values for Application.CutCopyMode:

xlCopy
xlCut
False

-... sort of ties up with this...

_2)
https://msdn.microsoft.com/de-de/lib.../ff839532.aspx
“....Application.CutCopyMode:.... Returns or sets the status of Cut or Copy mode. Can be True, False, or an XLCutCopyMode constant....”
Although the latter ( 2) ) suggest maybe four possible states, the former ( 1) ) maybe suggest 3, which is what I seem to get..
But neither really says what is going on, fully....

The demo code below seem to recognise a
.Copy ' Recognises Case Is = xlCopy, and Returns "1"
Or
.Cut ' Recognises Case Is = xlCut, and Returns "2"

_... everything else is taken as
___ ' Recognises Case Is = False, and Returns "0"

_...................

Well , that does suggest I suppose, that there are three valid values for Application.CutCopyMode, - as Rory said......

_..................

But does this tell me that
Application.CutCopyMode = True
And
Application.CutCopyMode = xlCopy
And
Application.CutCopyMode = xlCut

Have no meaning. A case of VBA suggesting with intellisence and accepting things of no significance??? The Microsoft article, conveniently does not do a
Case Is = True


Or maybe that is hidden in what this

Originally Posted by rorya

......

The first two are only for reading

.

means.....??

Alan

_.....................

Please Login or Register  to view this content.

Hi niuyuer,
Please take out some of text in quotes as it makes the Thread a little easier to follow, like this

Originally Posted by Doc.AElstein

.......
I sometimes also find my VBA crashes if I try to do too much too quick. Possibly a case of VBA running ahead of itself ant tripping over if something is not quite finished internally yet. Maybe add a few message boxes or wait statements like
Application.Wait(Now + TimeValue("0:00:03")) ' Wait 3 seconds
To slow things down a bit.......

Thanks

_.._______________________

Originally Posted by niuyuer

Where is the best place to put Application.Wait....
.....

I do not know where the best place would be. Using a Wait was a bit of a long shot from me. I suppose the best place would be just before the code crashes, which as I mentioned I would try to find. But the code snippet you gave is quite small, so you should experiment.

Originally Posted by niuyuer

......
Anyone knows how to add some code and make sure the file finishes closing before opening next file, the file also needs to finish opening before moving to next line to copy data......

It is very difficult helping someone at such a difference with such a problem , as repeating the problem is very difficult. And I do not have the experience with typical crashing causes. I usually see in my codes that Excel always waits after closing or opening a file until the action is complete before moving on. I was making a wild guess that maybe occasionally that is not the case, so a wait between , for example the command to close and open might help. But that is for you to experiment with.
_......................

The best I can do is maybe suggest some alternative techniques to doing what your short Demo Code snippet does.

The next code is basically the one you gave. I modified it slightly so as to test it on a Folder of mine which had about 30 Files in it. It worked on my Files without crashing.

Please Login or Register  to view this content.

_..........................

In the next posts I give a couple of alternatives. I suggest you make sure you understand and have a version of the above code working before you take a look at the alternatives I give you.

Alternative 1:

I noted that you are copying just values. In which case you do not need to use the Clipboard at all. You simply assign the values to your Summary Worksheet to those of the values in the opened Data Worksheet.

This is the direct equivalent to my last code which uses this Value = Value method. It gave identical results to the code in the last Post

Please Login or Register  to view this content.

_.........................................

Alternative 2:

I only mention the basic idea here briefly. Basically it is possible to get values from a range in a closed Workbook. One problem is that you need to get the name of your sheets. I have read that this is possible, but could not get any given code to work. So here I reduce the problem just to reading data from the Worksheet with the name “Sheet1”.

You need this Function Code:
http://www.excelforum.com/showthread...t=#post4416483


_......

Originally Posted by niuyuer

..I believe the crash is caused by network speed, and I have no problem to run a large number of files on hard drive. I only copy a few times from each file, so there are too many Open File - Close File - Open File - Close File ... actions, and it may cause excel frozen......

-.....Note: The following code does not open or close any Workbooks. !!
I ran the code on the same Test data as the other two. It gives identical results. As the Workbooks are not opened it works a lot quicker.

Code:

Please Login or Register  to view this content.

_................................................................

Working at such a distance I cannot really help anymore on this crashing problem.

Alan

' Rem ref
' http://www.excelforum.com/excel-prog...ing-excel.html

EDIT: I found a code that does work to get at Worksheet names from a Closed Workbook. So I extended the last Code to include all Worksheets in the Closed Workbooks.

You will need the following Function ( which gets a collection of the Worksheets in a Closed Workbook ) :
Function TMSChucksOutClosedWBShtNames(WBName As String) As Collection

That Function is here:
http://www.excelforum.com/showthread...t=#post4416777

( _....In addition you still need the function
Public Function PutACSElinkInAndGetValue(ByVal FullFilePathAndName As String, ByVal ShtName As String, ByVal strCells As String, rngOutTopLeft As Range) As Variant
http://www.excelforum.com/showthread...t=#post4416483
_....)

_........................

Here the main code:

Please Login or Register  to view this content.

_................................

I tested it again with the same Test data as the last three codes. Results were identical. But it does take quite a while as the Function to get at the Worksheet Names from the Closed File takes quite a while.
But another alternative

Alan

Originally Posted by rorya

You can use ADO to get sheet names from a closed workbook

I can't .-)
_... I expect you can..
_...

I found quite a few references on how get sheet names from a closed workbook Today, but the one in Post #20 was the first one I got to work..
Have you got a code example or Link to a " ADO ADOBD" version that works.. ?
Alan
P.s. I am using XL 2007, that probably effects what Library references I need? ( Ideally a it is nice to see a code that will work with a late and early Binding Form... Early binding helps me to get stuff from Intellisense, and Late Binding is better to share etc...etc..

EDIT : My code is using ADO ( ADOBD ) ? Isn't it :confused:
http://www.excelforum.com/showthread...t=#post4416777

Bring back the Ban erd , I say , or probably not

Have you got a code example or Link to a " ADO ADOBD" version that works.. ?

The following macros are examples of late and early (Macro1 and Macro2 respectively) binding examples of how to get sheet names from a workbook (which seems to work whether the workbook is open or closed ):

Please Login or Register  to view this content.

Regards,

Robert

Originally Posted by Trebor76

The following macros are examples of late and early (Macro1 and Macro2 respectively) binding examples of how to get sheet names from a workbook (which seems to work whether the workbook is open or closed ):
....

Hi Robert ( Trebor76 )
Thanks very much Robert for adding a couple of alternatives. I do not really understand too well these things, so it is good to have alternative working Codes in case one does not work one day and I do not know why!!

I see I have one less Library to check ( in the Early binding version - I do not need the Microsoft ADO Ext. X.X for DDL and Security ). So I guess this is a full ADO ADOBD version.
( I have no real idea unfortunately what all these ADODB and ADOX things are.. )
Otherwise your codes seem very similar to that for Trevor TM Schucks.
http://www.mrexcel.com/forum/excel-q...ml#post3637224

As regards it working whether the File is open or not, - I guess the working of the code is not interested ( not effected ) by whether the File is “open”, - it still “goes through the connection” to the actual File. ( _..... If I understand these things correctly what one thinks one has “open” is not really the File, but some Pseudo copy or just something that looks like it and changes its appearance when you do things to it to show how changes would appear in the File. Only on saving is your actual File changed
_ Point 2) http://www.excelforum.com/showthread...t=#post4413285
_ Post #3 http://www.mrexcel.com/forum/excel-q...cro-files.html
_....)

_.........................

I had a very minor follow up question:
In the Early Binding version you use
oConn
and
oRST

In the Late Binding you use
objConn
and
objRST

I believe there is no significance to that ? Is that correct. In end effect they are identical Objects. ? Or have I missed anything subtle there ?? ( I would probably also use obj___ in the late Binding case just to remind me that I did a Dim ____ as Object )

Thanks again for your contribution

Alan

_.......................................

Please Login or Register  to view this content.

Duplicated below

I had a very minor follow up question:
In the Early Binding version you use
oConn
and
oRST

In the Late Binding you use
objConn
and
objRST

I name my variables based on the Leszynski naming convention that was developed for Access. The early binding variables don't follow that convention as I had just copied them as they were from another website

Thanks for the rep

HTH

Robert

Originally Posted by Trebor76

I name my variables based on the Leszynski naming convention that was developed for Access. The early binding variables don't follow that convention as I had just copied them as they were from another website .....

Thanks Robert