testing for completion of selectedsheets.copy (or a better way)

[innov]

Hello all!

I use the code below to "export" selected sheets from a workbook as excel, csv, txt, etc. (on the saveas fileformat differs).

Just recently, I've begun to have issues with the activewindow focus. I believe it's speed-related within a Novell server environment.

The problem is, sporadically (but not always), after the selectedsheets.copy command (below), the activeworkbook is still the SOURCE (i.e. ThisWorkbook). USUALLY, the activeworkbook is the NEW workbook created by the copy.

(1) Is their a way to test that the .copy is done? (I'm assuming it's an asynchronus task due to this problem).

I thought about looping (with a DoEvents or Sleep API) until the ActiveWorkbook does NOT = Thisworkbook.

(2) Is their a better way to get accomplish my goal?

THANKS !

Innov

'----------------------------------------------------------------------------
'Copy selected sheets to a temporary wkbk and copy values to remove formulas.
'Then break any remaining links (pics, etc.)
'The temp wkbk will be "saved as" excel, cvs, txt and/or html
'----------------------------------------------------------------------------
  ActiveWindow.SelectedSheets.Copy

'HERE IS WHERE THE ACTIVEWORKBOOK ISN'T ALWAYS THE NEWLY CREATED WORKBOOK.
  For Each ws In Worksheets
    Sheets(ws.Name).Activate
    Sheets(ws.Name).Unprotect
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
              SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
  Next ws
  
  Call Break_Links
  
'-----------------------------------------------------------------------------

 

[BrainB]

FWIW a couple of ideas.

1. I think a simple 'DoEvents' line after the Copy will do what you want.

2. I have successfully added delays in the code to overcome such problems. eg. 2 seconds :-
Application.Wait Now + TimeValue("00:00:02")

3. Using Application.Caption with On Error may perhaps be used as you suggest in your message.

4. Perhaps saving to a temporary named book and testing for this ??

    ' UNTESTED !!
    ActiveWindow.SelectedSheets.Copy _
        before:=Workbooks("TempBook.xls").Worksheets(1)
    On Error GoTo ReTry:
ReTry:
    Workbooks("TempBook.xls").Activate
    On Error GoTo 0

Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[innov]

Thanks, BrainB.

I'll give it a try with a slight variation.

Since:
(a) I'd like to avoid having to clean-up the temporary workbook when done with it; and
(b) I don't know the name o the temporary workbook (i.e. book2, book3,...)

I'm gonna capture the Workbooks.Count BEFORE the .COPY and then loop until the Workbooks.count = prev count + 1.

Something like this:

x = Workbooks.Count
Selectsheets.Copy

Do until y = x + 1
   doevents
   y = workbooks.count
loop

--- rest of code ---

Any additional thought, please let me know.

Thanks!
Innov