Large Amount of Info on Clipboard

[Bill Martin]

Using Excel 2003 I recorded a macro that opens a second file, copies a large
amount of data from it that gets pasted into the original file, then closes this
second file. I've used this code as a base to do what I actually want.

My problem is when the code closes the second file with:
ActiveWindow.Close Savechanges:=False

At that point Excel comes to a halt with a message window telling me that
there's a large amount of data on the clipboard, and do I want to save it? How
do I get VBA to tell it 'no' without this message window coming up?

Thanks.

Bill

 

[Dave Peterson]

Try:

application.cutcopymode = false
activewindow.close savechanges:=false

(I'd use a variable for the workbook. I don't trust the windows collection.)

application.cutcopymode = false
activeworkbook.close savechanges:=false
'or
wkbk.close savechanges:=false

 

[Bill Martin]

Thanks Dave. That works for me.

Bill
-------------------

 

[MILESCFA]

Using Excel 2003 ...
Bill

I had a THREE problems (one similar) and this site pops up near the top
of a google search so I hope I can help people with both. (please
forgive my "redundancy" as I try to make the search engines catch
everything, because the combined problems took me a long time to solve
with a lot of web searches, and think the solutions are very
worthwhile)

I am downloading stock quote info from thinkorswim to excel using dde
formulas. I do this using macro express and it took a long time to solve
the timing issue in macro express so it waited for excel to download /
update the dde data before moving on.

Excel VBA also wants to move on before the dde data is updated,
creating errors and "NAs". The excel wait command stopped the CPU
calculation, but the solution is not included here. If you're having the
same issue, postme here and hopefully I'll see it (I am not a frequent
visitor here).

I create "alerts" based on values and use conditional formating to to
highlight them (like large dollar straddles) in one sheet. Then I copy
this data to a different "database" sheet.

After this, I want to remove conditional formating formulas from the
excel "database" spreadsheet because the conditional format formulas use
a large / huge amount of memory that can reult in my excel crashing /
stops worrking.

My second problem (dde NOT updating quickly was the first): there is NO
easy way to convert conditional formating formulas to values without
losing the formating.

The only way I found was to copy the spreadsheet data to Microsoft Word
application, which retains the formating but does not keep the formulas
(if you have Excel, you have Word too, don't you!).

Then I copy this data back to the DB in excel. Thus I end up with the
conditioonal formating BUT NOT the conditional formulas. However, this
generates the problem I have that is similar to the one on this board:

I'm running an Excel VBA macro that has to paste to Word, but after
Word VBA copies the data back to Excel, Word has a large amount of data
on the cipboard (or large amount of data in the clipboard?). My solution
was fairly simple - I cut the original Word data that is pasted back to
excel, then I type a little text, select it all, then copy it. This copy
clears aout the previous large amount of data in word / clipboard memory
so I don't get the warning message.

Here's the VBA code:


'in declarations:

Dim appWD as word.application '(note: in VBA under tools-references
you have to have excel AND WORD object libraries selected)

'copy data FROM EXCEL


Sheets("hiOIactive").Select

Range("A11").Select


Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Select

Selection.Copy


'***************************

****************************

**************
Set appWD = CreateObject("Word.Application") 'create word object
appWD.Visible = True 'show word
appWD.Documents.Add 'DocumentType:=wdNewBlankDocument 'new
document BUT did NOT work for me


appWD.Selection.PasteExcelTable False, False, False 'paste the
Excel

"coonditional format"
Application.CutCopyMode = False 'DOES NOT WORK!


appWD.Selection.WholeStory ' (3) select the same data
appWD.Selection.Copy 'and copy it to clipboard in an HTML format
(default in word? it is in mine)

' appWD.WindowState = wdWindowStateMinimize ' have to keep it open
until paste back to XL


'paste back to XL (gets rid of conditional formating, which TAKES
ENORMOUS MEMORY


Sheets("DBwkly").Select

Range("A65000").End(xlUp).Offset(1, 1).Select

ActiveSheet.PasteSpecial Format:="HTML", Link:=False,
DisplayAsIcon:=False
'this is NOT a normal paste option in Excel, but it is available for
paste from word


'Set appWD = GetObject("Word.Application") 'create word object = DID
NOT WORK

'appWD.WindowState = wdWindowStateNormal 'SOMETIMES CAUSED ISSUES?


appWD.Visible = True


appWD.Selection.WholeStory ' (3) select the same data

appWD.Selection.Copy ' and copy it to clipboard in an HTML
format
appWD.Selection.Cut

'DELETE / CUT THE LARGE AMOUNT OF DATA IN THE CLIPBOARD!!

appWD.Selection.TypeText Text:="copyNOTHING"
'small "bit of nothing" to be copied


appWD.Selection.WholeStory 'copy "almost NOTHING"

appWD.Selection.Copy ' and py it to clipboard in an HTML
format

appWD.Selection.Cut 'NOW DELETE THE "NOTHING"


appWD.Selection.PasteAndFormat (wdPasteDefault)
'THIS PASTE loads CLIPBOARD with almost nothing so it will not ask
about saving clipboard info




appWD.Application.DisplayAlerts = False 'does NOT work


appWD.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges

appWD.Quit

application.DisplayAlerts = True 'just in case?


--
MILESCFA
------------------------------------------------------------------------
MILESCFA's Profile: http://www.thecodecage.com/forumz/member.php?u=2329
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=181785

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: (e-mail address removed) ---