frank_m
02-21-2012, 02:51 AM
When I run this code to browse for a file in Win XP, the folder remains locked after I'm finshed. (regardless of whether or not I open a file, or just dismiss the folder browse)
My folder is directly below the C directory, but in XP Home normal setup, folders can be added and renamed freely. - If I close and re-open only the Excel file that I used to run the code, the folder access is still locked, but if I shut down the entire instance of Excel, and reopen, it's back to normal..
Is there any code that I can run to free things up, or is this just an XP(Excel 2003) quirk) ?
Sub Browse_PNs_contained_within_Activecell()
Dim MyinitialFilename As String, MyFullPath As String
Dim MyFileDialog As FileDialog, strFileName As String, PartNum As Variant
Dim FSO As Object, StrRootDir As String, a() As String
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)), "'", "")'Col7 value in activerow
a() = Split(PartNum, "-")
If Len(a(0)) < 4 Then 'cocatenate before and after dash only if the before is less than 4 numbers
strFileName = UCase(a(0) & "-" & a(1))
Else
strFileName = UCase(a(0))
End If
MyFullPath = StrRootDir & "\Blue Prints\*" & strFileName & "*"
Set MyFileDialog = Application.FileDialog(msoFileDialogOpen)
With MyFileDialog
.AllowMultiSelect = False
.Filters.Clear
.InitialView = msoFileDialogViewDetails
.Title = MyDir & strFileName
.InitialFileName = MyFullPath
If .Show = True Then
ActiveWorkbook.FollowHyperlink Address:=.SelectedItems(1)
End If
End With
End Sub
My folder is directly below the C directory, but in XP Home normal setup, folders can be added and renamed freely. - If I close and re-open only the Excel file that I used to run the code, the folder access is still locked, but if I shut down the entire instance of Excel, and reopen, it's back to normal..
Is there any code that I can run to free things up, or is this just an XP(Excel 2003) quirk) ?
Sub Browse_PNs_contained_within_Activecell()
Dim MyinitialFilename As String, MyFullPath As String
Dim MyFileDialog As FileDialog, strFileName As String, PartNum As Variant
Dim FSO As Object, StrRootDir As String, a() As String
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)), "'", "")'Col7 value in activerow
a() = Split(PartNum, "-")
If Len(a(0)) < 4 Then 'cocatenate before and after dash only if the before is less than 4 numbers
strFileName = UCase(a(0) & "-" & a(1))
Else
strFileName = UCase(a(0))
End If
MyFullPath = StrRootDir & "\Blue Prints\*" & strFileName & "*"
Set MyFileDialog = Application.FileDialog(msoFileDialogOpen)
With MyFileDialog
.AllowMultiSelect = False
.Filters.Clear
.InitialView = msoFileDialogViewDetails
.Title = MyDir & strFileName
.InitialFileName = MyFullPath
If .Show = True Then
ActiveWorkbook.FollowHyperlink Address:=.SelectedItems(1)
End If
End With
End Sub