I have the following macro that is linked to a button.
There is also a function that is called to check to see if the file is already open.
These macros & functions work properly.
Basically, the Case statements check the extension of the file to be opened and then calls the correct macro to open that particular file.
I have shown the macro that is called by the Case statement that has the extension .xls . . . Excel
I want to keep these Case statements as I always want Excel files opened in new instances of Excel.
What I need is when the existing case statements do not find an extension that is listed, I want code added to this macro that can open any type of file, such as files with other types of extensions or without extensions.
My son suggested that I should try to get a macro that could open the file using the RUN command found under the Start Button. Sounds good to me.
Is there anyone that can give me a hand. I'll be happy to answer any questions.
Thanks in Advance as everyone has always been a great help to me.
Sub ExcelOrWordFile()
Select Case ActiveCell.Column
Dim cl As Range
Case 1, 3, 5, 7, 9, Is > 11
MsgBox "Please select the name of a file you want to open."
End
End Select
'THIS GETS THE PATH & FILE NAME & EXT OF THE FILE I WANT OPENED
Set cl = ActiveCell.Offset(0, -1)
Select Case LCase(Right(cl.Value, 3))
Case "xls", "xla"
Call NewExcelWithWorkbook 'THIS CALLS THE OPEN EXCEL MACRO
Case "doc", "wpd"
Call NewWordWithDocument
Case "exe"
Call NewShortcut
Case Else
MsgBox "Please select the name of a file you want to open.", vbInformation
End Select
End Sub
Sub NewExcelWithWorkbook()
Dim oXL As Object 'This is needed to open a new instance of Excel.
'Without it, the file is only opened as a new Window
Dim OpenFileName '<-this isn't used
Dim testFileFind As String
Dim oWB As Workbook
Dim cl As Range
'This reads the cell 1 column to the Left so the path & file name can be read
Set cl = ActiveCell.Offset(0, -1)
'The following tests for a blank cell and ends processing
'It is needed because dir() function will not work with a blank.
If Len(Trim(cl)) = 0 Then
MsgBox "You have not entered a Path and File name."
End
End If
'The following tests for the existance of the file
testFileFind = Dir(cl)
'If the file is not found there will be nothing in the variable and processing ends.
If Len(testFileFind) = 0 Then
MsgBox "Invalid selection." & Chr(13) & _
"Filename " & cl.Value & " not found"
End
End If
'THIS TESTS TO SEE IF DOCUMENT ALREADY OPEN
If FileAlreadyOpen(cl.Value) = True Then
MsgBox "File is already open"
End
Else
End If
'THIS LINE OF CODE OPENS THE NEW INSTANCE OF EXCEL.
Set oXL = CreateObject("Excel.Application")
'THIS LINE OF CODE MAKES THE NEW INSTANCE OF EXCEL VISIBLE.
oXL.Visible = True
Set oWB = oXL.Workbooks.Open(cl)
End Sub
Bookmarks