Solved

Compile Error - Import Excel - "User Defined Type not Defined"

Posted on 2014-11-03
24
423 Views
Last Modified: 2014-11-03
Experts, why would I get a compile error on :
    Set xlApp = New Excel.Application

the compile error I get is "User Defined Type not Defined"

Option Compare Database
Option Explicit

Private Sub cmdImport_Click()

    CurrentDb.Execute "delete * from [Import_Access]"
    
     Dim strName As String
    Dim xlApp As Object
    Dim xlWB As Object
    Set xlApp = New Excel.Application
    With xlApp
        .Visible = False
        Set xlWB = .Workbooks.Open("J:\Corporate\Treasury\Compliance\Reporting and Key Dates for Compliance\Compliance Covenants - Financing and JVA.xls", , False)
        strName = "Deliverables"
    End With
    Set xlWB = Nothing
    Set xlApp = Nothing
   DoCmd.TransferSpreadsheet acImport, , "Import_Access", "J:\Corporate\Treasury\Compliance\Reporting and Key Dates for Compliance\Compliance Covenants - Financing and JVA.xls", True, strName & "!"

Open in new window


End Sub


"User Defined Type not Defined"
0
Comment
Question by:pdvsa
  • 12
  • 7
  • 5
24 Comments
 

Author Comment

by:pdvsa
ID: 40419602
the import from Excel to Access works though.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40419652
You either need to add an Excel reference or use this statement to late-bind instantiate your xlAPP variable.
Set xlApp = Createobject("Excel.Application")

Open in new window

0
 
LVL 33

Expert Comment

by:Norie
ID: 40419894
Just curioud, why are you opening the Excel workbook?

You don't need to do that when importing.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:pdvsa
ID: 40419915
aikimark:  that works.

imnorie:  Yes, I dont need to.  I was wondering how I can prevent it from opening?  When it opens, it doesnt release the file and I have issues.  I would like to remove the line but not sure what exactly to remove.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40419957
You need to quit the application before you set the variable to nothing.
xlApp.Quit

Open in new window

0
 

Author Comment

by:pdvsa
ID: 40419986
like this:  
Private Sub cmdImport_Click()

    CurrentDb.Execute "delete * from [Import_Access]"
   
     Dim strName As String
    Dim xlApp As Object
    Dim xlWB As Object
    'Set xlApp = New Excel.Application
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = False
        Set xlWB = .Workbooks.Open("J:\Corporate\Treasury\Compliance\Reporting and Key Dates for Compliance\Compliance Covenants - Financing and JVA.xls", , False)
        strName = "Deliverables"
    End With
    Set xlWB = Nothing
    Set xlApp = Nothing
   DoCmd.TransferSpreadsheet acImport, , "Import_Access", "J:\Corporate\Treasury\Compliance\Reporting and Key Dates for Compliance\Compliance Covenants - Financing and JVA.xls", True, strName & "!"
xlApp.Quit

End Sub
0
 

Author Comment

by:pdvsa
ID: 40419987
that was a question...forgot the ?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40419989
I wrote "before you set the variable to nothing."
Set xlApp = Nothing
0
 
LVL 33

Expert Comment

by:Norie
ID: 40420162
To run the delete query and do the import all you need are the first and last lines of the code, oh and the declaration of strName.
Dim strName As String

    CurrentDb.Execute "delete * from [Import_Access]"
    strName = "Deliverables"
    
    DoCmd.TransferSpreadsheet acImport, , "Import_Access", "J:\Corporate\Treasury\Compliance\Reporting and Key Dates for Compliance\Compliance Covenants - Financing and JVA.xls", True, strName & "!"

Open in new window

0
 

Author Comment

by:pdvsa
ID: 40420222
ARimark: I didn't see you had put a "set" in the code.

I will replace xlapp =nothing to
Set xlapp = nothing

And I shall put it at the end of the code where I show above.

Let me know if that is not what you mean.   Thank you
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40420228
Change this:
    Set xlWB = Nothing
    Set xlApp = Nothing

Open in new window


To this:
    xlApp.Quit
    Set xlWB = Nothing
    Set xlApp = Nothing

Open in new window

0
 

Author Comment

