Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared folder changed path - sometimes S://SharedFolder sometimes T://SharedFolder
I want to connect to a database file on a shared network folder from
Excel using either DAO or ADO. My experience, is that some user could not connect sometimes because their system changed the path like: - sometimes mappping like; S://SharedFolder sometimes T://SharedFolder? How can I avoid missing the path to the db I want to connect to from Excel using vba? Thank you for any help! Regards Jan T. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared folder changed path - sometimes S://SharedFolder sometimes T://SharedFolder
Jan T laid this down on his screen :
I want to connect to a database file on a shared network folder from Excel using either DAO or ADO. My experience, is that some user could not connect sometimes because their system changed the path like: - sometimes mappping like; S://SharedFolder sometimes T://SharedFolder? How can I avoid missing the path to the db I want to connect to from Excel using vba? Thank you for any help! Regards Jan T. Normally, a path on a server is UNC, beginning with ""\\server\share\" followed by the filename. I don't recommend using 'mapped' folders because <AFAIK these are locale-defined, and are only a redirect to a 'namespace' and NOT the absolute path to the actual folder. You might have better luck storing the UNC path in the Registry via 'SaveSetting' so your project can retrieve it via 'GetSetting' when it needs it. Additionally, you could prompt the user to locate the db file if it's not correct in the Registry so at first use (when/if the file gets moved) your project resets the Registry value to the new location. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared folder changed path - sometimes S://SharedFolder sometimes
While I totally agree with Garry's answer, you could also let the VBA
search for the path, if using a drive-letter, for some reason, is needed. Something like: For Counter = Asc("D") to Asc("Z") If Dir(Chr(Counter)&":\Sharefolder") < "" then DriveLetter = Chr(Counter) Next (Just made from back of my head - not tested) CE Den 29.01.2012 00:35, Jan T skrev: I want to connect to a database file on a shared network folder from Excel using either DAO or ADO. My experience, is that some user could not connect sometimes because their system changed the path like: - sometimes mappping like; S://SharedFolder sometimes T://SharedFolder? How can I avoid missing the path to the db I want to connect to from Excel using vba? Thank you for any help! Regards Jan T. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared folder changed path - sometimes S://SharedFolder sometimes T://SharedFolder
Charlotte E. laid this down on his screen :
While I totally agree with Garry's answer, you could also let the VBA search for the path, if using a drive-letter, for some reason, is needed. Something like: For Counter = Asc("D") to Asc("Z") If Dir(Chr(Counter)&":\Sharefolder") < "" then DriveLetter = Chr(Counter) Next (Just made from back of my head - not tested) CE Den 29.01.2012 00:35, Jan T skrev: I want to connect to a database file on a shared network folder from Excel using either DAO or ADO. My experience, is that some user could not connect sometimes because their system changed the path like: - sometimes mappping like; S://SharedFolder sometimes T://SharedFolder? How can I avoid missing the path to the db I want to connect to from Excel using vba? Thank you for any help! Regards Jan T. Charlotte, That's a good suggestion for finding a specific folder on a local drive! <FYI What I've learned is that VB[A] can't work with 'mapped' drives on a network because those are for use by the system only. What the system does is to create a 'virtual namespace' as a 'pointer' to the actual location on the network. In this case it's been easier to prompt the user via the folder picker dialog when the stored path throws an error. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared folder changed path - sometimes S://SharedFolder sometimes T://SharedFolder
On Jan 29, 9:13*am, GS wrote:
<FYI What I've learned is that VB[A] can't work with 'mapped' drives on a network because those are for use by the system only. What the system does is to create a 'virtual namespace' as a 'pointer' to the actual location on the network. In this case it's been easier to prompt the user via the folder picker dialog when the stored path throws an error. VBA *can* work fine with mapped drives, as long as the drive letter is consistent. If each user has a different drive letter mapped, then the UNC path should be used instead. Tim -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared folder changed path - sometimes S://SharedFolder sometimes T://SharedFolder
Tim Williams wrote on 1/30/2012 :
On Jan 29, 9:13*am, GS wrote: <FYI What I've learned is that VB[A] can't work with 'mapped' drives on a network because those are for use by the system only. What the system does is to create a 'virtual namespace' as a 'pointer' to the actual location on the network. In this case it's been easier to prompt the user via the folder picker dialog when the stored path throws an error. VBA *can* work fine with mapped drives, as long as the drive letter is consistent. If each user has a different drive letter mapped, then the UNC path should be used instead. Tim -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Tim, My mistake! I meant to ref 'mapped folders' in context. My experience with *directly* referring to folders 'mapped' to UNC drives is that they return the redirect folder NOT the UNC folder. Same behavior occurs with drives since both are 'paths' to somewhere. If I'm wrong I'd be happy to test any code you offer that shows otherwise. AFAIK, anything other than an absolute path will not work in any programming language when working with UNC paths over a network. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared folder changed path - sometimes S://SharedFolder sometimes T://SharedFolder
On Jan 30, 4:12*pm, GS wrote:
Tim Williams wrote on 1/30/2012 : On Jan 29, 9:13*am, GS wrote: <FYI What I've learned is that VB[A] can't work with 'mapped' drives on a network because those are for use by the system only. What the system does is to create a 'virtual namespace' as a 'pointer' to the actual location on the network. In this case it's been easier to prompt the user via the folder picker dialog when the stored path throws an error.. VBA *can* work fine with mapped drives, as long as the drive letter is consistent. If each user has a different drive letter mapped, then the UNC path should be used instead. Tim -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - Tim, My mistake! I meant to ref 'mapped folders' in context. My experience with *directly* referring to folders 'mapped' to UNC drives is that they return the redirect folder NOT the UNC folder. Same behavior occurs with drives since both are 'paths' to somewhere. If I'm wrong I'd be happy to test any code you offer that shows otherwise. AFAIK, anything other than an absolute path will not work in any programming language when working with UNC paths over a network. -- Garry If I map a local drive Z to \\someserver\somefolder I can do this: Debug.Print Dir("Z:\") or this: Debug.Print Dir("\\someserver\somefolder\") and I get the same result. Maybe we're talking at cross-purposes here - do you have an example which doesn't work like this? Tim |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared folder changed path - sometimes S://SharedFolder sometimes T://SharedFolder
Tim Williams used his keyboard to write :
If I map a local drive Z to \\someserver\somefolder I can do this: Debug.Print Dir("Z:\") or this: Debug.Print Dir("\\someserver\somefolder\") and I get the same result. Maybe we're talking at cross-purposes here - do you have an example which doesn't work like this? We might very well not be on the same page here! I was thinking of the situation where I was using a Windows Explorer FolderView method to return a path and whenever I selected a 'mapped' *folder* it returned the redirect folder rather than the absolute path. I'm sure you are correct about a local drive as explained, and so we are not on the same page regarding 'mapped' network folders (not as a local drive) as represented in Windows Explorer's folderview list under 'My Network Places'. IIRC, these folders are 'namespaces' that the system redirects via a '.lnk' to the absolute path. We can't use the value returned by the namespace as a valid path. The term that applies here is 'AbsolutePIDL' as pertains to Windows file system. There's no easy way to get the target of the link without jumping through several API hoops to get there. I opted to use a 3rd party OCX replacement for WE that promised all kinds of customization. Awesome tool but duplicated what WE did to a 'tee'! When I explained the problem to the author of the FolderView.ocx he understood exactly what I meant but stated that his ocx duplicates what Windows Explorer does. I replied that this is exactly why I was using his control since it was advertised as being identical to WE *'plus'* fully customizeable. Since this was used in a proprietary file manager app, he was gracious enough to upgrade the ocx by adding an additional method to always return an AbsolutePIDL to an additional 'AbsolutePath' property so I could access it more easily. This allowed me to continue using the ocx with my project. What was interesting is that both WE Fileview and his Fileview.ocx displayed the actual folder contents which, I suspect, is because his tool did in fact duplicate WE in 'out-of-the-box' configuration. Explains why/how he knew exactly what to do to give me what I was looking for. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared folder changed path - sometimes S://SharedFolder sometimes T://SharedFolder
On Jan 31, 7:30*am, GS wrote:
Tim Williams used his keyboard to write : If I map a local drive Z to \\someserver\somefolder I can do this: Debug.Print Dir("Z:\") or this: Debug.Print Dir("\\someserver\somefolder\") and I get the same result. *Maybe we're talking at cross-purposes here - do you have an example which doesn't work like this? We might very well not be on the same page here! I was thinking of the situation where I was using a Windows Explorer FolderView method to return a path and whenever I selected a 'mapped' *folder* it returned the redirect folder rather than the absolute path. I'm sure you are correct about a local drive as explained, and so we are not on the same page regarding 'mapped' network folders (not as a local drive) as represented in Windows Explorer's folderview list under 'My Network Places'. IIRC, these folders are 'namespaces' that the system redirects via a '.lnk' to the absolute path. We can't use the value returned by the namespace as a valid path. The term that applies here is 'AbsolutePIDL' as pertains to Windows file system. There's no easy way to get the target of the link without jumping through several API hoops to get there. I opted to use a 3rd party OCX replacement for WE that promised all kinds of customization. Awesome tool but duplicated what WE did to a 'tee'! When I explained the problem to the author of the FolderView.ocx he understood exactly what I meant but stated that his ocx duplicates what Windows Explorer does. I replied that this is exactly why I was using his control since it was advertised as being identical to WE *'plus'* fully customizeable. Since this was used in a proprietary file manager app, he was gracious enough to upgrade the ocx by adding an additional method to always return an AbsolutePIDL to an additional 'AbsolutePath' property so I could access it more easily. This allowed me to continue using the ocx with my project. What was interesting is that both WE Fileview and his Fileview.ocx displayed the actual folder contents which, I suspect, is because his tool did in fact duplicate WE in 'out-of-the-box' configuration. Explains why/how he knew exactly what to do to give me what I was looking for. -- Garry I see. That's not something I've run into, but typically I'm not asking users to browse the full network for files/folders. Tim |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shared folder changed path - sometimes S://SharedFolder sometimes T://SharedFolder
It happens that Tim Williams formulated :
On Jan 31, 7:30*am, GS wrote: Tim Williams used his keyboard to write : If I map a local drive Z to \\someserver\somefolder I can do this: Debug.Print Dir("Z:\") or this: Debug.Print Dir("\\someserver\somefolder\") and I get the same result. *Maybe we're talking at cross-purposes here - do you have an example which doesn't work like this? We might very well not be on the same page here! I was thinking of the situation where I was using a Windows Explorer FolderView method to return a path and whenever I selected a 'mapped' *folder* it returned the redirect folder rather than the absolute path. I'm sure you are correct about a local drive as explained, and so we are not on the same page regarding 'mapped' network folders (not as a local drive) as represented in Windows Explorer's folderview list under 'My Network Places'. IIRC, these folders are 'namespaces' that the system redirects via a '.lnk' to the absolute path. We can't use the value returned by the namespace as a valid path. The term that applies here is 'AbsolutePIDL' as pertains to Windows file system. There's no easy way to get the target of the link without jumping through several API hoops to get there. I opted to use a 3rd party OCX replacement for WE that promised all kinds of customization. Awesome tool but duplicated what WE did to a 'tee'! When I explained the problem to the author of the FolderView.ocx he understood exactly what I meant but stated that his ocx duplicates what Windows Explorer does. I replied that this is exactly why I was using his control since it was advertised as being identical to WE *'plus'* fully customizeable. Since this was used in a proprietary file manager app, he was gracious enough to upgrade the ocx by adding an additional method to always return an AbsolutePIDL to an additional 'AbsolutePath' property so I could access it more easily. This allowed me to continue using the ocx with my project. What was interesting is that both WE Fileview and his Fileview.ocx displayed the actual folder contents which, I suspect, is because his tool did in fact duplicate WE in 'out-of-the-box' configuration. Explains why/how he knew exactly what to do to give me what I was looking for. -- Garry I see. That's not something I've run into, but typically I'm not asking users to browse the full network for files/folders. Tim Yeah.., I can relate. My experience results from making a special file manager app for working CNC machine program files over a network. In some cases the machines were connected directly to the network. My solution needed to service every possible scenario for getting at the files wherever they happened to be located. The version that has the built-in explorer is a VB6.exe (about to be released as a C++.exe since the vbruntime is being deprecated next Windows version). The Excel VBA version uses the FolderPicker dialog as the solution via error trapping when a user selects mapped folders. The AbsolutePath is stored on the worksheet in both versions. (The VB6.exe also uses its own 3rd party spreadsheet.ocx) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
All Hyperlinks have changed the path | Excel Discussion (Misc queries) | |||
path to macros being changed | Excel Programming | |||
Remove end folder from path found with ThisWorkbook.Path command ? | Excel Programming | |||
How to List the names of the subfolders present in the folder (path of folder is given in the textbox by user ) | Excel Programming | |||
File path of external link changed | Links and Linking in Excel |