Getting Application.GetOpenFilename to open correct folder
ChDir won't work with UNC paths. But you can use a Windows API that will work
with either UNC paths or Mapped drives.
Here's an example I saved from a previous post:
And here's an example of that API call. It uses application.getopenfilename,
but you'll see how to use it.
Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Sub testme01()
Dim myFileName As Variant
Dim myCurFolder As String
Dim myNewFolder As String
Dim Wkbk as workbook
myCurFolder = CurDir
myNewFolder = "\\share\folder1\folder2"
On Error Resume Next
ChDirNet myNewFolder
If Err.Number < 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If
On Error GoTo 0
myFileName = Application.GetOpenFilename(filefilter:="Excel Files, *.xls")
ChDirNet myCurFolder
If myFileName = False Then
Exit Sub 'user hit cancel
End If
'do your stuff to open it and process it.
Set wkbk = workbooks.open(filename:=myfilename)
'....
End Sub
Don M. wrote:
I'm trying to write code that checks to see if a file of a specific name
exists in a folder. If it does then I just continue with my macro. If the
file is not there or is not named correctly I need the macro to open that
folder so I can rename it.
RyanH helped me a bunch yesterday getting code written that will look in the
folder and check for me, but I'm having trouble getting the
Application.GetOpenFilename line to open the correct folder to open. I dont
want the user to have to browse for the folder when the macro already knows
what folder the file is in. It seems to be using some kind of default path
that I can't figure out how to control. This morning it opened the Personal
folder when I tried it after a fresh reboot.
This file name check occurs on nine different folders for nine different
regions, Region1 through Region9. Right now the code accurately identifies
the misnamed file, an Open window pops up but I have to browse to the correct
folder. I can then double-click on the file and the file is renamed
correctly. I like this double-click ability as it keeps the users input to a
minimum. The problem I'm having is getting the correct folder to open up. I
have to be able to specify which folder opens when the
Application.GetOpenFilename line is executed.
In Cell(5,3) on worksheet "Run Report" is the number 744560
In Cell(5,5) on worksheet "Run Report" is the number 744803
Here's the code:
'This is the name of the file I'm looking for
'and what it should be called everytime
Const BulkReportFileName = "BulkFulfillmentReport.xls"
Region1BulkID = Worksheets("Run Report").Cells(5, 3) €˜744560
Region1BulkPrefix = Left(Worksheets("Run Report").Cells(5, 3), 3) €˜744
Region2BulkID = Worksheets("Run Report").Cells(5, 5) '744803
Region2BulkPrefix = Left(Worksheets("Run Report").Cells(5, 5), 3) '744
Region1:
€˜this is the Path the file should be in for Region1
BulkReportPath = "\\fileserver\Data\Global\Taskorder _
Documents\000\" & Region1BulkPrefix & "\" & Region1BulkID & "\"
€˜check to see if the Bulk Report is named correctly
If dir(BulkReportPath & BulkReportFileName) = "" Then
MsgBox "The Bulk Report is misnamed" & vbNewLine & _
vbNewLine & "Double click the Bulk Report in the" & _
vbNewLine & "next window and it will be renamed", _
vbInformation, "The Bulk Report is misnamed"
Line1:
OldBulkReportFileName = Application._
GetOpenFilename("(*bulk*.xls), *bulk*.xls")
€˜change the name of the file
If OldBulkReportFileName < False Then
Name OldBulkReportFileName As BulkReportPath _
& BulkReportFileName
Else
MsgBox "Click OK and select the misnamed Bulk _
Report.", vbInformation, "You did not select a workbook."
GoTo Line1:
End If
End If
Region2:
€˜this is the Path the file should be in for Region2
BulkReportPath = "\\fileserver\Data\Global\Taskorder_
Documents\000\" & Region2BulkPrefix & "\" & Region2BulkID & "\"
€˜check to see if the Bulk Report is named correctly
If dir(BulkReportPath & BulkReportFileName) = "" Then
MsgBox "The Bulk Report is misnamed" & vbNewLine & _
vbNewLine & "Double click the Bulk Report in the" & _
vbNewLine & "next window and it will be renamed", _
vbInformation, "The Bulk Report is misnamed"
Line2:
OldBulkReportFileName = Application._
GetOpenFilename("(*bulk*.xls), *bulk*.xls")
€˜change the name of the file
If OldBulkReportFileName < False Then
Name OldBulkReportFileName As BulkReportPath _
& BulkReportFileName
Else
MsgBox "Click OK and select the misnamed Bulk _
Report.", vbInformation, "You did not select a workbook."
GoTo Line2:
End If
End If
As you can see, this is nearly identical from Region2 and will be for all
nine regions once this works. The problem is that when I execute the
Application.GetOpenFilename line the folder that gets opened is not the
folder with the incorrectly named file. I don't want the user to have to
search for this folder, I want the macro to open it.
Does anyone know why this is happening and how to fix it? I'm so close I can
taste it. This path thing is all that's in my way now thanks to your help.
Don
--
Dave Peterson
|