32位系统开发的VBA宏在64位环境下无法导入文件故障咨询

The core issue here isn't just the PtrSafe keyword you added to your API declarations—your tagOPENFILENAME struct definition is likely not adapted for 64-bit memory addressing, which is causing the common dialog API calls to fail silently. Here's how to fix it step by step:

1. Update the tagOPENFILENAME Structure for 64-bit #

In 64-bit VBA, pointer-type fields (like window handles, instance handles, and callback pointers) need to use LongPtr instead of Long (since pointers take 8 bytes in 64-bit systems, not 4). Non-pointer integer fields can stay as Long.

Here's the corrected, cross-compatible struct definition:

#If VBA7 Then
    Private Type tagOPENFILENAME
        lStructSize As Long
        hwndOwner As LongPtr       ' Pointer type: use LongPtr for 64-bit
        hInstance As LongPtr       ' Pointer type: use LongPtr for 64-bit
        lpstrFilter As String
        lpstrCustomFilter As String
        nMaxCustFilter As Long
        nFilterIndex As Long
        lpstrFile As String
        nMaxFile As Long
        lpstrFileTitle As String
        nMaxFileTitle As Long
        lpstrInitialDir As String
        lpstrTitle As String
        flags As Long
        nFileOffset As Integer
        nFileExtension As Integer
        lpstrDefExt As String
        lCustData As LongPtr       ' Pointer type: use LongPtr for 64-bit
        lpfnHook As LongPtr        ' Pointer type: use LongPtr for 64-bit
        lpTemplateName As String
    End Type
#Else
    ' Fallback for 32-bit VBA (no LongPtr support)
    Private Type tagOPENFILENAME
        lStructSize As Long
        hwndOwner As Long
        hInstance As Long
        lpstrFilter As String
        lpstrCustomFilter As String
        nMaxCustFilter As Long
        nFilterIndex As Long
        lpstrFile As String
        nMaxFile As Long
        lpstrFileTitle As String
        nMaxFileTitle As Long
        lpstrInitialDir As String
        lpstrTitle As String
        flags As Long
        nFileOffset As Integer
        nFileExtension As Integer
        lpstrDefExt As String
        lCustData As Long
        lpfnHook As Long
        lpTemplateName As String
    End Type
#End If

2. Ensure Correct API Declaration & Function Logic #

Your existing API declarations with PtrSafe are correct for 64-bit, but wrap them in the same #If VBA7 condition to maintain cross-compatibility. Also, fix critical details in your GetOpenFile function:

• Always set .lStructSize to Len(OFN) (don't hardcode values—struct size differs between 32/64-bit)
• Allocate a sufficiently large buffer for lpstrFile
• Use modern dialog flags for better compatibility

Here's a complete, working version of the file import function:

#If VBA7 Then
    Declare PtrSafe Function aht_apiGetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean
    Declare PtrSafe Function aht_apiGetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
    Declare PtrSafe Function CommDlgExtendedError Lib "comdlg32.dll" () As Long
#Else
    Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean
    Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
    Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long
#End If

' Reusable open file dialog function (works on 32/64-bit)
Function GetOpenFile(Optional varDirectory As Variant, Optional varFilter As Variant) As String
    Dim OFN As tagOPENFILENAME
    Dim strFileBuffer As String
    Dim strFilter As String

    ' Set default filter if none provided
    If IsMissing(varFilter) Then
        strFilter = "All Files (*.*)" & vbNullChar & "*.*" & vbNullChar
    Else
        strFilter = varFilter & vbNullChar & varFilter & vbNullChar
    End If

    ' Allocate buffer for selected file path
    strFileBuffer = String$(2048, vbNullChar)

    ' Populate dialog settings
    With OFN
        .lStructSize = Len(OFN)
        .hwndOwner = Application.hwnd
        .lpstrFilter = strFilter
        .lpstrFile = strFileBuffer
        .nMaxFile = Len(strFileBuffer) - 1
        .lpstrFileTitle = String$(256, vbNullChar)
        .nMaxFileTitle = 255
        If Not IsMissing(varDirectory) Then .lpstrInitialDir = varDirectory
        .lpstrTitle = "Select File to Import"
        ' Flags: Use modern explorer-style dialog + enforce valid file/path
        .flags = &H80000 Or &H4 Or &H8 ' OFN_EXPLORER | OFN_FILEMUSTEXIST | OFN_PATHMUSTEXIST
    End With

    ' Trigger the dialog
    If aht_apiGetOpenFileName(OFN) Then
        ' Extract the selected path (trim null terminators)
        GetOpenFile = Left$(OFN.lpstrFile, InStr(OFN.lpstrFile, vbNullChar) - 1)
    Else
        ' Optional: Handle dialog errors
        Dim lngError As Long
        lngError = CommDlgExtendedError
        If lngError <> 0 Then
            MsgBox "File selection failed with error code: " & lngError, vbExclamation
        End If
        GetOpenFile = ""
    End If
End Function

3. Key Notes for Cross-Compatibility #

• The #If VBA7 Then condition lets the code run on both 32-bit (VBA6 and earlier) and 64-bit (VBA7+) systems without modification.
• Never hardcode struct sizes or pointer lengths—use Len() and LongPtr to let VBA handle memory addressing automatically.
• The OFN_EXPLORER flag ensures the dialog uses the modern Windows explorer interface, which is more reliable on newer OS versions.

内容的提问来源于stack exchange，提问作者Rahul