DIR Excel VBA Command Stopped Working in 64 Bit Excel

[richlocus]

Hello:

I just migrated an Excel application from the 32bit to 64 bit version. Now, the DIR command does not pull up the list of files in a directory. I did not change any code except the Declare statement at the top to access the username.

The directory I specify for the DIR command is:
strPathToCustomerInputTemplate = "C:\Users\" & UserNameWindows() & "\Documents\CustomerUpdates_Before\"

After the command executes, the variable strPathToCustomerInputTemplate is as follows:
C:\Users\richl\Documents\CustomerUIpdates_Before\
That is the correct path to the files within the Before directory.

Then I issue the following VBA command to start accessing the files within that directory:
strExcelFileName = Dir(strPathToCustomerInputTemplate, vbNormal)
Do Until strExcelFileName = ""

It doesn't bring up any files. On the 32-bit version, there was no problem.

Ideas?

Thanks,
Rich Locus

[HansV]

Seems a good reason to avoid the 64-bit version of Office...

(I don't have any experience with the 64-bit version myself)

[richlocus]

Unfortunately, my client uses the 64-Bit Version. I made a small test version for the 32-bit and ran it successfully. I copied the exact same code for the 64-bit version. The only change was in the Declare statement. The same failure occurred in the 64-bit test code. Codes and directory structures are identical between the 32-bit and 64-bit. This is a real issue!!

Here are the two test code segments:

*************** Excel 32-Bit Version ***************

Code: Select all

Option Explicit
Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Sub UpdateMonthlyCustomerWorkbooks()
Dim strPathToCustomerInputTemplate As String
Dim strExcelFileName As String
strPathToCustomerInputTemplate = "C:\Users\" & UserNameWindows() & "\Documents\CustomerUpdates_Before\"
strExcelFileName = Dir(strPathToCustomerInputTemplate, vbNormal)
Do Until strExcelFileName = ""
 MsgBox (strExcelFileName)
Loop
End Sub

Function UserNameWindows() As String
    
    Dim lngLen As Long
    Dim strBuffer As String
    
    Const dhcMaxUserName = 255
    
    strBuffer = Space(dhcMaxUserName)
    lngLen = dhcMaxUserName
    If CBool(GetUserName(strBuffer, lngLen)) Then
        UserNameWindows = Left$(strBuffer, lngLen - 1)
    Else
        UserNameWindows = ""
    End If
End Function

*************** Excel 64-Bit Version ***************

Code: Select all

Option Explicit
Private Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
                      (ByVal IpBuffer As String, nSize As Long) As Long

Public Sub UpdateMonthlyCustomerWorkbooks()
Dim strPathToCustomerInputTemplate As String
Dim strExcelFileName As String
strPathToCustomerInputTemplate = "C:\Users\" & UserNameWindows() & "\Documents\CustomerUpdates_Before\"
strExcelFileName = Dir(strPathToCustomerInputTemplate, vbNormal)
Do Until strExcelFileName = ""
 MsgBox (strExcelFileName)
Loop
End Sub

Function UserNameWindows() As String
    
    Dim lngLen As Long
    Dim strBuffer As String
    
    Const dhcMaxUserName = 255
    
    strBuffer = Space(dhcMaxUserName)
    lngLen = dhcMaxUserName
    If CBool(GetUserName(strBuffer, lngLen)) Then
        UserNameWindows = Left$(strBuffer, lngLen - 1)
    Else
        UserNameWindows = ""
    End If
End Function

[richlocus]

Note: My error. I had a misspelled directory name. Sorry! It worked after I corrected the error. Please cancel this request.

[Jay Freedman]

Seems a good reason to avoid the 64-bit version of Office...

(I don't have any experience with the 64-bit version myself)

I've been using 64-bit versions of Office since 2013, and I've had no problems with it either in the UI or in VBA. There have been plenty of annoyances, but no real problems. I do keep a virtual machine running Windows 7 with 32-bit Office 2003, 2007, and 2010 for testing and for answering posts on the Answers forum, but all my daily work is in 64-bit Microsoft 365.

Yes, if you have VBA code that calls Win32 API functions, you need to know how to change the declarations and calls for 64-bit, but that's usually straightforward. Jan Karel Pieterse's page at https://jkp-ads.com/articles/apideclarations.asp is a great help.