Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Application.GetOpenFilename to open correct folder
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Application.GetOpenFilename to open correct folder
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 On Oct 23, 9:52*am, 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 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 - |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Application.GetOpenFilename to open correct folder
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Application.GetOpenFilename to open correct folder
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\0 00\" & 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 "JP" wrote: 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 On Oct 23, 9:52 am, 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. - snip - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Application.GetOpenFilename to open correct folder
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Application.GetOpenFilename to open correct folder
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 On 23 Okt., 19:40, Don M. wrote: 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 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- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Application.GetOpenFilename to open correct folder
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. 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Application.GetOpenFilename to open correct folder
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 "Per Jessen" wrote: 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 On 23 Okt., 19:40, Don M. wrote: 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- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Application.GetOpenFilename to open correct folder
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" wrote: 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 On 23 Okt., 19:40, Don M. wrote: 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- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Application.GetOpenFilename to open correct folder
Don,
Look at the post from Dave Peterson. It seems that you need to incorporate his solution into your macro. Best regards, Per On 23 Okt., 20:58, Don M. wrote: 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? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting Application.GetOpenFilename to open correct folder
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 "Dave Peterson" wrote: 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. 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 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to set GetOpenFilename to a specific Folder | Excel Programming | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Programming | |||
Application.GetOpenFilename vs Application.Dialogs(xlDialogsOpen) | Excel Programming | |||
GetOpenFilename - Default Folder | Excel Programming |