The operation on the OLE object failed. (1 Viewer)

RoyM

Registered User.
Local time
Today, 05:49
Joined
Mar 12, 2019
Messages
11
Hi, I registered COMDLG32.OCX using regsvr32.exe and was able to add "Microsoft Common Dialog Control 6.0 (SP3)" to the References in MS Access VBA. Yet, when I try to add a "CommonDialog Class" to my Form it raises the error: The operation on the OLE object failed. The OLE server may not be registered. To register the OLE server, reinstall it."

How do I fix this? Also, is there a book or class that teaches how to resolve these kind of problems?

Thanks, Roy
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:49
Joined
Oct 29, 2018
Messages
21,358
Hi Roy. Welcome to the forum. Are you using 64-bit Access? Just curious...
 

RoyM

Registered User.
Local time
Today, 05:49
Joined
Mar 12, 2019
Messages
11
Yes, I am using "Microsoft Access 2016 MSO 64-bit. I don't know how to find out whether the COMDTL32.OCX file I loaded is 32-bit or 64-bit. The OCX file size is 138KB, the DLL file size is 1033KB. I saw that there is a regsvr32.exe in both System32 and SysWOW64. I registered the same OCX file in both directories...because I have NO IDEA WHAT I AM DOING! Thus, the request for a book or class I can take to learn.
 

RoyM

Registered User.
Local time
Today, 05:49
Joined
Mar 12, 2019
Messages
11
I should also mention I am using Windows 10 Build 16299.
 

RoyM

Registered User.
Local time
Today, 05:49
Joined
Mar 12, 2019
Messages
11
So Microsoft is no longer providing a DLL or OCX with methods in it which we can use to open files (MSComDlg.CommonDialog.1), etc.? Are you saying we each need to write our own equivalent to COMDLG32.OCX ? That's beyond my current skill level. I can understand Microsoft abandoning a specific file name, but would then expect them to provide the functionality under a new name.
 

isladogs

MVP / VIP
Local time
Today, 09:49
Joined
Jan 14, 2017
Messages
18,186
That's not what I said.
There are several alternative methods of browsing for a file, save a file, opening a file etc that do work perfectly in 64-bit Access
Depending on what exactly you want, one of us can suggest one or more alternatives that will work at least as well & probably better
 

RoyM

Registered User.
Local time
Today, 05:49
Joined
Mar 12, 2019
Messages
11
Great! I use the common dialog that everyone uses in every windows application to open a file. Please inform me of the new way to use it. Thanks
 

isladogs

MVP / VIP
Local time
Today, 09:49
Joined
Jan 14, 2017
Messages
18,186
I use a generic function called fHandleFile
The code behind it is complex but using it is easy

Copy this code to a standard module - I call it modHandleFiles:

Code:
Option Compare Database
Option Explicit

'=================================================
'API conversaion for 32/64-bit - checked 09/03/2019
#If VBA7 Then
    Public Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, _
        ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr
#Else
    Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
        ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If
'=================================================

Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 2            'Open Maximized
Public Const WIN_MIN = 3            'Open Minimized

Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

Function fHandleFile(stFile As String, lShowHow As Long)

'09/03/2019 - Updated for 64-bit
#If VBA7 Then
    Dim lRet As LongPtr
#Else
    Dim lRet As Long
#End If

Dim varTaskID As Variant
Dim stRet As String

    'First try ShellExecute
    lRet = ShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
            
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function

Now wherever you want to open a file you need one line
Code:
fHandleFile ("Full file path here", WIN_NORMAL)

NOTE: the brackets are optional

It can do lots of different things. For example:
Code:
'Open a file:
  fHandleFile("C:\MendipDataSystems\info.docx",WIN_NORMAL)

'Open a folder:
  fHandleFile("C:\TEMP",WIN_NORMAL)

'Call Email app:
  fHandleFile("mailto:someone@gmail.com",WIN_NORMAL)

'Open URL:
 fHandleFile("http://www.mendipdatasystems.co.uk", WIN_NORMAL)

'Handle Unknown extensions ...pops up the Open with dialog:
 fHandleFile("C:\TEMP\TestThis",WIN_NORMAL)
 
Last edited:

RoyM

Registered User.
Local time
Today, 05:49
Joined
Mar 12, 2019
Messages
11
Interesting. I've never used ShellExecute before. But there's no form?

I am looking for something that is the equivalent to the CommonDialog function in the COMDLG32.OCX file. Isn't there something out there? If Microsoft no longer supplies it, then the entire Windows world would have to write their own? There must be another file that they supply...right???

All I need is the filename that the user selects. At this point, I guess I need to write my own form to browse Directories and select a file. It's going to take me a lot of time and code to figure this out.
 

isladogs

MVP / VIP
Local time
Today, 09:49
Joined
Jan 14, 2017
Messages
18,186
You said you wanted code to open a file so that’s what I supplied. If you had asked for code to browse for a file that’s what I would have posted

I’m out at the moment but will post something later today unless someone else does so first


Sent from my iPhone using Tapatalk
 

RoyM

Registered User.
Local time
Today, 05:49
Joined
Mar 12, 2019
Messages
11
I have discovered that Microsoft Common Item Dialog has superseded Microsoft Common Dialog Box Library. I know that Common Dialog Box Library is inside file ComDlg32.ocx, but I don't know what file contains Microsoft Common Item Dialog. I've tried searching for an hour, but to no avail. Does anyone know?
 

isladogs

MVP / VIP
Local time
Today, 09:49
Joined
Jan 14, 2017
Messages
18,186
The following code will open a standard Windows dialog to the folder specified and show ALL files in that folder. The selected file will then be displayed in a textbox on your form

