?
Solved

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

Posted on 2014-11-03
24
Medium Priority
?
493 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 46

Accepted Solution

by:
aikimark earned 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 46

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 46

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 46

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 46

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 46

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 46

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

800 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