MS Access error 424 Object Required

Marilync1266
Marilync1266 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

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, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Once you have defined the objects, then you need to define xlApp

Set xlApp = CreateObject("Excel.Application")

Author

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
Ensure you’re charging the right price for your IT

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

Author

Commented:
I have that
Set xlApp = New Excel.Application
Top Expert 2016

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
Top Expert 2016

Commented:
oops sorry

set xlApp = new excel.application
Top Expert 2016

Commented:
Marilync1266,

which line is raising the error ??
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Which line is generating the error, or is it a compile error?

Author

Commented:
the paste special
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
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
Top Expert 2016

Commented:

change this

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

to this

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

Author

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, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Marilyn,

You need to post this as a separate question

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial