WkS.Copy behaves buggy when put online

[CyberJar]

Hello,

My Excel 97/VBA is executed online from a web server.
Two things are wrong with my Save Routine:

1. I do a Worksheet.Copy, Save it, Close it, and Retun to ThisWorkbook.
The problem is after this executes there is a big empty Window named "Microsoft Excel [Read-only]" which has the focus. My guess is that a new window is opened to create a new Workbook, then copies the Worksheet in. After Saving & Closing, only the empty window is left. If I close this empty window, my Application stops. My Application is running in a separate Window.

2. In the debugger, I see that "Application.ScreenUpdating = False" never gets set to False. Odd!

Here is the Save Routine:

Private Sub cmdSave_Click()
Dim varFileName As Variant

On Error GoTo 300
ChDir "C:\"
Do
varFileName = Application.GetSaveAsFilename("My Report", "Excel File(*.xls),*.xls", , "Save My Report")
Loop Until varFileName <> False
If (VarType(varFileName) = vbString) Then
Application.ScreenUpdating = False
objMyWS.Copy
ActiveWorkbook.SaveAs FileName:=varFileName, FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close savechanges:=False
ThisWorkbook.Activate
Application.ScreenUpdating = True
End If
objMyWS.Activate
Exit Sub
300:
MsgBox Err.Number & " " & Err.Description
Resume Next
End Sub

The question is, How do I remove the big empty Window and stop it from receiving focus? I would like that window to go away after the Workbook is closed. Why does "ScreenUpdating = False" fail? I need the WkSheet to refresh itself after line deletion (in another section of code).

It works fine offline, but once online - all rules change.

TIA,CJ

[Garratt]

Are you opening another workbook?
If so have you thought about reading the other workbook(sheet) like a db without opening it?

[CyberJar]

Hello,

I am opening a new Workbook to save my current Worksheet into.

Could I do something tricky to get the Worksheet's data into the new Workbook? - like use it as a db? How would that work?

CJ

[Garratt]

You can use a closed workbook just like a database. You have to make a reference to MS ActiveX DataObjects (ver x.x).
info:
http://www.exceltip.com/show_tip/Import_and_Export_in_VBA/Use_a_closed_workbook_as_a_database_(ADO)_using_VBA_in_Microsoft_Excel/432.html

Try this search on Google for more examples: "Use a closed workbook"

Besides the ADO/SQL stuff, you can reference ranges and cells in a closed workbook directly: http://www.rondebruin.nl/ado.htm



I'm guessing you have Excel setup to open new workbooks in a different (new) Excel process (/window) which might also explain why the screenupdating is not working.

If that is the case you might try this (I'm guessing - never used it):
ActiveWindow.Application.ScreenUpdating = False