Opening / Closing files using Excel Macro?


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?
 
Ad

Advertisements

Ad

Advertisements

B

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

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

Top