Excel 2007 Clear Cache at run time?

[wolf.stalker]

Greetings all!

Is there a way to clear the cache by means of vba code?

i have noticed in 2007, it starts to lag doing repetative copy/paste routines. when we were 2003, this was not an issue but in 2007 this has become one. it used to take 30-60 seconds to run my macro and it now takes 3-5 minuets. the only thing that changed is the version :-\

i have looked around but i am not finding anything useful. any one have an idea?

[Paul_Hossler]

You can clear the UnDo stack in VBA


http://excel.tips.net/Pages/T002463_Clearing_the_Undo_Stack_in_a_Macro.html


Paul

[wolf.stalker]

um, that link really dosen't do much for me.....

"In Word, you can use the UndoClear method with the ActiveDocument object in order to clear the Undo stack. "

"Excel VBA doesn't provide a method like UndoClear. The reason is because the undo stack is automatically cleared by Excel whenever your macro makes a change (any change) to the workbook."

[Ken Puls]

Honestly, I'm not sure what you're after here. You want to clear the clipboard, or...?

[Paul_Hossler]

Sorry, the only 'cache' I could think of that you might want to clear after a lot of cutting and pasting is the UnDo stack. I thought the link would help, esp the part about how to clear the UnDo stack



If your macro doesn't make any changes, and you still want it to clear the undo stack, then all you need to do is make an innocuous change to the worksheet. For instance, the following macro copies the contents of cell A1 back into A1, and in the process clears the undo stack:
Sub ClearUndo() Range("A1").Copy Range("A1")End Sub

I never really tried this so I don't how well it might work.

Paul

[Ken Puls]

Maybe inserting the following after each paste command:

Application.CutCopyMode=False

It may clear some stuff up for you...

[Kenneth Hobs]

Maybe you are copying and pasting more rows than in the previous version?

I offered some standard speed tips in this kb article. http://www.vbaexpress.com/kb/getarticle.php?kb_id=1035

You might also try method 1 or 3 below if you want to clear the clipboard.
Option Explicit

Declare Function CloseClipboard Lib "user32" () As Long
Declare Function EmptyClipboard Lib "user32" () As Long
Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long

Sub ClearClipboard()
OpenClipboard 0&
EmptyClipboard
CloseClipboard
End Sub

Sub ClearClipboard2() 'Does not work.
Application.CutCopyMode = False
End Sub

'http://www.cpearson.com/excel/clipboard.htm
Public Sub ClearClipboard3()
Dim MyDataObj As New DataObject
MyDataObj.SetText ""
MyDataObj.PutInClipboard
End Sub

[wolf.stalker]

thank you for your help, but my problem, in a nutshell, is this. in 2003, i had a workbook that took 20+ worksheets and by measn of vba code, it coppied the sheet and pasted it into a new workbook and then saved that workbook as the worksheet's name. sounds simple right? back then, took me less than a min to run this task for say 23 worksheets. it now takes me like 3-5 min to run the same macro in 2007.

i am pretty sure it has to do with the "copy" but i just don't know how else i can copy a worksheet and paste it into a new workbook any easier than the way i am currently doing it. i thought maybe clearing the cache after each copy but i don't know. all i know is it seems to take foreEVER to do this routine now opposed to doing with 2003.

here is the code i am using

For Each wkSheet In CurWkbook.Worksheets
If wkSheet.Index < 5 Then 'only want worksheet index 6+
'do nothing
Else
shtcnt(1) = (6)
wkSheetName = Trim(wkSheet.Name) 'get name of worksheet
wkSheetName = wkSheetName & " " & dtimestamp 'add date stamp to worksheet name
Workbooks.Add ' auto build a new workbook
ActiveWorkbook.SaveAs Filename:=xpathname & wkSheetName & ".xlsx", FileFormat:=xlNormal, password:="", WriteResPassword:="", _
CreateBackup:=False, ReadOnlyRecommended:=False 'how we save new workbook with name and stuff
Set newWkbook = ActiveWorkbook
Application.DisplayAlerts = False
newWkbook.Worksheets("sheet1").Delete ' remove sheet1
On Error Resume Next
newWkbook.Worksheets(wkSheet.Name).Delete
On Error GoTo 0
Application.DisplayAlerts = True

CurWkbook.Worksheets(wkSheet.Name).Copy before:=newWkbook.Sheets(1)

ActiveWorkbook.Sheets(1).Select
Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
Next wkSheet

[Tinbendr]

...but i just don't know how else i can copy a worksheet and paste it into a new workbook any easier than the way i am currently doing it.
Another way, and I don't know if this would be any faster, is to copy the entire workbook to a new file, then delete the sheets you don't need.

[Paul_Hossler]

I think what you're after is to take a WB with N worksheets and create N-5 new workbooks, each containing one of the worksheets from the original, from the 6th on???

If so, this bit of code seems to run pretty fast


Option Explicit


Sub MakeData()
While ThisWorkbook.Worksheets.Count < 21
ThisWorkbook.Worksheets.Add
ActiveSheet.Name = "WSNUM-" & Format(ThisWorkbook.Worksheets.Count, "000")
Wend
End Sub


Sub CopySheets()
Dim wkSheet As Worksheet
Dim NewWkbookName As String, CurWkbookPath As String
Dim CurWkbook As Workbook, NewWkbook As Workbook

Application.ScreenUpdating = False

Set CurWkbook = ThisWorkbook

For Each wkSheet In CurWkbook.Worksheets
If wkSheet.Index > 5 Then 'only want worksheet index 6+
NewWkbookName = Trim(wkSheet.Name) & Format(Now, "-yyyy-mm-dd")

Application.StatusBar = "Copying Worksheet " & NewWkbookName

wkSheet.Copy

Set NewWkbook = ActiveWorkbook

On Error Resume Next
Application.DisplayAlerts = False
Kill CurWkbook.Path & Application.PathSeparator & NewWkbookName & ".xlsx"
Application.DisplayAlerts = True
On Error GoTo 0

Call NewWkbook.SaveAs(CurWkbook.Path & Application.PathSeparator & NewWkbookName & ".xlsx", xlWorkbookDefault)

NewWkbook.Close

End If
Next wkSheet

Application.ScreenUpdating = True
Application.StatusBar = False


End Sub


Paul

[wolf.stalker]

thanks...i give that a shot tomorrow when i get into the office.