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
DIR Excel VBA Command Stopped Working in 64 Bit Excel
-
- 2StarLounger
- Posts: 168
- Joined: 03 Oct 2015, 00:30
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: DIR Excel VBA Command Stopped Working in 64 Bit Excel
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 don't have any experience with the 64-bit version myself)
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 168
- Joined: 03 Oct 2015, 00:30
Re: DIR Excel VBA Command Stopped Working in 64 Bit Excel
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 ***************
*************** Excel 64-Bit Version ***************
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
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
-
- 2StarLounger
- Posts: 168
- Joined: 03 Oct 2015, 00:30
Re: DIR Excel VBA Command Stopped Working in 64 Bit Excel
Note: My error. I had a misspelled directory name. Sorry! It worked after I corrected the error. Please cancel this request.
-
- Microsoft MVP
- Posts: 1320
- Joined: 24 May 2013, 15:33
- Location: Warminster, PA
Re: DIR Excel VBA Command Stopped Working in 64 Bit Excel
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.