try two (or more) paths in one code (1 Viewer)

steve2 · Dec 15, 2017

I have the following problem:

In ms access I have a button to open a file or folder on my computer.

If I want another user to be able to open the same folder from his computer, if using the same button of course he gets an error. He needs to take another path. So I created a second button. Effective, but not very handy .

I want to be able to Follow one path in my code and on error, close windows explorer and try another path.

Till now I wrote the following (but it still opens 2 windows. One correct, and one fault).

Code:

Private Sub Knop299_Click()
'opening a folder by following a path
'when the path does not exist, try the following path e.g. path2
' and so on

Dim Foldername As String
Dim Foldername2 As String

Foldername = "D:\Documents and Settings\Administrator\Mydocuments\doc27"
Foldername2 = "F:\BEHEER\BELANRIJKEDOCUMENTEN\doc27"
Shell "C:\Windows\explorer.exe """ & Foldername & "", vbNormalFocus

'here it goes wrong.  File is not on this path, but still my documents is opened
'so here I would like a code to close explorer.  

On Error Resume Next
Shell "C:\Windows\explorer.exe """ & Foldername2 & "", vbNormalFocus


End Sub

Thanks for helping me out (I hope I explained my problem correctly)

static · Dec 15, 2017

Why do you need to use Windows Explorer?

You don't need Explorer to work with files in VBA.

steve2 · Dec 15, 2017

The file is not in access, it's just a map on my computer I want to open. I assume you mean reports in the access dbase itself.
(sorry If I dit not understand your remark correctly)

static · Dec 15, 2017

Can you say what you are trying to do, instead of telling us what doesn't work?

Thanks.

Mark_ · Dec 15, 2017

The map is in a shared location, correct?

If so, add a "User" table. In the "User" table each user has user-specific information. This would include the full path to your map. This will also be expanded as you add more to your program (or more users of your program).

For file locations I would ALWAYS either allow the users to use a file dialog to select OR have the correct path saved in a table. This avoid issues as you go forward and discover you have more files users will need to access or more users who have different paths to the same data.

steve2 · Dec 15, 2017

thanks Mark,

didn't think so far, but that seems an easy solution

static · Dec 15, 2017

If files are on a network you should use UNC. Then you don't need to worry about mapped drives.

https://pagecommunication.co.uk/2014/07/15/vba-to-convert-a-mapped-drive-letter-to-unc-path/

steve2 · Dec 23, 2017

Sorry I'm a bit late with my thanking, but anyway,
thanks for the hint. UNC is new to me. I had to look for the Microsoft Scripting Runtime because I'm still programming in the 97 access (It runs in access 2017 but cannot program in it and I'm afraid to ruin the relations in my dbase).
It works now perfectly from my computer and from the one of my colleague with one button. So a lot of thanks and have a good year-ending.

The_Doc_Man · Dec 23, 2017

What gets me is that if you have a properly split FE/BE setup, and if you set the file usage permissions correctly, you should not CARE about the other users' paths. Even AC97 was able to do that, and Win 98 ALSO hands permissions correctly. But you aren't on THAT level of Windows, are you?

steve2 · Dec 23, 2017

correct. My level is not that high. (I'm glad everything keeps working). At first I even had a problem with using the hyperlinks, but that is because it was more than 20 years ago I finished my program - (and since then only changed some reports). So sometimes I see problems where there shouldn't be any.

For that, I'm glad forums like this one exist.

try two (or more) paths in one code (1 Viewer)

steve2

Registered User.

static

Registered User.

steve2

Registered User.

static

Registered User.

Mark_

Longboard on the internet

steve2

Registered User.

static

Registered User.

steve2

Registered User.

The_Doc_Man

Immoderate Moderator

steve2

Registered User.

Similar threads

Users who are viewing this thread