Saving and opening a named folder using VB

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Hi,
In creating a workbook, I seem to have struck a dificulty in that if the
workbook is transferred to another computer I need to change some of the
code each time (as the other computers run different versions of windows, so
the path to a specific folder is different.)
The code that's affected relates to saving the file and opening the file
using VB code within that file. Is there some way that excel can create a
new named folder and place it in the My Documents folder so that the
procedure would work in allcircumstances? AND also look for that folder
(wherever it may be found) to open a file.
Rob
 
This API code was copied out from a post by Laurent Longre.

Option Explicit
Private Declare Function SHGetSpecialFolderLocation Lib "Shell32" _
(ByVal hwnd As Long, ByVal nFolder As Long, ppidl As Long) As Long
Private Declare Function SHGetPathFromIDList Lib "Shell32" _
(ByVal Pidl As Long, ByVal pszPath As String) As Long

Function PersonalFolder() As String
Dim Pidl As Long
PersonalFolder = Space$(260)
SHGetSpecialFolderLocation 0, 5, Pidl
SHGetPathFromIDList Pidl, PersonalFolder
PersonalFolder = Left$(PersonalFolder, _
InStr(1, PersonalFolder, vbNullChar) - 1)
End Function
Sub testme()
MsgBox PersonalFolder
End Sub

Once you have the Mydocuments folder path, you can use mkdir to great a
directory below that.
 
Rob,

Just guessing, but are you trying to modify one Excel file via another??
If so, why not use:
ThisWorkbook.Path

If both files are in the same directory (regardless of the what it's named)
it should work.

John
 
Thanks for that Tom,
With a bit of fiddling I may get this to work. I may have to come back to
you yet, and get some more advice. I'm not sure how to go about ......
"do the mkdir to great a directory below that".
But will have a go first.

Rob

Tom Ogilvy said:
This API code was copied out from a post by Laurent Longre.
Click to expand...
Option Explicit
Private Declare Function SHGetSpecialFolderLocation Lib "Shell32" _
(ByVal hwnd As Long, ByVal nFolder As Long, ppidl As Long) As Long
Private Declare Function SHGetPathFromIDList Lib "Shell32" _
(ByVal Pidl As Long, ByVal pszPath As String) As Long

Function PersonalFolder() As String
Dim Pidl As Long
PersonalFolder = Space$(260)
SHGetSpecialFolderLocation 0, 5, Pidl
SHGetPathFromIDList Pidl, PersonalFolder
PersonalFolder = Left$(PersonalFolder, _
InStr(1, PersonalFolder, vbNullChar) - 1)
End Function
Sub testme()
MsgBox PersonalFolder
End Sub
 
No John, I'm trying to create a folder in My Documents to save the Excel
file to so that anyone using the Excel file on any windows system can
retrieve it from a specifically named folder that the VB code creates.
Rob
 
Back
Top