PDA

View Full Version : Solved: Directory Path



Gil
02-14-2011, 07:21 PM
Hello
After a few hours research I dont know where to start with this problem. I want to be able to send out my spreadsheets to my members but do not want to reveal the macros if possible. In order for them to use the spreadsheet they need to input their own directory file path into the vba code.
So I think to start I need some code which identifies that the default path exists in some VBA code would be a help. Any suggestions will be very helpful.
Gil

:help

Simon Lloyd
02-14-2011, 07:25 PM
Why not supply your code that you have and we can help make it a bit more universal, why the need for each person to have an ability to choose different filepaths anyway?

Kenneth Hobs
02-14-2011, 07:48 PM
ThisWorkbook.Path gives the path for the VBA project file's path no matter where they put it.

It that does not suffice, it is better to put the path in a cell and make the workbook get the path there.

Gil
02-15-2011, 04:55 AM
Hello
Thanks for the suggestions.
Simon, the need for each person to have an ability to choose different filepaths is because everyone has their own preference which drive or file name etc
Kenneth, I am a novice, is this the way to use ThisWorkbook.Path

Private Sub Workbook_Open()
MsgBox ThisWorkbook.Path
End Sub
and i didnt understand 'the path in a cell and make the workbook get the path there'
I have also now found something else that will help me get a start. http://www.vbaexpress.com/kb/getarticle.php?kb_id=559.
I think what I need now is some code to search a vba project to see if the same path is specified and with an option to edit it.
When I have a few bits put together I will gladly share it.

Gil

:think:

Gil
02-15-2011, 05:21 AM
Kenneth
Did you mean for 'the path in a cell and make the workbook get the path there' something like this

=CELL("filename")

Gil
:think:

ndendrinos
02-15-2011, 06:22 AM
Hello Gil,
I posted a very similar question here
http://www.mrexcel.com/forum/showthread.php?t=428271&highlight=ndendrinos
maybe you would like to have a look while waiting for a solution here.

Gil
02-15-2011, 06:29 AM
Hello ndendrinos
Looks interesting, I will try it out.
Gil

:thumb

shrivallabha
02-15-2011, 06:29 AM
This one is picked from here and I have not marked up the thread number so can't give you links to the original one.
First create a button and assign this macro to it:
Private Sub BrowseFolder_Click()
On Error Resume Next
Dim result As String
result = BrowseForFolder
Select Case result
Case Is = False
result = "Invalid !"
BrowsedFolder.Caption = result
Case Else
End Select
MsgBox "You have just selected folder " & result, _
vbOKOnly + vbInformation
BrowsedFolder.Caption = result
End Sub
And a function in the same module:
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Thanks to VBAX
Dim ShellApp As Object
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please select folder", 0, OpenAt)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0
Set ShellApp = Nothing
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function
Invalid:
BrowseForFolder = False
End Function
The main advantage is that it provides you with the flexibility of picking up custom path!

Kenneth Hobs
02-15-2011, 09:27 PM
I don't see a benefit to =cell("filename"). If you want a path from a cell:
Msgbox Worksheets("Sheet1").Range("A1").Value

If you want the fullname then:
Thisworkbook.Fullname

If your goal is to get a path from the user, then the browse method posted or others would suffice.

Of course you can mix and match getting the path/filename and storing it via a range cell if you need it for a later session or use a Public variable if it need only exist for the session.

Simon Lloyd
02-15-2011, 09:50 PM
You could always use the Before Save event and call something like this:Sub file_to_save()
Dim sFile As Variant
Do While sFile = False
sFile = Application.GetSaveAsFilename(InitialFileName:="", _
fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls", Title:="Save workbook to..")
If sFile <> False Then
ActiveWorkbook.SaveAs Filename:=sFile
Else
MsgBox ("File name required!"), vbInformation
End If
Loop
End Sub

Gil
02-24-2011, 12:47 PM
Hello
In order to close this thread as solved I have attached a file which shows some things I was trying to achieve. In fact, I have and am happy to say that I fully appreciate the help from you all. If any other novices get any benefit from my example I will be pleased.

Gil
:clap:

mdmackillop
02-24-2011, 01:09 PM
You could use SaveSettings to save the path to the registry, and GetSettings to recover it.

Gil
02-24-2011, 02:04 PM
Thank you. As you know there are many ways to do the same thing with vba. For me it's finding one that works for me. My knowledge of this subject is very basic so can I ask where your suggestion fits.( in the code that is )
Many thanks
Gil
:dunno

mdmackillop
02-24-2011, 03:26 PM
You would use this as follows

Private Sub CommandButton4_Click()
MsgBox GetSetting("MyApp", "Startup", "Path")
End Sub

Private Sub BrowseFolder_Click()
On Error Resume Next
Dim result As String
result = BrowseForFolder
Select Case result
Case Is = False
result = "Invalid !"
BrowsedFolder.Caption = result
Case Else
End Select
MsgBox "You have just selected folder " & result, _
vbOKOnly + vbInformation
BrowsedFolder.Caption = result
'HKEY_CURRENT_USER\Software\VB and VBA Program Settings.
SaveSetting "MyApp", "Startup", "Path", result

End Sub

Gil
02-24-2011, 05:01 PM
Thanks for that. I am now away for a couple of days so I will experiment then. Many thanks.
Gil