Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
change local drive / folder to network folder
Hi,
I need to read data from various excel files on a netwerk connected PC. I can read the info when nothing goes wrong with the filename, but if there's a typo in the filename (manual input), this one can't be found on the network PC, so i reply with a messagebox with a selection Retry / Cancel. When Retry is selected, i change the default drive to the network location where the file resides so that the files in the xlDialogOpen are the ones in this location, but it won't work. i get an " Run-time error '5' " - " Invalid procedure call or argument" The network location is something like this : \\Kndclt21079\F TMG\Cat\Ate_Hass1\Logging\HASS_PowerMeasurements\ this is part of the code i use: Dim CurFolder As String Dim NewFolder As String CurFolder = CurDir .. .. .. 'open dialog to select TMG file NewFolder = KastNummer '= \\Kndclt21079\F TMG\Cat \Ate_Hass1\Logging\HASS_PowerMeasurements\ ChDrive NewFolder ChDir NewFolder dlgAnswer = Application.Dialogs(xlDialogOpen).Show 'change back ChDrive CurFolder ChDir CurFolder Exit Sub Any help apreciated. Thanks in advance Ludo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
change local drive / folder to network folder
Newfolder shouldn't have a backslash as the last charater.
"Ludo" wrote: I think the better solution is to use the filedialog and set the initial folder Set fd = Application.FileDialog(msoFileDialogFilePicker) 'Declare a variable to contain the path 'of each selected item. Even though the path is a String, 'the variable must be a Variant because For Each...Next 'routines only work with Variants and Objects. Dim vrtSelectedItem As Variant 'Use a With...End With block to reference the FileDialog object. With fd 'Set the initial path to the KastNummer '= \\Kndclt21079\F TMG\Cat\Ate_Hass1\Logging\HASS_PowerMeasurements\ .InitialFileName = KastNummer 'Use the Show method to display the File Picker dialog box and return the 'user's action. 'If the user presses the action button... If .Show = -1 Then dlgAnswer = .SelectedItems end if end with Hi, I need to read data from various excel files on a netwerk connected PC. I can read the info when nothing goes wrong with the filename, but if there's a typo in the filename (manual input), this one can't be found on the network PC, so i reply with a messagebox with a selection Retry / Cancel. When Retry is selected, i change the default drive to the network location where the file resides so that the files in the xlDialogOpen are the ones in this location, but it won't work. i get an " Run-time error '5' " - " Invalid procedure call or argument" The network location is something like this : \\Kndclt21079\F TMG\Cat\Ate_Hass1\Logging\HASS_PowerMeasurements\ this is part of the code i use: Dim CurFolder As String Dim NewFolder As String CurFolder = CurDir .. .. .. 'open dialog to select TMG file NewFolder = KastNummer '= \\Kndclt21079\F TMG\Cat \Ate_Hass1\Logging\HASS_PowerMeasurements\ ChDrive NewFolder ChDir NewFolder dlgAnswer = Application.Dialogs(xlDialogOpen).Show 'change back ChDrive CurFolder ChDir CurFolder Exit Sub Any help apreciated. Thanks in advance Ludo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
change local drive / folder to network folder
On 11 sep, 12:48, Joel wrote:
Newfolder shouldn't have a backslash as the last charater. "Ludo" wrote: I think the better solution is to use the filedialog and set the initial folder * *Set fd = Application.FileDialog(msoFileDialogFilePicker) * * 'Declare a variable to contain the path * * 'of each selected item. Even though the path is a String, * * 'the variable must be a Variant because For Each...Next * * 'routines only work with Variants and Objects. * * Dim vrtSelectedItem As Variant * * 'Use a With...End With block to reference the FileDialog object. * * With fd * * * * 'Set the initial path to the KastNummer * * * * * '= \\Kndclt21079\F TMG\Cat\Ate_Hass1\Logging\HASS_PowerMeasurements\ * * * * .InitialFileName = KastNummer * * * * 'Use the Show method to display the File Picker dialog box and return the * * * * * 'user's action. * * * * 'If the user presses the action button... * * * * If .Show = -1 Then * * * * * *dlgAnswer = .SelectedItems * * * * end if * * end with Hi, I need to read data from various excel files on a netwerk connected PC. I can read the info when nothing goes wrong with the filename, but if there's a typo in the filename (manual input), this one can't be found on the network PC, so i reply with a messagebox with a selection Retry / Cancel. When Retry is selected, i change the default drive to the network location where the file resides so that the files in the xlDialogOpen are the ones in this location, but it won't work. i get an " Run-time error '5' " - *" Invalid procedure call or argument" The network location is something like this : \\Kndclt21079\F TMG\Cat\Ate_Hass1\Logging\HASS_PowerMeasurements\ this is part of the code i use: * * Dim CurFolder As String * * Dim NewFolder As String * * CurFolder = CurDir .. .. .. 'open dialog to select TMG file * * * * * * NewFolder = KastNummer *'= \\Kndclt21079\F TMG\Cat \Ate_Hass1\Logging\HASS_PowerMeasurements\ * * * * * * ChDrive NewFolder * * * * * * ChDir NewFolder * * * * * * dlgAnswer = Application.Dialogs(xlDialogOpen)..Show * * * * * * 'change back * * * * * * ChDrive CurFolder * * * * * * ChDir CurFolder * * * * * * Exit Sub Any help apreciated. Thanks in advance Ludo- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Hi Joel, Tried this before and even when i remove the backslash on the end, it returns me the same error code i used to strip the last character: 'open dialog to select TMG file NewFolder = Left(KastNummer, Len(KastNummer) - 1) ' \ \Kndclt21079\F TMG\Cat\Ate_Hass1\Logging\HASS_PowerMeasurements ChDrive NewFolder ChDir NewFolder Ludo |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
change local drive / folder to network folder
Read Joel's response once more. He doesn't use ChDrive or ChDir. Just the
..initialfilename parm. (chdrive won't work with UNC paths.) There is an API function that would work, but try Joel's suggestion first. Ludo wrote: On 11 sep, 12:48, Joel wrote: Newfolder shouldn't have a backslash as the last charater. "Ludo" wrote: I think the better solution is to use the filedialog and set the initial folder Set fd = Application.FileDialog(msoFileDialogFilePicker) 'Declare a variable to contain the path 'of each selected item. Even though the path is a String, 'the variable must be a Variant because For Each...Next 'routines only work with Variants and Objects. Dim vrtSelectedItem As Variant 'Use a With...End With block to reference the FileDialog object. With fd 'Set the initial path to the KastNummer '= \\Kndclt21079\F TMG\Cat\Ate_Hass1\Logging\HASS_PowerMeasurements\ .InitialFileName = KastNummer 'Use the Show method to display the File Picker dialog box and return the 'user's action. 'If the user presses the action button... If .Show = -1 Then dlgAnswer = .SelectedItems end if end with Hi, I need to read data from various excel files on a netwerk connected PC. I can read the info when nothing goes wrong with the filename, but if there's a typo in the filename (manual input), this one can't be found on the network PC, so i reply with a messagebox with a selection Retry / Cancel. When Retry is selected, i change the default drive to the network location where the file resides so that the files in the xlDialogOpen are the ones in this location, but it won't work. i get an " Run-time error '5' " - " Invalid procedure call or argument" The network location is something like this : \\Kndclt21079\F TMG\Cat\Ate_Hass1\Logging\HASS_PowerMeasurements\ this is part of the code i use: Dim CurFolder As String Dim NewFolder As String CurFolder = CurDir .. .. .. 'open dialog to select TMG file NewFolder = KastNummer '= \\Kndclt21079\F TMG\Cat \Ate_Hass1\Logging\HASS_PowerMeasurements\ ChDrive NewFolder ChDir NewFolder dlgAnswer = Application.Dialogs(xlDialogOpen).Show 'change back ChDrive CurFolder ChDir CurFolder Exit Sub Any help apreciated. Thanks in advance Ludo- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Hi Joel, Tried this before and even when i remove the backslash on the end, it returns me the same error code i used to strip the last character: 'open dialog to select TMG file NewFolder = Left(KastNummer, Len(KastNummer) - 1) ' \ \Kndclt21079\F TMG\Cat\Ate_Hass1\Logging\HASS_PowerMeasurements ChDrive NewFolder ChDir NewFolder Ludo -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HyperLink to a folder on other drive on the same network (LAN) | Excel Worksheet Functions | |||
Links to Local Drive Instead of Same Folder | Excel Discussion (Misc queries) | |||
Folder Network drive | Excel Discussion (Misc queries) | |||
Template Startup Folder On A Network Drive | Excel Discussion (Misc queries) | |||
Prgrammatically save a file to a specific folder on a network drive | Excel Programming |