MS Access error 424 Object Required

When I run this code, I receive an error "424 Object Required" - Help!

Set xlwb_Import = xlApp.Workbooks.Open(strImportFileName)

Set xlws_New = xlwb_Import.Worksheets.Add
xlws_New.Name = ("temp")
Set xlws_Import = xlwb_Import.Worksheets("Export")
Set xlws_New = xlwb_Import.Worksheets("Temp")

xlws_Import.Activate
xlws_Import.Range("A1:S500").Copy
xlws_New.Activate
xlws_New.Range("A1:S500").PasteSpecial.Values
Marilync1266Asked:
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.

Dale FyeCommented:

Have you defined your objects?

Dim xlApp as Excel.Application  'or Object
Dim xlwb_Import as Excel.Workbook 'or Object
Dim xlws_New as Excel.Worksheet 'or Object

0
Dale FyeCommented:
Once you have defined the objects, then you need to define xlApp

Set xlApp = CreateObject("Excel.Application")
0
Marilync1266Author Commented:
This is how I have them defined - maybe I'm  missing something
Dim xlApp As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlwb_Import As Excel.Workbook
Dim xlws As Excel.Worksheet
Dim xlws_New As Excel.Worksheet
0
Ultimate Tool Kit for Technology Solution Provider

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 now.

Marilync1266Author Commented:
I have that
Set xlApp = New Excel.Application
0
Rey Obrero (Capricorn1)Commented:


Dim xlApp As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlwb_Import As Excel.Workbook
Dim xlws As Excel.Worksheet
Dim xlws_New As Excel.Worksheet

set xlApp as new excel.application  'add this line


Set xlwb_Import = xlApp.Workbooks.Open(strImportFileName)

Set xlws_New = xlwb_Import.Worksheets.Add
xlws_New.Name = ("temp")
Set xlws_Import = xlwb_Import.Worksheets("Export")
Set xlws_New = xlwb_Import.Worksheets("Temp")

xlws_Import.Activate
xlws_Import.Range("A1:S500").Copy
xlws_New.Activate
xlws_New.Range("A1:S500").PasteSpecial.Values
0
Rey Obrero (Capricorn1)Commented:
oops sorry

set xlApp = new excel.application
0
Rey Obrero (Capricorn1)Commented:
Marilync1266,

which line is raising the error ??
0
Dale FyeCommented:
Which line is generating the error, or is it a compile error?
0
Marilync1266Author Commented:
the paste special
0
Dale FyeCommented:
Don't think this is your issue, but I would replace:

xlws_New.Name = ("temp")

with

xlws_New.Name = "temp"

Have you tried:

xlws_New.Range("A1:S500").PasteSpecial Paste:=xlPasteValues
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
Rey Obrero (Capricorn1)Commented:

change this

xlws_New.Range("A1:S500").PasteSpecial.Values

to this

xlws_New.Range("A1:S500").PasteSpecial
0
Marilync1266Author Commented:
That worked but now I'm receiving another error with this statement.

DoCmd.TransferSpreadsheet acImport, 8, "tbl_Quote_Equipment", strImportFileName, True, "Temp!A1:S500"

The data and field settings look fine.

Basically, what I want to do is open a file from excel and copy the values (not formulas) from one worksheet in the file into a new worksheet that I will add to the file.  Then I want to import the data into a table in my database.  After that is succesful, I want to delete the temporary worksheet I created.

I've attached a screen shot of the error, copy of my table definition and the excel data (this is not in the original file)
Error.JPG
Import-Data.xlsx
Table-Definition.pdf
0
Dale FyeCommented:
Marilyn,

You need to post this as a separate question
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.