VB Help Define a local path

[Chris Elston]

Hello, need help from someone who knows more about VB than me. I have an Excel Spreadsheet that I've written some macros for. It seems to me Windows has these "global variables" you can use to find a local user path. For example, I want a varible path name in my code. Instead of this: C:\Documents and Settings\Chris\Desktop I would like something like this: C:\"windows global path to user logged in". I've seen something like this before, but I can't find it or google it. and will this work in Excel VBA code? Or I guess I will have my Excel User pick a path and store it in an Excel Cell, then read that cell to know the path I want to save the folder too.

[Bob O]

txtTemp = File.OpenText(TextBox1.Text) Not the VB expert but if you place a text box on the form and have them type in the path and then reference the textbox1.text for the path or select it from a list box and them reference that. I just test the above code in a homework problem I had in .net and it worked.

[IO_Rack]

Search the help section for "Environ Function". This function gives you access to the windoze environment variables. "User Directory" should be one of them. Edit: Sorry. "User Directory" is not an environment variable. "username" is. This is what I did: "C:\Documents and Settings\" + Environ("username") + "\My Documents\" That was the best I could come up with at the time. I am not a VB expert. Edited February 26, 2007 by IO_Rack

[GerryM]

Attached is a macro I programmed in Excel for the user to select a file. The function returns the filename and directory selected. FileDialog.txt

[Chris Elston]

I think I beat you to it...I ended up making the USER pick the folder location, rather than trying to read it. I found this snippet on an excel forum board. Works like a champ. I store the file location in cell F7. Then recall it later when I want to know where to save the file too. Thanks! '---------------------------------- '- macro to get a folder '-------------------------------------- Option Explicit Public Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type '32-bit API declarations Declare Function SHGetPathFromIDList Lib "shell32.dll" _ Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) _ As Long '- API function Declare Function SHBrowseForFolder Lib "shell32.dll" _ Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long '================================================= '- THIS RUNS THE WHOLE MACRO TO GET THE SELECTION '================================================= Sub BrowseFolders() Dim Msg As String Msg = "Please select a folder." 'MsgBox GetDirectory(Msg) Range("F7").Select ActiveCell.FormulaR1C1 = GetDirectory(Msg) End Sub ' '------------------------- '- this does the API call '------------------------- Function GetDirectory(Optional Msg) As String Dim bInfo As BROWSEINFO Dim path As String Dim r As Long, x As Long, pos As Integer '--------------------------- ' Root folder = Desktop bInfo.pidlRoot = 0& '--------------------------- ' Title in the dialog If IsMissing(Msg) Then bInfo.lpszTitle = "Select a folder." Else bInfo.lpszTitle = Msg End If '--------------------------- ' Type of directory to return bInfo.ulFlags = &H1 '--------------------------- ' Display the dialog x = SHBrowseForFolder(bInfo) '--------------------------- ' Parse the result path = Space$(512) r = SHGetPathFromIDList(ByVal x, ByVal path) If r Then pos = InStr(path, Chr$(0)) GetDirectory = Left(path, pos - 1) Else GetDirectory = "" End If End Function '----------------------------------------------------