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" wrote:

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


On 23 Okt., 15:52, 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