32 bit macro converted to 64 bit

[tstroh]

I have a macro I found on the internet many years ago and have found many uses for it.
It collects a basic listing of file names and paths.

I'm using Office 365 Pro Plus, Excel 32 bit.
I would like some people in another office to collect information for me but they are using Office 365 Pro Plus, Excel 64 bit.
I do not have 64bit to test it on.

Is there a way to install both 64 bit and 32 version of Excel and choose which I use?

I've converted a couple of lines in the macro from info I found on the internet but it still doesn't run.
Any ideas?

Excel File Attached

Please Login or Register  to view this content.

[Leith Ross]

Hello tstroh,

The simple answer to your question is "Yes, it can be written to work on both platforms." I have not had time to examine all the API calls to see if any 32 bit versions have been changed or have been dropped or do not exist in the 64 bit environment. Unless you know what you are doing, I strongly suggest you not make changes to the code. You can crash your system or even corrupt it.

I will look the code over closely and set it up for either 32 bit or 64 bit compilation.

[tstroh]

Thanks Leith

I've created a lot of MS Access VBA back in the '90s and early 2000 and a little Excel but am rusty. I noticed I did this in 2008.
Most things I have to find a reference on the web somewhere nowadays.

[CK76]

It collects a basic listing of file names and paths.

At quick glance, you don't need API call for what you are doing. Why not just use "Scripting.FileSystemObject", along with DIR() function?

It will be much safer to use and will work on both 32 bit & 64 bit Office.

[CK76]

If API is desired. Below is good reference site for 32/64 bit compatibility for API calls.

https://jkp-ads.com/Articles/apideclarations.asp

[tstroh]

I couldn't begin to tell why it's the way it is. It's been a long time since I've needed to look at this code.

I looked at the JKP web site and change the Declare lines.

'32 bit code
'Declare Function SHGetPathFromIDList Lib "shell32.dll" _
'Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

'Declare Function SHBrowseForFolder Lib "shell32.dll" _
'Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

'64 bit code
Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As LongPtr, ByVal pszPath As String) As Long

Declare PtrSafe Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As LongPtr



That then forces a dim of a variable to fail.
I think it has something to do with the declare type and my var type but I don't know. I've tried various combinations but it either crashes excel or has a type mismatch.

[Leith Ross]

Hello CK76,

Please do not refer the poster to another site.

[Leith Ross]

Hello tstroh,

There are some issues around the SHGetPathFromIDList call. This works well on the older 32 bit systems because all files were physical. On the newer 64 bit systems files can be physical or virtual. If you don't know how to detect the difference, it will crash your system.

[Leith Ross]

Hello tstroh,

I am still reading the documentation about on the API calls. This could take some time to get through. I agree with CK76 that using other non API methods would be safer and faster. I believe this code was written about the time of Windows 2000. There was no browse for folder and file options in VBA5. Using the API was the only way at that time to generate a user interface for browsing files and folders without creating a UserForm.

[CK76]

Here's sample code for non API method of grabbing file names from folder, & subfolders.

GetFiles is sub that collects list of files. You'd call it through FiletoSheet sub. 2nd argument left out or set to false will only get files from selected folder.

Please Login or Register  to view this content.

EDIT: As Leith noted, playing with API when you are not familiar with it, can cause unforeseen problems, best to avoid using the link provided in my post without reading through full documentation on the API.

[rorya]

Hello CK76,

Please do not refer the poster to another site.

Out of curiosity, on what basis do you object to a link to Jan Karel's site? There must be thousands of such links on this site already.

[CK76]

It's to prevent OP from accidentally implementing from the site without knowing full details of issue(s) associated with each API, such as the one mentioned by Leith on post #8.

It was my bad, I should have realized OP had not used VBA in a while and this could be dangerous.

[rorya]

How do you get that from “ Please do not refer the poster to another site.”??

[Leith Ross]

Hello Rory,

I simply did not want the poster wandering away from the forum to explore other options and become distracted until we had time to provide some meaningful feedback.

[tstroh]

works perfectly

thank you

[CK76]

You are welcome.

If you are satisfied with the answer provided, please mark the thread as solved by using thread tool found at top of your initial post.