-
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
-
Forum Rules