Solved

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

Posted on 2014-11-03
24
469 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 34

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 34

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 34

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 34

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 34

Expert Comment

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

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

691 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