Check to see if Excel Workbook is open through VBA Code. And filter saveas types?

[Jhail83]

I am using a button on an access form to export a report using a custom Excel Template.

However, if the excel file is open when a user tries to save, it will throw an error, then nothing happens, but the excel template file stays open in the background, causing the user to have to manually close it through the task manager.

Also, currently you can save the workbook as anything.



For instance I can overwrite a word file, which is tacky. How can I get it to only save as .xls or .xlsx file types?

Option Compare Database
Public Sub btn_Export_Click()
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As DAO.Recordset
Dim fd As FileDialog
Dim vrtSelectedItem As Variant

Set objXL = CreateObject("Excel.Application")

'Opens the Template to Put records into.
Set xlWB = objXL.Workbooks.Open("C:\Users\xxxxx\Desktop\Test Database\2013 Excel Workbook Test")

'chooses which worksheet to put the data into.
Set xlWS = xlWB.Worksheets("xDetails")

'Selects which Query to retrieve the data from to export.
Set rst = CurrentDb.OpenRecordset("qry_ReportOutput")

'Create a FileDialog Object as a SaveAs Dialog Box
Set fd = Application.FileDialog(msoFileDialogSaveAs)
xlWS.Range("B3").CopyFromRecordset rst

With fd
   If .Show = -1 Then
      For Each vrtSelectedItem In .SelectedItems
            xlWB.SaveAs vrtSelectedItem
      Next vrtSelectedItem
   Else
   End If
End With

Set fd = Nothing
rst.Close
Set rst = Nothing
xlWB.Close

End Sub

Ideally, I want it to look like this.

With fd
     If .Show = -1 Then
           For Each vrtSelectedItem In .SelectedItems
                If blnWorkbookopen = 0 then
                       xlWB.SaveAs vrtSelectedItem (only as a workbook file type)
                else
                       msgbox "Please close the Workbook you are trying to save to."
                       Set fd = Nothing
                       rst.Close
                       Set rst = Nothing
                       xlWB.Close
                       Exit Sub
           Next vrtSelectedItem
              Else
              End If
End With

Thanks!

[June7]

I had this issue with a VBScript determining if an Access file is open. The only solution I could find was for the script to just kill any and all Access processes before running code to open database.

The FileDialog can be restricted to specific file types: http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[Jhail83]

How would I kill the process?

The Filters don't seem to work with (FileDialogSaveas) in Access..

Also for some reason, it freezes when you hit cancel or "x" out of the saveas filedialog box. I thought the if .show = -1 then... statement would handle that.

I tried:

If .Show = True Then
      For Each vrtSelectedItem In .SelectedItems
         xlWB.SaveAs vrtSelectedItem
      Next vrtSelectedItem
   Else
End If

and

If .Show = True Then
      For Each vrtSelectedItem In .SelectedItems
         xlWB.SaveAs vrtSelectedItem
      Next vrtSelectedItem
   Else
   xlWB.Close
   Set fd = Nothing
   rst.Close
   Set rst = Nothing
   Exit Sub
      End If

[June7]

Here is my VBScript:

' ProcessKillLocal.vbs
' Sample VBScript to kill a program
' Author Guy Thomas http://computerperformance.co.uk/
' Version 2.7 - December 2010
' ------------------------ -------------------------------'
Option Explicit
Dim objWMIService, objProcess, colProcess
Dim strComputer, strProcessKill
strComputer = "."
strProcessKill = "'msaccess.exe'"
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colProcess = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = " & strProcessKill )
For Each objProcess in colProcess
objProcess.Terminate()
Next
'WSCript.Echo "Just killed process " & strProcessKill & " on " & strComputer
'WScript.Quit
'End of WMI Example of a Kill Process

Dim objFSO, oShell
Set objFSO = CreateObject("Scripting.FileSystemObject")
'open the Access file
Set oShell = CreateObject("WScript.Shell")
oShell.Run """C:\Program Files\Microsoft Office\Office12\msaccess.exe"" ""\\servername\path\databasename.accdb"""

The X close and Cancel both work for me.

Sorry that filters don't.

[Jhail83]

Anyway I can use that to kill a specific file name of an application?

[June7]

No, that was the issue I had. Couldn't find any to determine what files are open. Just have to kill all Access processes. This is fine in my situation because I am the only user running that VBScript and I routinely close all my Access files each day anyway so the kill really isn't required. It's a 'just in case' measure.