Results 1 to 11 of 11

Need To Open Different Types of Files From My Single Macro

Threaded View

Launchnet Need To Open Different Types... 05-05-2009, 01:01 AM
antoka05 Re: Need To Open Different... 05-05-2009, 02:25 AM
Launchnet Re: Need To Open Different... 05-05-2009, 12:32 PM
Leith Ross Re: Need To Open Different... 05-05-2009, 01:00 PM
Launchnet Re: Need To Open Different... 05-05-2009, 03:16 PM
  1. #1
    Valued Forum Contributor
    Join Date
    09-04-2007
    Location
    Ontario, Ca
    Posts
    624

    Need To Open Different Types of Files From My Single Macro

    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
    Last edited by Launchnet; 05-10-2009 at 06:16 PM. Reason: Better description
    Thanks for helping . . .
    Matt @ Launchnet

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1