Ribbon not loading because of now using 64bit Office (1 Viewer)

ITguy1981

Registered User.
Local time
Today, 06:54
Joined
Aug 24, 2011
Messages
137
I'm currently using a custom ribbon that gets a macro error when any of the buttons are clicked. It seems the issue is due to using 64 bit Office now. I've had help with the previous code so I have no idea what needs changed or how to fix the issue.

Current Code:

Private Const VER_PLATFORM_WIN32_NT = 2
Private Type OSVERSIONINFO
dwOSVersionInfoSize As Long
dwMajorVersion As Long
dwMinorVersion As Long
dwBuildNumber As Long
dwPlatformId As Long
szCSDVersion As String * 128
End Type

Private Declare Function GetVersionEx Lib "kernel32" Alias "GetVersionExA" _
(ByRef lpVersionInformation As OSVERSIONINFO) As Long
Private Declare Function GetFileNameFromBrowseW Lib "shell32" Alias "#63" _
(ByVal hwndOwner As Long, _
ByVal lpstrFile As Long, _
ByVal nMaxFile As Long, _
ByVal lpstrInitialDir As Long, _
ByVal lpstrDefExt As Long, _
ByVal lpstrFilter As Long, _
ByVal lpstrTitle As Long) As Long
Private Declare Function GetFileNameFromBrowseA Lib "shell32" Alias "#63" _
(ByVal hwndOwner As Long, _
ByVal lpstrFile As String, _
ByVal nMaxFile As Long, _
ByVal lpstrInitialDir As String, _
ByVal lpstrDefExt As String, _
ByVal lpstrFilter As String, _
ByVal lpstrTitle As String) As Long
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:54
Joined
Oct 29, 2018
Messages
21,357
Hi. First, you'll need to add PtrSafe to all API declarations. Then, you may have to change some data types as well. Here's some references for you to consider: GetVersionEx
 

ITguy1981

Registered User.
Local time
Today, 06:54
Joined
Aug 24, 2011
Messages
137
I was able to resolve the issue by using an If Statement and using the SafePtr option for VBA7. Do you have any insight on my next issue which is a Type mismatch for StrPtr. I'm sure it's related to the same thing, but not sure how to fix it.

Public Function BrowseFiles()
Dim sSave As String

sSave = Space(255)
'If we're on WinNT, call the unicode version of the function
'If IsWinNT Then
GetFileNameFromBrowseW Screen.ActiveForm.hwnd, _
StrPtr(sSave), _
255, _
StrPtr("c:"), _
StrPtr("xlsx"), _
StrPtr("Excel files(*.xlsx)" + Chr$(0) + "*.xlsx" + Chr$(0) + _
"All files (*.*)" + Chr$(0) + "*.*" + Chr$(0)), _
StrPtr("The Title")
'If we're not on WinNT, call the ANSI version of the function
'Else
'GetFileNameFromBrowseA Screen.ActiveForm.hwnd, _
sSave, _
255, _
"c:", _
"txt", _
"All files (*.*)" + Chr$(0) + "*.*" + Chr$(0) + _
"Text files (*.txt)" + Chr$(0) + "*.txt" + Chr$(0), _
"The Title"
'End If

BrowseFiles = Trim(Replace(sSave, Chr$(0), " "))

End Function
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:54
Joined
Oct 29, 2018
Messages
21,357
Hi. What does the StrPtr() function look like?
 

ITguy1981

Registered User.
Local time
Today, 06:54
Joined
Aug 24, 2011
Messages
137
Basically I have a button that on click runs

Private Sub ImportBrowse_Click()
Me!BrowseTextBox = BrowseFiles()


It opens windows explorer to browse for a file. It works in 32bit Office.
--------------------------------

Public Function BrowseFiles()
Dim sSave As String

sSave = Space(255)
'If we're on WinNT, call the unicode version of the function
'If IsWinNT Then
GetFileNameFromBrowseW Screen.ActiveForm.hwnd, _
StrPtr(sSave), _
255, _
StrPtr("c:"), _
StrPtr("xlsx"), _
StrPtr("Excel files(*.xlsx)" + Chr$(0) + "*.xlsx" + Chr$(0) + _
"All files (*.*)" + Chr$(0) + "*.*" + Chr$(0)), _
StrPtr("The Title")
'If we're not on WinNT, call the ANSI version of the function
'Else
'GetFileNameFromBrowseA Screen.ActiveForm.hwnd, _
sSave, _
255, _
"c:", _
"txt", _
"All files (*.*)" + Chr$(0) + "*.*" + Chr$(0) + _
"Text files (*.txt)" + Chr$(0) + "*.txt" + Chr$(0), _
"The Title"
'End If

BrowseFiles = Trim(Replace(sSave, Chr$(0), " "))

End Function
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:54
Joined
Oct 29, 2018
Messages
21,357
Hi. If that's all you need (browse for files), then you might be able to avoid this problem if you don't use API and just use the FileDialog object instead.
 

ITguy1981

Registered User.
Local time
Today, 06:54
Joined
Aug 24, 2011
Messages
137
They way it currently is it opens explorer and when you choose the file it saves the path\name in the text box. The user will browse and select an excel file. It puts the excel file path in to the box. Then the user can click update which will then run a procedure to update another table using the selected spreadsheet.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:54
Joined
Oct 29, 2018
Messages
21,357
They way it currently is it opens explorer and when you choose the file it saves the path\name in the text box. The user will browse and select an excel file. It puts the excel file path in to the box. Then the user can click update which will then run a procedure to update another table using the selected spreadsheet.

Right, which the FileDialog object can also do, and you avoid problems when using 64-bit Office.
 

ITguy1981

Registered User.
Local time
Today, 06:54
Joined
Aug 24, 2011
Messages
137
If I use the code you posted for explorer I can select the file, but it does not insert the directory location after selecting the file and open
 

ITguy1981

Registered User.
Local time
Today, 06:54
Joined
Aug 24, 2011
Messages
137
I've tried this code and the file picker opens, but I need it to fill my text box with the file path of the file chosen.

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
If .Show Then
selectFile = .SelectedItems(1)
Else
End
End If
End With
Set fd = Nothing
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:54
Joined
Oct 29, 2018
Messages
21,357
I've tried this code and the file picker opens, but I need it to fill my text box with the file path of the file chosen.

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
If .Show Then
selectFile = .SelectedItems(1)
Else
End
End If
End With
Set fd = Nothing
Hi. If you only want the user to select a folder, then maybe you should use msoFileDialogFolderPicker instead of msoFileDialogFilePicker. Otherwise, you can parse the filespec returned by the FileDialog object to strip out the filename. Here's one possible example:
Code:
selectFile=Left(.SelectedItems(1),InStrRev(.SelectedItems(1),"\")-1)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:54
Joined
Oct 29, 2018
Messages
21,357
I need the user to select a .xlsx file.
And again, that's not a problem with the FileDialog object and you avoid the issue with using API on 64-bit systems. Try this:
Code:
Dim fd As Object
Set fd = Application.FileDialog(3)
With fd
    If .Show Then
        MsgBox .SelectedItems(1)
    End If
End With
Set fd = Nothing
 

Users who are viewing this thread

Top Bottom