Excel 2016 - Compile Error in Hidden Module

[jeevaaviindos]

I'm creating a simple report excel where it merge xls files when user select a folder. It was working perfectly until when i test in different PC, i got " Compile Error in Hidden Module" error. Below is the screenshot of the error.

Below are the MergeSheets module codes that i got from internet.

<code>Option Explicit
Public strPath As String
Public Type SELECTINFO
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

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 SELECTINFO) As Long
Function SelectFolder(Optional Msg) As String
Dim sInfo As SELECTINFO
Dim path As String
Dim r As Long, x As Long, pos As Integer
sInfo.pidlRoot = 0&

If IsMissing(Msg) Then
    sInfo.lpszTitle = "Select your folder."
Else
    sInfo.lpszTitle = Msg
End If

sInfo.ulFlags = &H1

x = SHBrowseForFolder(sInfo)

path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
    pos = InStr(path, Chr$(0))
    SelectFolder = Left(path, pos - 1)
Else
    SelectFolder = ""
End If
End Function

"Merging Part"
Sub MergeExcels()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
Dim RowofCopySheet As Integer

RowofCopySheet = 1 

ThisWB = ActiveWorkbook.Name

path = SelectFolder("Select a folder containing Excel files you want to merge")

Application.EnableEvents = False
Application.ScreenUpdating = False

Set shtDest = ActiveWorkbook.Sheets(1)
Filename = Dir(path & "\*.xls", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
    If Not Filename = ThisWB Then
        Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
        Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
        Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
        CopyRng.Copy Dest
        Wkb.Close False
    End If

    Filename = Dir()
Loop

Range("A1").Select

Application.EnableEvents = True
Application.ScreenUpdating = True

MsgBox "Files Merged!"
End Sub</code>

I managed find the source of problem which is the incompatibility of 32bit and 64bit OS. And i managed to find the solution where i need to include PtrSafe into my declare part. After i include the "PtrSafe" as below, the error is not showing up anymore.

<code>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 SELECTINFO) As Long</code>

However when i execute the code, my Excel program crash.
Although this code is created for 32bit OS, it is working when i developing on my PC which is 64bit version. I even tried the same code on 2nd PC which also 64bit. However when i tried on 3rd PC which also 64bit, the error showed up.
Hope you give can give idea for this. Thanks.


**Update

-Seems that the 3rd pc is using Office 64bits thus not working.
-I even changed all the pointers to LongPtr. after change i get new error which is "Run-time error '1004' Cannot run macro".

 

[RedBeard]

In your updated code for 64bit office, you've set the functions to Private, is this intentional?

I assume you've read this: https://msdn.microsoft.com/en-us/library/office/gg264421.aspx

 

[jeevaaviindos]

In your updated code for 64bit office, you've set the functions to Private, is this intentional?

I assume you've read this: https://msdn.microsoft.com/en-us/library/office/gg264421.aspx

Yea i got that from somewhere in web. However if i remove the Private, the result is same. Yeap i read from that link.

 

[Jaafar Tribak]

See if this works for you ... modifications in red :

Option Explicit

Type SELECTINFO
        [COLOR=#ff0000]hOwner As LongPtr[/COLOR]
        [COLOR=#ff0000]pidlRoot As LongPtr[/COLOR]
        pszDisplayName As String
        lpszTitle As String
        ulFlags As Long
        [COLOR=#ff0000]lpfn As LongPtr[/COLOR]
        [COLOR=#ff0000]lParam As LongPtr[/COLOR]
        iImage As Long
End Type


Declare PtrSafe Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" _
        (ByVal pidl As LongPtr, ByVal pszPath As String) As [COLOR=#ff0000]LongPtr[/COLOR]
        
Declare PtrSafe Function SHBrowseForFolder Lib "shell32.dll" _
                               Alias "SHBrowseForFolderA" (lpBrowseInfo As SELECTINFO) As Long
                               
Function SelectFolder(Optional Msg) As String
Dim sInfo As SELECTINFO
Dim path As String
Dim [COLOR=#ff0000]r As LongPtr,[/COLOR] x As Long, pos As Integer
sInfo.pidlRoot = 0&


If IsMissing(Msg) Then
    sInfo.lpszTitle = "Select your folder."
Else
    sInfo.lpszTitle = Msg
End If


sInfo.ulFlags = &H1


x = SHBrowseForFolder(sInfo)


path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
    pos = InStr(path, Chr$(0))
    SelectFolder = Left(path, pos - 1)
Else
    SelectFolder = ""
End If
End Function


Sub MergeExcels()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
Dim RowofCopySheet As Integer


RowofCopySheet = 1


ThisWB = ActiveWorkbook.Name


path = SelectFolder("Select a folder containing Excel files you want to merge")


Application.EnableEvents = False
Application.ScreenUpdating = False


Set shtDest = ActiveWorkbook.Sheets(1)
Filename = Dir(path & "\*.xls", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
    If Not Filename = ThisWB Then
        Set Wkb = Workbooks.Open(Filename:=path & "" & Filename)
        Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
        Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
        CopyRng.Copy Dest
        Wkb.Close False
    End If


    Filename = Dir()
Loop


Range("A1").Select


Application.EnableEvents = True
Application.ScreenUpdating = True


MsgBox "Files Merged!"
End Sub