Solved: User Changeable File Paths

[asystole0]

Hi Guys

I have a spreadsheet im going to dishout to about 20 people all over the country with no way of remote access and some aernt very tech savvie!

I have lots of references to directorys in my VBA code is there a way to have a vba form to allow the user to change the file paths and store the path so they only have to change it once?


E.G. I used the code below to populate a combobox but want to change the path from "C:\" without going in and changing the code.

ComboBox1.Clear
FolderName = Dir("C:\", vbDirectory)
Do While FolderName <> ""
    If FolderName <> "." And FolderName <> ".." Then
        If (GetAttr("C:\" & FolderName) And vbDirectory) = vbDirectory Then
           ComboBox1.AddItem FolderName
        End If
    End If
    FolderName = Dir()
Loop

Any help is greatly apreciated as always!

[lynnnow]

What you can do is create a hidden TXT file unbeknownst to the user to get the folder name and save the result as a complete string in that TXT file. Then when the file loads, search for the TXT file and use the TXT file for the path. If the file is loaded the first time, the TXT file will not be found and the user should be prompted to get the folder path. But do remember to keep the TXT file hidden so that the user does not delete it. Otherwise he'll have to set the folder path once again.

Just a nudge in some direction. Will try some code for this.

[Paul_Hossler]

Another way to start with -- needs some polish and error checks of course -- but doesn't require extra files. Keeps things in the registry.

[VBA]
Option Explicit
Sub drv()
If OK2Continue Then
MsgBox "OK"
Else
MsgBox "Not OK"
End If
End Sub

Function OK2Continue() As Boolean
Dim sPath As String

OK2Continue = False

sPath = GetSetting("MyApp", "MyAppVariables", "FolderPath", "")
If Len(sPath) = 0 Then
sPath = FolderChoose(MyDocuments)
If Len(sPath) = 0 Then Exit Function
End If
Call SaveSetting("MyApp", "MyAppVariables", "FolderPath", sPath)
OK2Continue = True

End Function

Function FolderChoose(Optional sInitFolder As String = "", _
Optional Title As String = "Select Folder or Cancel to Exit", _
Optional ButtonLabel As String = "Select Folder") As String
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
.ButtonName = ButtonLabel
.InitialFileName = sInitFolder
.Title = Title
If .Show = -1 Then
FolderChoose = .SelectedItems(1)
Else
FolderChoose = vbNullString
End If
End With
Set fd = Nothing
End Function
Function MyDocuments() As String
Dim objFSO As Object
Dim objShell As Object
Dim objFolder As Object
Dim objFolderItem As Object

Const MY_DOCUMENTS = &H5&

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("Shell.Application")

Set objFolder = objShell.Namespace(MY_DOCUMENTS)
Set objFolderItem = objFolder.Self
MyDocuments = objFolderItem.Path

Set objFSO = Nothing
Set objShell = Nothing
Set objFolder = Nothing
Set objFolderItem = Nothing

End Function
[/VBA]


As it is now, once it's been stored in the registry, it will not ask for a folder, but that's easy enough to change.

Paul

[Bob Phillips]

The simplest way is to store them in a hidden worksheet.

If you make it very hidden, the user won't be able to unhide from Excel.

[Tinbendr]

I tend to use CustomDocumentProperties.