-
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
-
Haven't done it but I imagine you should be able to check with something like
Code:
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
-
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
-
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?
-
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