Browse Folders in Access 2010
I've been using the Browse Folders API from the "Access Web" for several years (Access 2003 and 2007). Now I've got a 2007 accdb that is being run in 2010 (64 bit) in a Windows 7 environment. But when the BrowseFolders API is called, Access 2010 locks up and then shuts down.
I've modified the original code as follows to account for the 64 bit processor, as indicated in several other posts on this site, but this has not helped.
#If Win64 = 1 Then
Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias _
"SHGetPathFromIDListA" (ByVal pidl As Long, _
ByVal pszPath As String) As Long
Private Declare PtrSafe Function SHBrowseForFolder Lib "shell32.dll" Alias _
"SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
As Long
#Else
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
"SHGetPathFromIDListA" (ByVal pidl As Long, _
ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
"SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
As Long
#End If
Any ideas how to resolve this?
I've got a built-from-scratch BrowseFolders form that I created using a treeview control, which also works great in 2007, but when I try to open the form in 2010, it gives me an error message that says "there is no object in this control". When I tried to add a new ActiveX treeview control to the form, I couldn't find the treeview in the list of ActiveX controls, so that one doesn't work in 2010 either.
I've modified the original code as follows to account for the 64 bit processor, as indicated in several other posts on this site, but this has not helped.
#If Win64 = 1 Then
Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias _
"SHGetPathFromIDListA" (ByVal pidl As Long, _
ByVal pszPath As String) As Long
Private Declare PtrSafe Function SHBrowseForFolder Lib "shell32.dll" Alias _
"SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
As Long
#Else
Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _
"SHGetPathFromIDListA" (ByVal pidl As Long, _
ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _
"SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _
As Long
#End If
Any ideas how to resolve this?
I've got a built-from-scratch BrowseFolders form that I created using a treeview control, which also works great in 2007, but when I try to open the form in 2010, it gives me an error message that says "there is no object in this control". When I tried to add a new ActiveX treeview control to the form, I couldn't find the treeview in the list of ActiveX controls, so that one doesn't work in 2010 either.
I have never used msaccess on a 64bit platform running 64bit office. I have ran 32bit office 2010 on 64bit Win7 and that works fine. (including your code). I realise that PtrSafe is needed to help run 32bit api's in 64bits but cannot shed any light on why it is misbehaving. Perhaps there is some corruption or conflict elsewhere.
Have you tried creating a new db, with just some test code for your shbrowser dialog? If that works then you know the issue cant be with this bit of code.
There is an alternative with works in 2007 32bit and I assume 64bit also since it requires a reference to the office object library/ It is not as sexy as shbrowserdialog but it requires no api declarations.
Dim fDialog As Office.FileDialog
Dim sFolder As String
Set fDialog = Application.FileDialog(mso
fDialog.Show
sFolder = fDialog.SelectedItems(1)
MsgBox "Selected folder is " & sFolder
Have you tried creating a new db, with just some test code for your shbrowser dialog? If that works then you know the issue cant be with this bit of code.
There is an alternative with works in 2007 32bit and I assume 64bit also since it requires a reference to the office object library/ It is not as sexy as shbrowserdialog but it requires no api declarations.
Dim fDialog As Office.FileDialog
Dim sFolder As String
Set fDialog = Application.FileDialog(mso
fDialog.Show
sFolder = fDialog.SelectedItems(1)
MsgBox "Selected folder is " & sFolder
ok, just had a thought. What if you used the new datatypes? like LongPtr or LongLong ?
eg
Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias _
"SHGetPathFromIDListA" (ByVal pidl As LongPtr, _
ByVal pszPath As String) As LongPtr
the BROWSEINFO type would need changing also (example below is just one change)
hOwner As LongPtr
so you would need to wrap that inside your IF also
eg
Private Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias _
"SHGetPathFromIDListA" (ByVal pidl As LongPtr, _
ByVal pszPath As String) As LongPtr
the BROWSEINFO type would need changing also (example below is just one change)
hOwner As LongPtr
so you would need to wrap that inside your IF also
Have you thought of using the Shell.BrowseForFolder method instead?
http://msdn.microsoft.com/en-us/library/bb774065(v=vs.85).aspx
http://msdn.microsoft.com/en-us/library/bb774065(v=vs.85).aspx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@nick/rocki
My client has a number of Win7 (64 bit) computers and recently purchased some BI software that apparently requires A2010 (64 bit), so I downloaded the 64 bit A2010 and installed it on my new laptop. Most of their computers are XP or Win7 running 32 bit Access 2007, so my primary development environment for them is 32 bit 2007. But in an effort to avoid having two separate versions of the application, have used the conditional compilation for a couple of the other API calls, and that has worked well.
I've used the fileDialog in the past, but stopped using it after finding the API call. I'll take a look at modifying some of the other parameters in that API.
@Jez:
Was not aware of the shell.BrowseForFolder option. I'll take a look at it as well.
My client has a number of Win7 (64 bit) computers and recently purchased some BI software that apparently requires A2010 (64 bit), so I downloaded the 64 bit A2010 and installed it on my new laptop. Most of their computers are XP or Win7 running 32 bit Access 2007, so my primary development environment for them is 32 bit 2007. But in an effort to avoid having two separate versions of the application, have used the conditional compilation for a couple of the other API calls, and that has worked well.
I've used the fileDialog in the past, but stopped using it after finding the API call. I'll take a look at modifying some of the other parameters in that API.
@Jez:
Was not aware of the shell.BrowseForFolder option. I'll take a look at it as well.
@fyed
I am afraid I don't have much to teach here, just a lot to learn
can you post some resources on <conditional compilation>
This is a pin that all of us are going to have to pull some day.
May aw well start getting my feet wet early
I am afraid I don't have much to teach here, just a lot to learn
can you post some resources on <conditional compilation>
This is a pin that all of us are going to have to pull some day.
May aw well start getting my feet wet early
@fyed
Thank you for putting in the phrase <conditional compilation>
There's stuff about it here
http://office.microsoft.com/en-us/access-help/HV080755465.aspx
One of the biggest PITA's I encounter in a mixed environment is FilterOnLoad.
2003 reopens forms with the filter off. 2007/2010 not so much.
I've handled it by putting Me.filter = "" in the close event of every form because, while I could test for version, I didn't know how to get the debugger not to hate properties that didn't exist downlevel.
Thnak you!
Thank you for putting in the phrase <conditional compilation>
There's stuff about it here
http://office.microsoft.com/en-us/access-help/HV080755465.aspx
One of the biggest PITA's I encounter in a mixed environment is FilterOnLoad.
2003 reopens forms with the filter off. 2007/2010 not so much.
I've handled it by putting Me.filter = "" in the close event of every form because, while I could test for version, I didn't know how to get the debugger not to hate properties that didn't exist downlevel.
Thnak you!
Reading your original post again.
<I couldn't find the treeview in the list of ActiveX controls>
MS deprecated the Calendar control--which was a PITA to me.
I got it working in Access 2010 with steps noted here
https://www.experts-exchange.com/questions/26992166/MS-Access-2010-need-MS-Access-2003-Calendar-Control.html?cid=1572&anchorAnswerId=35728584#a35728584
Perhaps the same attack may work for this?
Find and copy the dll, register it, and save out the registry keys from a system after you get it going to make future deployment simple?
<I couldn't find the treeview in the list of ActiveX controls>
MS deprecated the Calendar control--which was a PITA to me.
I got it working in Access 2010 with steps noted here
https://www.experts-exchange.com/questions/26992166/MS-Access-2010-need-MS-Access-2003-Calendar-Control.html?cid=1572&anchorAnswerId=35728584#a35728584
Perhaps the same attack may work for this?
Find and copy the dll, register it, and save out the registry keys from a system after you get it going to make future deployment simple?
You'll have to confirm this for yourself, but I reckon you'll find that the Shell.BrowseForFolder method (in the Microsoft Shell Controls And Automation library, SHELL32.dll) works unaltered for all Access/Windows versions.
ASKER
JezWalters,
That worked great, in both 2007 and 2010, and avoids the reference necessary for the fileDialog method
That worked great, in both 2007 and 2010, and avoids the reference necessary for the fileDialog method
I think you'd best clarify the exact nature of your 64 bit environment.
I run 32 bit Office on Window 7 Ultimate 64 bit.
I don't need the PtrSafe part of the declarations and all my API code runs completely unchanged from my XP-32 bit /Access 2003 32-bit production code
You are running 64 bit Office on 64 bit Windows, correct?