Makro: Workbooks.Open - Making Excel File Type irrelevant

[Gnijnan]

Hello everyone,

I've got a macro which opens an excel workbook. This workbook is sometimes saved as xls and sometimes as xlsx. Therefore, as you can see below, i didn't specify a file type. On my laptop this works. The workbook gets opened, regardless of the filetype.
I send the macro and the folder structure on which it relies to a colleague, but when she runs the macro she gets an error that the file can't be found. If I add

& ".xls"

to the macro the file gets opened. But then it doesn't work for xlsx files anymore. I'm 99% sure that the filetype is the error, not something else, like a wrong folder hierarchy.

Does anyone have a solution to correct my macro or that it, saypens the xlsx if it doesn't find an xls, etc?
Or does anyone have an idea where the error on my colleague's side is? I've got windows 7, she's got windows 8

My code:

parentfolder = Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1)    
Workbooks.Open Filename:= _ 
      parentfolder & "\Reporting\Cognos\Order Income\" & Range("G2") & Range("H2") & " OI (EUR CONS) " & Range("F2")

 

[Darren Bartrup]

Hi and welcome to the board.

A couple of ways I can think of....

First is to ask the user to point to the relevant file:

' To Use    : vFile = GetFile()'           : vFile = GetFile("S:\Reporting\Cognos\Order Income\")
'---------------------------------------------------------------------------------------
Function GetFile(Optional startFolder As Variant = -1) As Variant
    Dim fle As FileDialog
    Dim vItem As Variant
    Set fle = Application.FileDialog(msoFileDialogFilePicker)
    With fle
        .Title = "Select a File"
        .AllowMultiSelect = False
        .Filters.Add "Order Income Excel Files", "*.xls; *.xlsx", 1
        If startFolder = -1 Then
            .InitialFileName = Application.DefaultFilePath
        Else
            If Right(startFolder, 1) <> "\" Then
                .InitialFileName = startFolder & "\"
            Else
                .InitialFileName = startFolder
            End If
        End If
        If .Show <> -1 Then GoTo NextCode
        vItem = .SelectedItems(1)
    End With
NextCode:
    GetFile = vItem
    Set fle = Nothing
End Function


Public Sub test()


    Dim vfile As Variant
    
    vfile = GetFile("S:\Reporting\Cognos\Order Income\")


End Sub

The other way is to check if the file exists first:

'----------------------------------------------------------------------
' FileExists
'
'   Returns True if specified file exists.
'----------------------------------------------------------------------
Public Function FileExists(ByVal FileName As String) As Boolean
    Dim FSO As Scripting.FileSystemObject
    Set FSO = New Scripting.FileSystemObject
    FileExists = FSO.FileExists(FileName)
End Function


Sub Test()


    MsgBox FileExists("S:\Bartrup-CookD\_Code Library\mdl_File_Functions.bas"), vbOKOnly


End Sub

If it returns TRUE - then go ahead an open the file, if it returns FALSE then open the other file type.

 

[Gnijnan]

Awesome, these sound like very good ideas. Thank you, you helped me very much.

 

[RoryA]

You could also use Dir and pass ".xls?" as the extension to get the actual file name back.