Solved

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

Posted on 2014-11-03
24
445 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 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

733 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