Getting Application.GetOpenFilename to open correct folder

[Don M.]

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 don’t
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

 

[JP]

Don,

If you want the GetOpenFileName Method to start in a specific folder,
use the ChDir Function to change to that folder first.

Dim strFileN As String
ChDir "C:\My Folder\"
strFileN = Application.GetOpenFilename("Microsoft Office Excel Files
(*.xls), *.xls", , "Choose File To Open")

The dialog box will use C:\My Folder\ as the base folder.

HTH,
JP

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 don’t
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.

- snip -

 

[Per Jessen]

Hi Don

Use ChDir to change to the desired folder.

This loop should check all 9 files if the BulkID are found in
cell(5,3) - (5,5) - (5,7) and so on...

Sub test()
'This is the name of the file I'm looking for
'and what it should be called everytime
Const BulkReportFileName = "BulkFulfillmentReport.xls"

Set RegionBulkIDCell = Worksheets("Run Report").Cells(5, 3)
off = 0

For region = 1 To 9
'this is the Path the file should be in
RegionBulkID = RegionBulkIDCell.Offset(0, off).Value
RegionBulkPrefix = Left(RegionBulkID, 3)
BulkReportPath = "\\fileserver\Data\Global\Taskorder_Documents
\000\" & RegionBulkPrefix & "\" & RegionBulkID & "\"

'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:
ChDir (BulkReportPath)
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
off = off + 2
Next
End Sub

Regards,
Per

 

[Don M.]

JP, I tried the ChDir line and the folder that opens up is still my Personal
folder in Documents and Settings.

I sure thought that would do the trick. This is what I'm trying to use:

ChDir "\\fileserver\Data\Global\TaskorderDocuments\000\" & Region1BulkPrefix
& "\" & Region1BulkID & "\"

OldBulkReportFileName = Application.GetOpenFilename("Microsoft Office
Excel Files(*.xls), *.xls", , "Choose File To Open")

I tried using a local folder, "C:\Windows" and it works fine. But, when I
put in my network folder it won't work. In fact, when I try to open the
network folder, the folder that actually opens up is the "C:\Windows" folder
that opened before, so somewhere that path is being saved as a default.

Don

 

[Don M.]

Thanks Per, I get the same problem as with JP's suggestion. The folder that
gets opened up is not BulkReportPath.

I'm at a loss as to why this happens.

Don

 

[Per Jessen]

Hi Don

You have to change to the desired drive too, as using ChDir only
doesn't change the default drive

ChDrive("\\fileserver")
ChDir ...

Regards,
Per

 

[Dave Peterson]

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.]

Thanks again Per, unfortunately the ChDrive is crashing on me. I get a
run-time error 5: message with Invalid procedure call or argument.

I'm trying to use

ChDrive ("\\fileserver\") 'Gives a "run-time error '5':" message
ChDir (BulkReportPath)
OldBulkReportFileName = Application.GetOpenFilename("(*bulk*.xls),
*bulk*.xls")

but it doesn't get past the ChDrive line without the error.

Don

 

[Don M.]

The book says that Drive has to be a string expression representing an
existing drive. Does that mean that \\fileserver has to be a mapped network
drive?

 

[Per Jessen]

Don,

Look at the post from Dave Peterson. It seems that you need to
incorporate his solution into your macro.

Best regards,
Per

 

[Don M.]

THANK YOU! THANK YOU! THANK YOU! THANK YOU! THANK YOU!

That code worked great. After some time spent figuring out how to combine
your code with two other suggestions I got it all works flawlessly. Barring
yet another unforseen glitch. But this part works beautifully!

Thank you!

Don