Excel extension issue(s)

[cstimart]

I have many files with macros that reference other excel files and now that I have been converted to Office 2007, the extensions are changing.

Is there away to call another file w/o having the specifically reference .xls, .xlsx, or .xlsm so that it will access the file no matter the specific excel extension?

 

[mole999]

a common problem every where

have you thought of using xlsb instead of xlsm and xlsx, and upgrade any xls files at the same time

could save most of your future issue

 

[Norie]

How are you 'calling' the workbooks?

 

[cstimart]

For the issue I'm dealing with now, I have a spreadsheet which prompts the user to select the Year and Quarter that they want to see. Then, based on that answer, I have a bunch of variables that fill with the data from the various year/quarter sources....for example.

records(0) = "='M:\My Documents\" & y3ar & " Project\Study\BLAB\" & y3ar & "\" & quarter & "\[" & y3ar & " Project 2 - Detailed Analysis " & quarter & ".xls]Image Admin Time'!$G$27"

then, I also provide a hyperlink to allow the user to open that source sheet, too....but that does not include the Cell reference.

 

[Rasm]

Google

Application.FileDialog(msoFileDialogFolderPicker)

You get all kinds of hits - so take your pick

 

[cstimart]

Google

Application.FileDialog(msoFileDialogFolderPicker)

You get all kinds of hits - so take your pick

That makes my head hurt...I was hoping there was an easy solution like using a *.xl* or something.

 

[Rasm]

OK - maybe this helps - so create a Command button 'CmdBrowse' on your userform.

Public Sub CmdBrowse_Click()
    Dim strPath As String
    strPath = "c:\"
    Call GetFolder(strPath)
    Dim FileSpec As String
    Dim FileArray() As Variant
    FileSpec = strPath & "\*.xls"
    'FileSpec = TBpath.Text & "\*.xlsx"
    Call GetFileList(FileSpec, FileArray)
    Stop' the filearray contain the filenames - not sure if you want then all or what
    Erase FileArray
End Sub
 
Function GetFolder(strPath As String) As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function
 
Function GetFileList(FileSpec As String, FileArray() As Variant) As Variant
    Dim FileCount As Integer
    Dim FileName As String
    On Error GoTo NoFilesFound
    FileCount = 0
    FileName = Dir(FileSpec)
    If FileName = "" Then GoTo NoFilesFound
'   Loop until no more matching files are found
    Do While FileName <> ""
        FileCount = FileCount + 1
        ReDim Preserve FileArray(1 To FileCount)
        FileArray(FileCount) = FileName
        FileName = Dir()
    Loop
    GetFileList = FileArray
    Exit Function
'   Error handler
NoFilesFound:
    GetFileList = False
End Function

 

[Jon von der Heyden]

Use the Dir function, for example:

Public Sub Demo()
    Const strPath As String = "C:\"
    Const strFileName As String = "Test"
    Dim strFileAndPath As String
    
    strFileAndPath = strPath & Dir$(strPath & strFileName & ".xl*")
    
    If Len(strFileAndPath) > Len(strPath) Then
        Workbooks.Open Filename:=strFileAndPath
    End If
End Sub

Note, if the file exists for more than one of the formats then it will pick up the first file only, the order being:

1. xls
2. xlsm
3. xlsx

 

[Rasm]

ok - try something like this - if you want multiselect=true then look at the help

 FilesToOpen = Application.GetOpenFilename("Excel Files (*.xl*)," & "*.xl*", 1, "Select Excel File", "Open", MultiSelect:=False)   
    If FilesToOpen <> False Then MsgBox "Files selected  " & FilesToOpen

 

[cstimart]

Use the Dir function, for example:

Public Sub Demo()
    Const strPath As String = "C:\"
    Const strFileName As String = "Test"
    Dim strFileAndPath As String
    
    strFileAndPath = strPath & Dir$(strPath & strFileName & ".xl*")
    
    If Len(strFileAndPath) > Len(strPath) Then
        Workbooks.Open Filename:=strFileAndPath
    End If
End Sub

Note, if the file exists for more than one of the formats then it will pick up the first file only, the order being:

1. xls
2. xlsm
3. xlsx

Unless, I am missing something, won't Len(strFileAndPath) > Len(strPath) be true 100% of the time?