Ошибка access user defined type not defined

I’m receiving a compile error on the following line of code:

Dim oXL As Excel.Application

The code is in VB in MS Access 2007. The line above is the beginning of a segment to generate an MS Excel file. The obvious answer to me was to ensure that the «Microsoft Office 12.0 Object Library» is checked under Tools > References. I’ve done this but the error persists. Does Excel need to be installed side-by-side for this to work? What have I done wrong? Thanks in advance.

Tim Williams

146k8 gold badges94 silver badges120 bronze badges

asked Jul 19, 2012 at 21:10

You need to reference Microsoft Excel 12.0 Object Library or use late binding. Late binding is almost always necessary if you will be sharing your project with users who may have different versions of Excel installed.

For late binding, you would instead do:

Dim oXL as object
Set oXL = CreateObject("Excel.Application")

Then your code should work as expected, without the need to make the reference… assuming you aren’t using any other Excel specific values or objects.

answered Jul 19, 2012 at 21:16

DanielDaniel

12.9k2 gold badges35 silver badges60 bronze badges

Источник

Permalink

Cannot retrieve contributors at this time

title	keywords	f1_keywords	ms.prod	ms.assetid	ms.date	ms.localizationpriority
User-defined type not defined (VBA)	vblr6.chm1011292	vblr6.chm1011292	office	60e0da5e-c498-7a2f-46c6-c09d59fc607a	12/27/2018	high

You can create your own data types in Visual Basic, but they must be defined first in a Type…End Type statement or in a properly registered object library or type library. This error has the following causes and solutions:

You tried to declare a variable or argument with an undefined data type or you specified an unknown class or object.

Use the Type statement in a module to define a new data type. If you are trying to create a reference to a class, the class must be visible to the project. If you are referring to a class in your program, you must have a class module of the specified name in your project. Check the spelling of the type name or name of the object.
The type you want to declare is in another module but has been declared Private. Move the definition of the type to a standard module where it can be Public.
The type is a valid type, but the object library or type library in which it is defined isn’t registered in Visual Basic. Display the References dialog box, and then select the appropriate object library or type library. For example, if you don’t check the Data Access Object in the References dialog box, types like Database, Recordset, and TableDef aren’t recognized and references to them in code cause this error.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

Private Sub doc2docx_Click()
    Dim strFilename As String
    Dim strDocName As String
    Dim strPath As String
    Dim oApp As Object
    Dim oDoc As Object
    Dim fDialog As FileDialog
    Dim intPos As Integer
    Dim strPassword As String
    Dim strWritePassword As String
    Set fDialog = Application.FileDialog(4) ' msoFileDialogFolderPicker
    With fDialog
        .Title = "Select folder and click OK"
        .AllowMultiSelect = False
        .InitialView = msoFileDialogViewList
        If .Show <> -1 Then
            MsgBox "Conversion Cancelled By User", , "List Folder Contents"
            Exit Sub
        End If
        strPath = fDialog.SelectedItems.Item(1)
        If Right(strPath, 1) <> "" Then strPath = strPath + ""
    End With
    Set oApp = CreateObject(Class:="Word.Application")
    If oApp.Documents.Count > 0 Then
        oApp.Documents.Close SaveChanges:=-2 ' wdPromptToSaveChanges
    End If
    If Left(strPath, 1) = Chr(34) Then
        strPath = Mid(strPath, 2, Len(strPath) - 2)
    End If
    strFilename = Dir(strPath & "*.doc")
    strPassword = InputBox("Enter password to open the document")
    strWritePassword = InputBox("Enter password to edit the document")
    Do While strFilename <> ""
        Set oDoc = oApp.Documents.Open(FileName:=strPath & strFilename, _
                                  PasswordDocument:=strPassword, _
                                  WritePasswordDocument:=strWritePassword, _
                                  AddToRecentFiles:=False)
        strDocName = oDoc.FullName
        intPos = InStrRev(strDocName, ".")
        strDocName = Left(strDocName, intPos - 1)
        strDocName = strDocName & ".docx"
        oDoc.SaveAs2 FileName:=strDocName, _
                     FileFormat:=12, _
                     CompatibilityMode:=14
        oDoc.Close SaveChanges:=0 ' wdDoNotSaveChanges
        strFilename = Dir
    Loop
    oApp.Quit
End Sub

My references are in this order;

Visual Basic For Application

Microsoft Access 16.0 Object Library

OLE Animation

Microsoft Office 16.0 Access Database Engine Object Library

Microsoft ActiveX Data Objects 6.0 Library

Microsoft Ward 16.0 Object Library

Thank you for your assistance with his issue.

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Источник

Ошибка access user defined type not defined

See also

А вот еще интересные материалы: