worksheet SaveAs WK4 in background

[Edward]

I have an excel workbook with multiple sheets. I want to save one of
the sheets as a WK4 in the background. I amusing the following code:

Sheets("sheetx").SaveAs Filename:= "C:\xxx.wk4", FileFormat:=xlWK4,
CreateBackup:=False

The problem is that although the excel appears to still be open with
all of the tabs, it has been renamed at the top to the WK4 filename.
If I close the excel and open the WK4 then it opens as WK4 with only
the sheet that I saved. My problem is that users may have an issue
with the excel being "lost" while the supposed WK4 file looks like
excel.

SaveCopyAs does not seem to help because that cannot be done against a
single sheet and cannot convert to WK4.

Any ideas about how I could do a SaveAs only in the background without
renaming the excel?

 

[Joel]

'create newwork book with copy of sheetx
Sheets("sheet1").Copy
ActiveWorkbook.SaveAs Filename:="C:\temp\abc.wk4", FileFormat:=xlWK4, _
CreateBackup:=False
ActiveWorkbook.Close

 

[Edward]

'create newwork book with copy of sheetx
Sheets("sheet1").Copy
ActiveWorkbook.SaveAs Filename:="C:\temp\abc.wk4", FileFormat:=xlWK4, _
  CreateBackup:=False
ActiveWorkbook.Close









- Show quoted text -

This makes new problems. I do not want to close the original
document, just export one of its sheets in the background.

 

[Joel]

I don't think it will close the original. Th ecopy will create a new
workbook and become the activeworksheet. The original should remain open.

 

[Edward]

I don't think it will close the original.  Th ecopy will create a new
workbook and become the activeworksheet.  The original should remain open.






- Show quoted text -

Have you tried it? It closes it for me. I have Excel 2002
(10.6823.6817) SP3 and VB 6.3.

 

[dan dungan]

It didn't close it for me in Excel 2000 SP-3

 

[Joel]

Ed: Do you have any workbook events that may be changing the focus to a
different workbook? Try this instead

'create newwork book with copy of sheetx
Sheets("sheet1").Copy
set newbk = activeworkbook
newbk.SaveAs Filename:="C:\temp\abc.wk4", FileFormat:=xlWK4, _
CreateBackup:=False
newbk.Close

 

[Edward]

It didn't close it for me in Excel 2000 SP-3

Not sure what happened before but it works now. Thanks.