[RICHCODE]Private Sub cmdBrowse_Click()

'NOTE: this code requires the VBA reference Microsoft Office xx.0 Object library (mso.dll)
'Typical path (for Access 2010): C:\Program Files\Common Files\microsoft shared\OFFICE14\mso.dll


On Error GoTo Err_cmdBrowse_Click

Dim strFilename As String
Dim F As FileDialog

Set F = Application.FileDialog(msoFileDialogFilePicker)
F.title = "Locate the upgrade folder and click on 'Open'" 'modify as required

' Clear out the current filters, and add our own.
F.Filters.Clear
F.Filters.Add "", "*.*" 'this shows all files

' Set the default folder
F.InitialFileName = "C:\" 'modify as required

' Call the Open dialog routine.
F.Show

' Return the path and file name.
strFilename = F.SelectedItems(1)
Me.txtFileName = strFilename

Exit_cmdBrowse_Click:
Exit Sub

Err_cmdBrowse_Click:
'err 5 = user clicked cancel
If Err.Number <> 5 Then
MsgBox "Error " & Err.Number & " in cmdBrowse_Click procedure : " & vbCrLf & Err.description
End If
Resume Exit_cmdBrowse_Click

End Sub[/CODE]

If you only want to see specified file types, change the line F.Filters.Add "", "*.*" to one of the following or similar (adapt as appropriate)

Code:
F.Filters.Add "Access Databases", "*.accdb;*.mdb"
F.Filters.Add "Excel files", "*.xls;*.xlsx"
F.Filters.Add "Text files", "*.txt"
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:49
Joined
May 21, 2018
Messages
8,463
I think you forgot to mention the most important thing for that to work.
You need a reference to Microsoft Office 16.0 Library
 

RoyM

Registered User.
Local time
Today, 05:49
Joined
Mar 12, 2019
Messages
11
I can work with that! Thanks isladogs and MajP
 

isladogs

MVP / VIP
Local time
Today, 09:49
Joined
Jan 14, 2017
Messages
18,186
Thanks - you are indeed correct
I've now added a comment to that effect in the code

Roy - that reference may not appear in the references list so you'll need to browse for the file MSO.DLL. The path will be similar to this depending on your Office version & bitness



NOTE: I've also deleted an End If line that was left in my code in error
 

Attachments

  • Capture.PNG
    Capture.PNG
    12.1 KB · Views: 519

Cliff67

Registered User.
Local time
Today, 02:49
Joined
Oct 16, 2018
Messages
175
The following code will open a standard Windows dialog to the folder specified and show ALL files in that folder. The selected file will then be displayed in a textbox on your form

Code:
Private Sub cmdBrowse_Click()

[COLOR="SeaGreen"]'NOTE: this code requires the VBA reference Microsoft Office xx.0 Object library (mso.dll)
'Typical path (for Access 2010): C:\Program Files\Common Files\microsoft shared\OFFICE14\mso.dll
[/COLOR]

On Error GoTo Err_cmdBrowse_Click

    Dim strFilename As String
    Dim F As FileDialog

    Set F = Application.FileDialog(msoFileDialogFilePicker)
    F.title = "Locate the upgrade folder and click on 'Open'" [COLOR="seagreen"] 'modify as required[/COLOR]
   
    ' Clear out the current filters, and add our own.
      F.Filters.Clear
      F.Filters.Add "", "*.*" [COLOR="SeaGreen"] 'this shows all files[/COLOR]
     
    ' Set the default folder
            F.InitialFileName = "C:\" [COLOR="seagreen"] 'modify as required[/COLOR]
      
    ' Call the Open dialog routine.
      F.Show

    ' Return the path and file name.
      strFilename = F.SelectedItems(1)
      Me.txtFileName = strFilename

Exit_cmdBrowse_Click:
    Exit Sub

Err_cmdBrowse_Click:
    'err 5 = user clicked cancel
    If Err.Number <> 5 Then
        MsgBox "Error " & Err.Number & " in cmdBrowse_Click procedure : " & vbCrLf & Err.description
    End If
    Resume Exit_cmdBrowse_Click
   
End Sub

If you only want to see specified file types, change the line F.Filters.Add "", "*.*" to one of the following or similar (adapt as appropriate)

Code:
F.Filters.Add "Access Databases", "*.accdb;*.mdb"
F.Filters.Add "Excel files", "*.xls;*.xlsx"
F.Filters.Add "Text files", "*.txt"
could this be used to select a folder too?
 

isladogs

MVP / VIP
Local time
Today, 09:49
Joined
Jan 14, 2017
Messages
18,186
could this be used to select a folder too?
Yes...with minor changes.
Use Application.FileDialog msoFileDialogFolderPicker instead.
You should be able to find examples online if needed
 

Cliff67

Registered User.
Local time
Today, 02:49
Joined
Oct 16, 2018
Messages
175
Yes...with minor changes.
Use Application.FileDialog msoFileDialogFolderPicker instead.
You should be able to find examples online if needed
Thanks islasdogs, I found that with the autofill that VBA does and it works well.

What I need to do now if figure out how to make a new folder in that view if the destination folder does not exist
 

isladogs

MVP / VIP
Local time
Today, 09:49
Joined
Jan 14, 2017
Messages
18,186
Its easy enough to check if a folder exists e.g use Dir and then create a new folder if needed.
I'm answering on my phone so can't provide code right now but a forum or Google search should get what you need.
 

Users who are viewing this thread

Top Bottom