Compile error: User-defined type not defined

[crayroge]

Hi, I am new to VBA scripting and I am running a form in access to pull a file from a folder. However because a corrupted database, I export the form to a new data base and now I am getting this error:
"Compile error: User-defined type not defined". Is something wrong with this logic? I just want this setup to where the folder name can be variable and that the file name can be variable as well.

Option Compare Database


Function GetFolder(strPath As String, strPrompt As String) As String
' [URL='http://www.mrexcel.com/forum/excel-questions/294728-browse-folder-visual-basic-applications.html']Browse for a folder in VBA[/URL]

    Dim fldr As FileDialog
   
   
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    'msoFileDialogFilePicker
    sInitDir = CurDir ' Store initial directory
   
    With fldr
        .Title = strPrompt
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    ChDrive sInitDir ' Return to the Initial Drive
    ChDir sInitDir ' Resets directory for Initial Drive

    Set fldr = Nothing

End Function


Sub Import_Files()


          
     Dim strFolder As String
     Dim varFile As Variant
    
    
     Dim strFileID As String
    
     Dim colFiles As Collection: Set colFiles = New Collection
    
  

     strFolder = GetFolder("", "Please select the paths of the files") & "\"
    
     varFile = Dir(strFolder & "\*.txt")
     While (varFile <> "")
        colFiles.Add varFile
        varFile = Dir
     Wend
   
     For Each file In colFiles


        'Debug.Print Right(file, 12)
      
              
        strClip = Left(Right(file, 12), 8)
       
        strYear = Left(strClip, 4)
        strMonth = Mid(strClip, 5, 2)
        strDay = Right(strClip, 2)
       
        dtFile = CDate(strMonth & "/" & strDay & "/" & strYear)
    
                      ' File name
        If InStr(1, file, "COL") > 0 Then
            strlocation = "AD3"
        ElseIf InStr(1, file, "PS") > 0 Then
            strlocation = "AD2"
        ElseIf InStr(1, file, "JAX") > 0 Then
                strlocation = "AD1"
        End If
       
        Debug.Print strlocation & "|" & dtFile & "|" & file
        
       DoCmd.TransferText acImportFixed, "AdeccoImport", "AdeccoPayroll", strFolder & "\" & file, False
       
        DoCmd.SetWarnings False
       
        DoCmd.RunSQL "UPDATE AdeccoPayroll SET AdeccoPayroll.FileDate = #" & dtFile & "# WHERE (((AdeccoPayroll.FileDate) Is Null));"
            DoCmd.RunSQL "UPDATE AdeccoPayroll SET AdeccoPayroll.FileName =  '" & strlocation & "' WHERE (((AdeccoPayroll.FileName ) Is Null));"

        DoCmd.SetWarnings True
       
       
       
Next ' File in folder
   
End Sub

 

[rlv01]

It sounds like your VBA project is missing a needed library reference. Do you have the Microsoft Office Object Library checked? (Tools -> Refererences...)

 

[crayroge]

Thanks but how do I open up the file to make that change?

 

[rlv01]

As I said in my previous reply:

Tools -> References...

Look for "Tools" in the VBA editor menu bar.