by:pdvsa
ID: 40420240
is this correct?    Imnorie:  I did have the declaration strName = "Deliverables" but further down in the code (see initial post).  In the below post, I moved strName = "Deliverables" to where you instructed me to.  

Option Compare Database
Option Explicit

Private Sub cmdImport_Click()
    Dim strName As String

            CurrentDb.Execute "delete * from [Import_Access]"
           strName = "Deliverables"
    Dim xlApp As Object
    Dim xlWB As Object
    'Set xlApp = New Excel.Application
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = False
        Set xlWB = .Workbooks.Open("J:\Corporate\Treasury\Compliance\Reporting and Key Dates for Compliance\Compliance Covenants - Financing and JVA.xls", , False)
   
    End With
    Set xlWB = Nothing
    Set xlApp = Nothing
   DoCmd.TransferSpreadsheet acImport, , "Import_Access", "J:\Corporate\Treasury\Compliance\Reporting and Key Dates for Compliance\Compliance Covenants - Financing and JVA.xls", True, strName & "!"

Set xlApp = Nothing
xlApp.Quit


End Sub
0
 

Author Comment

by:pdvsa
ID: 40420242
hang on I didnt see that post...
0
 
LVL 33

Expert Comment

by:Norie
ID: 40420243
If you really want the code to open the workbook you should also close the workbook before quitting Excel.
0
 

Author Comment

by:pdvsa
ID: 40420248
this is how I have it now:

Private Sub cmdImport_Click()
    Dim strName As String

            CurrentDb.Execute "delete * from [Import_Access]"
            strName = "Deliverables"
    Dim xlApp As Object
    Dim xlWB As Object
    'Set xlApp = New Excel.Application
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = False
        Set xlWB = .Workbooks.Open("J:\Corporate\Treasury\Compliance\Reporting and Key Dates for Compliance\Compliance Covenants - Financing and JVA.xls", , False)
   
    End With
    Set xlWB = Nothing
    Set xlApp = Nothing
   DoCmd.TransferSpreadsheet acImport, , "Import_Access", "J:\Corporate\Treasury\Compliance\Reporting and Key Dates for Compliance\Compliance Covenants - Financing and JVA.xls", True, strName & "!"

xlApp.Quit
    Set xlWB = Nothing
    Set xlApp = Nothing

End Sub
0
 

Author Comment

by:pdvsa
ID: 40420253
Will the above code now prevent the file from opening?  I dont need to see the file.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40420273
It doesn't prevent Excel from opening.  It makes sure that it cleans up after it is finished processing the workbook
0
 
LVL 33

Expert Comment

by:Norie
ID: 40420298
All this code does is open the workbook, why do you need it?
im xlApp As Object
    Dim xlWB As Object
    'Set xlApp = New Excel.Application
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        .Visible = False
        Set xlWB = .Workbooks.Open("J:\Corporate\Treasury\Compliance\Reporting and Key Dates for Compliance\Compliance Covenants - Financing and JVA.xls", , False)
    
    End With
    Set xlWB = Nothing
    Set xlApp = Nothing

Open in new window

0
 

Author Comment

by:pdvsa
ID: 40420302
if its a simple fix please let me know.  I have wanted to stop opening the file for quite sometime.  I can post another question if need be.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40420312
If all you're doing is importing the Excel data, then you probably don't need to open the workbook.  Just use the TransferSpreadsheet method
0
 

Author Comment

by:pdvsa
ID: 40420321
I shall rem out this line?
Set xlWB = .Workbooks.Open("J:\Corporate\Treasury\Compliance\Reporting and Key Dates for Compliance\Compliance Covenants - Financing and JVA.xls", , False)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40420330
comment everything but these four statements
Dim strName As String
CurrentDb.Execute "delete * from [Import_Access]"
strName = "Deliverables"

 DoCmd.TransferSpreadsheet acImport, , "Import_Access", "J:\Corporate\Treasury\Compliance\Reporting and Key Dates for Compliance\Compliance Covenants - Financing and JVA.xls", True, strName & "!"

Open in new window

0
 

Author Closing Comment

by:pdvsa
ID: 40420365
thank you very much
0
 
LVL 33

Expert Comment

by:Norie
ID: 40420496
I'm confused.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question