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.

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.
--
Cheers,
Ryan


"Don M." wrote:

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.

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

"RyanH" wrote:

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.
--
Cheers,
Ryan


"Don M." wrote:

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.

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

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.
--
Cheers,
Ryan


"Don M." wrote:

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.

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!
--
Cheers,
Ryan


"Don M." wrote:

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

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.
--
Cheers,
Ryan


"Don M." wrote:

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.

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\0 00\" _
& 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

"RyanH" wrote:

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!
--
Cheers,
Ryan


"Don M." wrote:

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

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.
--
Cheers,
Ryan


"Don M." wrote:

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.

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

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!
--
Cheers,
Ryan


"Don M." wrote:

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

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.
--
Cheers,
Ryan


"Don M." wrote:

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.

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\0 00\" &
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.
--
Cheers,
Ryan


"Don M." wrote:

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

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!
--
Cheers,
Ryan


"Don M." wrote:

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

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.
--
Cheers,
Ryan


"Don M." wrote:

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.