Opening / Closing files using Excel Macro?



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
Any ideas?





Barb Reinhardt

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
.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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question