Consulting

Results 1 to 1 of 1

Thread: File Browse InitialFileName not working on one Excel 2007 machine only

  1. #1
    VBAX Expert
    Joined
    Sep 2010
    Posts
    604
    Location

    File Browse InitialFileName not working on one Excel 2007 machine only

    This code browses for pdf files where the excels active cell value is a partial file name.

    It works fine on 2003 and 2010 but on one 2007 machine at work, the dialog lists all the files (the InitialFileName is ignored)

    * I'm 90% sure I've run this on a 2007 machine before without this issue.

    Has anyone ever seen this happen before?

    Edit: Using a msgbox I've verified that the PartNum Variable contains the activecell value
    [vba]
    Sub Browse_Initial_FileName_is_Activecell()
    Dim MyinitialFilename As String
    Dim MyFullPath As String
    Dim MyFileDialog As FileDialog 'As Object
    Dim PartNum
    Dim FSO As Object, StrRootDir As String
    On Error GoTo errorHandler
    Set FSO = CreateObject("Scripting.FileSystemObject")
    StrRootDir = FSO.GetDriveName(ThisWorkbook.Path)
    Set FSO = Nothing
    ChDrive StrRootDir
    ChDir StrRootDir & "\Blue Prints\"
    PartNum = Replace(UCase(Trim(ActiveCell.EntireRow.Cells(7).Value)), "'", "")
    MyFullPath = StrRootDir & "\Blue Prints\*" & PartNum & "*"
    Set MyFileDialog = Application.FileDialog(msoFileDialogOpen)

    With MyFileDialog
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "PDF Files", "*.pdf"
    .InitialFileName = MyFullPath
    .InitialView = msoFileDialogViewDetails
    .Title = MyFullPath

    If .Show = True Then
    ActiveWorkbook.FollowHyperlink Address:=.SelectedItems(1)
    End If

    End With
    ChDir ThisWorkbook.Path
    Exit Sub
    errorHandler:
    Select Case Err.Number
    Case 76
    MsgBox Err.Description & ", Error# " & Err.Number _
    & " - Make sure the Folder with the name Blue Prints is in the root directory of This Workbook."
    Case Else
    MsgBox Err.Description & ",Error# " & Err.Number & ", Please report to Frank"
    End Select
    End Sub

    [/vba]
    Last edited by frank_m; 04-08-2012 at 11:29 AM. Reason: Removed Application.EnableCancelKey = xlDisabled to simplify reading the code.

Posting Permissions

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