Check for file in folder, if not there, open folder to rename file

[Don M.]

I have a macro that looks for a specifically named file in a folder on our
network that may have several files in it. The point of this macro is to
import the contents of the file into another spreadsheet. I can't seem to get
people to realize that this file needs to be named the same thing each week
so my macro can find it. As a result, when my macro goes to import the file

\\000\745\745670\bulkfulfillmentreport.xls

and can't find it, the macro stops in error and I have to rename the file
and restart the macro from the top. I have to do this to 9 different files in
9 different folders and they all have to be named bulkfulfillmentreport.xls

I'd like to figure out a better way to deal with this. I'm thinking some
sort of code that looks in that folder for the file, before the import
starts, and if the file can't be found, just open the folder so I can rename
the file manually. I'm getting stuck on how to make the macro wait while I
rename the file then continue after I'm done. Of course, if the macro could
rename the file for me that would be great, but I never know what some bone
head has named the file so I can't tell the macro what file to look for.

 

[RyanH]

Here are a few things I use.

You could use the Open dialog box by using the FindFile Method or
GetOpenFile. I would suggest reading the Help section so you know what
parameters to use. This method displays the Open dialog box and allows the
user to hand select a file.

Or you could use the Dir Function.

If Dir(ThisWorkbook.Path & "\" & WorkbookName) = "" Then
' ask user to select the file with the FindFile Method, and continue code
Else
' macro continues with your code
End If

Hope this helps! If so, let me know and click "YES" below.

 

[Don M.]

I've tried using this code, and it opens the folder correctly, but none of
the contents appear. I still need code that only uses this code if the file
is NOT present.

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "\\000\744\744560\*.xls" 'Specify folder path
If .Show = -1 Then
'do it
Else
'don't do it
End If
End With

I'll look into Open, Findfile and GetOpenfile now.

Don

 

[Don M.]

OK, I have combined code into this:

If Dir("\\000\744\744560\bulkfulfillment.xls") = "" Then
response = MsgBox("Rename the file 'bulkfulfillment.xls'",
vbExclamation, "Bulk Fulfillment Report does not exist!")

With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "\\000\744\744560\*.xls" 'Specify folder path
If .Show = -1 Then 'do it
Else 'don't do it
End If
End With ' ask user to select the file with the FindFile Method, and
continue code
Else ' macro continues with your code
End If

and it works up to the point of displaying the contents of the folder. It
still just shows an empty window. I didn't see how to use Findfile here. I'll
have to go through it again.

Don

 

[RyanH]

Sorry about that Don, I got lazy on you by just pointing you to the help.
Here is some code that I would use.

Side Note: You said you may have to do this nine times, right? May I
suggest using the MultiSelect parameter of the GetOpenFilename method. This
will fill an array and you can then use a loop to loop thru each file and
execute your code, assuming you are executing the same code for each file.

Option Explicit

Sub FindAFile()

Const strFileName As String = "bulkfulfillmentreport.xls"
Dim varMyWorkbookName As Variant

If Dir("\\000\745\745670\" & strFileName) = "" Then
MsgBox "Can't Find " & strFileName, vbInformation

' open dialog box and rename file or just select the file to use
varMyWorkbookName = Application.GetOpenFilename
Else
MsgBox "Found File Name " & strFileName, vbInformation
End If

If varMyWorkbookName <> False Then
MsgBox "This is the file you selected " & varMyWorkbookName
End If

End Sub

Hope this helps!

 

[Don M.]

Thank you Ryan, I got something similar to your code put together and they
both work about the same way. I think your code gives me the ability to use
the file as is without renaming it, if I can get that incorporated into the
rest of my macro, which I like. However, when I run your code or my code, the
window that pops up is my Desktop, not the folder that contains the
incorrectly named file.

Here's my code, including all the variables that the path is built with:

If Dir("\\fileserver\Data\Global\TaskorderDocuments\000\" _
& Region1BulkPrefix & "\" & Region1BulkID & "\ _
BulkFulfillmentReport.xls") = "" Then

MsgBox("Blah, Blah", vbOKOnly, "Blah, Blah")
FileToOpen = Application.GetOpenFilename _
("\\fileserver\Data\Global\TaskorderDocuments\000\" _
& Region1BulkPrefix & "\" & Region1BulkID & "*.*, *.xls")
End If

I thought the FileToOpen line would open the path that is in parenthases,
but it opens my Desktop. I must not have the path specified correctly, but it
checks the correct path for the existence of the file correctly, otherwise
the code wouldn't be executing this part of the macro. I'm not experienced
enough with VB to understand why I'm incorrectly building the path.

Don

 

[Don M.]

Ryan, if I'm correct, and your code allows me to simply click on the
incorrectly named file, can the code then be written to rename the file that
I click on in the dialog that opens? The code is correctly identifying that
the file is named incorrectly. This would really be slick if it would then
open the folder with the incorrectly named file, I just click on that file in
the window that pops up, and the code then knows that that's the file I want
renamed and it renames it for me.

Can VB do that somehow?

Don

 

[RyanH]

The GetOpenFilename does not open the file it only returns the filename you
selected.

You suggestion sounds like a good idea. You can use the Name statement to
change the name and directory the file is in. The code below will test if
the file name is correct and in the correct directory, if it is not the user
will select the file to be replaced and the workbook will be opened. Once
the workbook you want to use is open you can use the wbkMyWorkbook variable
as a reference. For example,

Instead of Range("A1").Value = $1000 use, wbkMyWorkbook.Range("A1").Value

Option Explicit

Sub FindAFile()

' this is the file name that should be in strMyPath
Const myFileName As String = "BulkFulfillmentReport.xls"

Dim strMyPath As String
Dim varOldFileName As Variant
Dim wbkMyWorkbook As Workbook

' this is the Path myFileName should be in
strMyPath = "\\fileserver\Data\Global\TaskorderDocuments\000\" &
Region1BulkPrefix & "\" & Region1BulkID & "\"

' test if file is where it should be
If Dir(strMyPath & myFileName) = "" Then

' filter only .xls files, then select the file you wish to change
the name
varOldFileName = Application.GetOpenFilename("(*.xls), *.xls")

' change the name of the file
If varOldFileName <> False Then

' change wrong file name to correct file name
Name varOldFileName As strMyPath & myFileName
Else
MsgBox "You did not select a workbook.", vbInformation
End If
End If

' set the workbook to be used as a reference
Set wbkMyWorkbook = Workbooks.Open(strMyPath & myFileName)

' use wbkMyWorkbook as your workbook reference in the rest of your code

End Sub

Hope this helps! If so, let me know and click "YES" below.

 

[RyanH]

Correction

ActiveWorkbook.Sheet("Sheet1").Range("A1").Value = "$1000" use,
wbkMyWorkbook.Sheet("Sheet1").Range("A1").Value = "$1000"

 

[Don M.]

I'm almost there thanks to your help. There's just one last hangup from
making this code flawless.

I have code that works fine for the first of nine iterations, Region1
through Region9. The misnamed file is identified, found, I double-click on it
when the window pops up and the file is renamed correctly. The problem I'm
having is on the second iteration, what I call Region2, the window that pops
up not the correct folder. It's the folder that popped up for Region1. I have
to be able to specify which folder opens when the Application.GetOpenFilename
line is executed. It's like there is some default path being saved as
whatever the last path was.

I'm going to add some constants, which are actually based on these cell
references

Region1BulkID = Worksheets("Run Report").Cells(5, 3)
Region1BulkPrefix = Left(Worksheets("Run Report").Cells(5, 3), 3)
Region2BulkID = Worksheets("Run Report").Cells(5, 5)
Region2BulkPrefix = Left(Worksheets("Run Report").Cells(5, 5), 3)

in my application, so you can copy the code and try it if that will help.
The values are being assigned correctly when I run my macro.

Here's the code for Region1, which is working fine.

' Check to see if the Bulk Report is named correctly

Const Region1BulkPrefix = "744" ' This is set in my macro by a cell reference
Const Region1BulkID = "744560" ' This is set in my macro by a cell reference

BulkReportPath = "\\fileserver\Data\Global\TaskorderDocuments\000\" &
Region1BulkPrefix _ & "\" & Region1BulkID & "\" ' this is the Path
BulkReportFileName should be in

' path is correctly set as
\\fileserver\Data\Global\TaskorderDocuments\000\744\744560\

If dir(BulkReportPath & BulkReportFileName) = "" Then ' test if file is
where it should be

MsgBox "The Bulk Report is misnamed" & vbNewLine & vbNewLine & "Double
click the Bulk _ Report in the" & vbNewLine & "next window and it will be
renamed", vbInformation

Line1:

OldBulkReportFileName = Application.GetOpenFilename("(*bulk*.xls),
*bulk*.xls")

If OldBulkReportFileName <> False Then ' change the name of the file

Name OldBulkReportFileName As BulkReportPath & BulkReportFileName

Else
MsgBox "You did not select a workbook.", vbInformation

GoTo Line1:

End If
End If

Then I do some more macro stuff to complete the work for Region1 and I come
into the Region2 portion of the code. It's basically the same code, only the
variables for BulkReportPath are different due to the use of Region2 folder
specs.

Here's what I have for Region2 code:

' Check to see if the Bulk Report is named correctly

Const Region2BulkPrefix = "744" ' This is set in my macro by a cell reference
Const Region2BulkID = "744803" ' This is set in my macro by a cell reference

BulkReportPath = "\\fileserver\Data\Global\TaskorderDocuments\000\" &
Region2BulkPrefix _ & "\" & Region2BulkID & "\" ' this is the Path
BulkReportFileName should be in

' path is correctly set as
\\fileserver\Data\Global\TaskorderDocuments\000\744\744803\

If dir(BulkReportPath & BulkReportFileName) = "" Then ' test if file is
where it should be

MsgBox "The Bulk Report is misnamed" & vbNewLine & vbNewLine & "Double
click the Bulk _ Report in the" & vbNewLine & "next window and it will be
renamed", vbInformation

Line2:

OldBulkReportFileName = Application.GetOpenFilename("(*bulk*.xls),
*bulk*.xls")

If OldBulkReportFileName <> False Then ' change the name of the file

Name OldBulkReportFileName As BulkReportPath & BulkReportFileName

Else
MsgBox "You did not select a workbook.", vbInformation

GoTo Line2:

End If
End If

As you can see, this is nearly identical for Region1 and will be for all
nine regions once this works. The problem is that when I execute the
Application.GetOpenFilename line for Region2, the folder that gets opened is
the same one that I had opened in Region1. I don't want the user to have to
search for this folder, I want the macro to open it.

Do you 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