Opening / Closing files using Excel Macro?

  • Thread starter Thread starter Wes_A
  • Start date Start date
W

Wes_A

Excel 2007 on XP Pro:
I am trying to write a a macro in Excel which will:
(1) open a workbook from another only if not already open
(2) close the workbook from another only if the other workbook is already
open.
Any ideas?
 
Try using this function. I'm sure I got it from here at some point.

Option Explicit
Function OpenWorkbook() As Excel.Workbook
Dim sFile As String
Dim ShortName As String

Set OpenWorkbook = Nothing

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Clear
.Title = "Please Select File to open"
If .Show = False Then Exit Function
sFile = .SelectedItems(1)
End With


ShortName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))



On Error Resume Next
Set OpenWorkbook = Workbooks(ShortName)
On Error Resume Next

If OpenWorkbook Is Nothing Then
Application.AutomationSecurity = msoAutomationSecurityLow
OpenWorkbook = Workbooks.Open(sFile)
Application.AutomationSecurity = msoAutomationSecurityByUI
End If

End Function
 
Back
Top