Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for file in folder, if not there, open folder to rename file
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for file in folder, if not there, open folder to rename file
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for file in folder, if not there, open folder to rename
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for file in folder, if not there, open folder to rename
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for file in folder, if not there, open folder to rename
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for file in folder, if not there, open folder to rename
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for file in folder, if not there, open folder to rename
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for file in folder, if not there, open folder to rename
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for file in folder, if not there, open folder to rename
Correction
ActiveWorkbook.Sheet("Sheet1").Range("A1").Value = "$1000" use, wbkMyWorkbook.Sheet("Sheet1").Range("A1").Value = "$1000" -- Cheers, Ryan "RyanH" wrote: 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check for file in folder, if not there, open folder to rename
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\0 00\" & Region1BulkPrefix _ & "\" & Region1BulkID & "\" ' this is the Path BulkReportFileName should be in ' path is correctly set as \\fileserver\Data\Global\TaskorderDocuments\000\74 4\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\0 00\" & Region2BulkPrefix _ & "\" & Region2BulkID & "\" ' this is the Path BulkReportFileName should be in ' path is correctly set as \\fileserver\Data\Global\TaskorderDocuments\000\74 4\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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check File name for its existance in a folder | Excel Programming | |||
Check for file in folder | Excel Programming | |||
Save file in a new folder, but create folder only if folder doesn't already exist? | Excel Programming | |||
open file from folder save in new folder | Excel Programming | |||
check if file is in particular folder | Excel Programming |