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

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"
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pdvsaProject financeAuthor Commented:
the import from Excel to Access works though.
0
aikimarkCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieData ProcessorCommented:
Just curioud, why are you opening the Excel workbook?

You don't need to do that when importing.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

pdvsaProject financeAuthor Commented:
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
aikimarkCommented:
You need to quit the application before you set the variable to nothing.
xlApp.Quit

Open in new window

0
pdvsaProject financeAuthor Commented:
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
pdvsaProject financeAuthor Commented:
that was a question...forgot the ?
0
aikimarkCommented:
I wrote "before you set the variable to nothing."
Set xlApp = Nothing
0
NorieData ProcessorCommented:
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
pdvsaProject financeAuthor Commented:
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
aikimarkCommented:
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
pdvsaProject financeAuthor Commented:
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
pdvsaProject financeAuthor Commented:
hang on I didnt see that post...
0
NorieData ProcessorCommented:
If you really want the code to open the workbook you should also close the workbook before quitting Excel.
0
pdvsaProject financeAuthor Commented:
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
pdvsaProject financeAuthor Commented:
Will the above code now prevent the file from opening?  I dont need to see the file.
0
aikimarkCommented:
It doesn't prevent Excel from opening.  It makes sure that it cleans up after it is finished processing the workbook
0
NorieData ProcessorCommented:
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
pdvsaProject financeAuthor Commented:
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
aikimarkCommented:
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
pdvsaProject financeAuthor Commented:
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
aikimarkCommented:
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
pdvsaProject financeAuthor Commented:
thank you very much
0
NorieData ProcessorCommented:
I'm confused.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.