Saving an Excel Spreadsheet that is already open

[jonny_clark]

I am working on a database program that outputs reports via EXcel. Presently I am using a common dialog to retrieve the filename the user wants to save the file as.

If the user chooses a filename that is already open in excel then I get an error. I need to be able to check if the file is already open (in excel) and prompt the user to close it. can anyone help

thanks

Jon

[JHausmann]

Haven't done it but I imagine you should be able to check with something like

for i=1 to Excel.Workbooks.Count
 if Trim(Ucase(workbooks.item(i).name))= Trim(UCase(users_filename)) then
    Excel.Workbooks(i).Close ' should ask for name
 end if
next i

'in the above example "excel" is your open instance of excel.application

Edited by JHausmann on 03-08-2000 at 05:36 PM

[scottr]

I had a similar a problem. The way I handled it was to use a function to see if excel was running. If it was then I access the excel object and have excel close down. when it closes down excel will ask the user to save the document so it does it for you.

Here is the function

Function fIsAppRunning(ByVal strAppName As String, _
Optional fActivate As Boolean) As Boolean
Dim lngH As Long, strClassName As String
Dim lngX As Long, lngTmp As Long
Const WM_USER = 1024
On Local Error GoTo fIsAppRunning_Err
fIsAppRunning = False
Select Case LCase$(strAppName)
Case "excel": strClassName = "XLMain"
Case "word": strClassName = "OpusApp"
Case "access": strClassName = "OMain"
Case "powerpoint95": strClassName = "PP7FrameClass"
Case "powerpoint97": strClassName = "PP97FrameClass"
Case "notepad": strClassName = "NOTEPAD"
Case "paintbrush": strClassName = "pbParent"
Case "wordpad": strClassName = "WordPadClass"
Case Else: strClassName = ""
End Select

If strClassName = "" Then
lngH = apiFindWindow(vbNullString, strAppName)
Else
lngH = apiFindWindow(strClassName, vbNullString)
End If
If lngH <> 0 Then
apiSendMessage lngH, WM_USER + 18, 0, 0
lngX = apiIsIconic(lngH)
If lngX <> 0 Then
lngTmp = apiShowWindow(lngH, SW_SHOWNORMAL)
End If
If fActivate Then
lngTmp = apiSetForegroundWindow(lngH)
End If
fIsAppRunning = True
End If
fIsAppRunning_Exit:
Exit Function
fIsAppRunning_Err:
fIsAppRunning = False
Resume fIsAppRunning_Exit
End Function

and here is how to call it


If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
MsgBox "Excel must be closed in order to export. Please follow the instructions, if you have made changes to the file, you will now be prompted to save them", _
vbOKOnly, "Excel File Export"
objXL.Application.Visible = True
objXL.Application.Quit
Exit Sub
End If

It works pretty well and avoids all the nasty pitfalls.

Regards,

Scott

[JHausmann]

The only problem with shutting down Excel is that you'll have to open it up again. Why have the application re-load itself for every spreadsheet?

[scottr]

I found that when excel was open it gave the system alot of problems. For my app it was one spreadsheet at a time so closing it was no big deal. If you have mulitple sheets that could be more difficult