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 FyeOwner, Developing Solutions LLCCommented:

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

Dale FyeOwner, Developing Solutions LLCCommented:
Once you have defined the objects, then you need to define xlApp

Set xlApp = CreateObject("Excel.Application")
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Marilync1266Author Commented:
I have that
Set xlApp = New Excel.Application
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
Rey Obrero (Capricorn1)Commented:
oops sorry

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

which line is raising the error ??
Dale FyeOwner, Developing Solutions LLCCommented:
Which line is generating the error, or is it a compile error?
Marilync1266Author Commented:
the paste special
Dale FyeOwner, Developing Solutions LLCCommented:
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

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
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
Dale FyeOwner, Developing Solutions LLCCommented:
Marilyn,

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