We help IT Professionals succeed at work.
Get Started

Access 2010 executing Excel VBA: getting error: Application-Defined or Object-Defined error

377 Views
Last Modified: 2016-02-11
Hi EE,

We recently migrated from XP/Office 2003    to W7/Office 2010.

We are running our projects as mdb under Access 2010.

Am testing a project and getting an error under Access 2010.

Think this is related to how defined Excel objects in the vba.

Am getting the error on the second record, so am thinking maybe did not "finish up" the previous row properly.

Yet cannot find the error. Hope you can help.

Background:
===========

I work for a college. Once a year we send the instructors the course catalog description and detail about the course
for them to review and update. Any updates we data entry via a Web based system.

Am using  Excel as a "report writer tool" and am hiding grid lines so that it looks like a report.

For each course-id-row read, the course id key is compared to the hold-key.
If there is a control-key break, then finish processing, and save the excel spreadsheet as a pdf.

The first course id, processed nicely, detected a new row, and went to finish off old key processing and creating a pdf.

When I process the second course id, am getting the error.

Below is uploaded:
    1) test mdb:
    2) xlt  excel file template
 
 please click on the form, mark 2 for how many to process, than click ok to process

You will see the first excel sheet create successfully
         then the mdb will stop, if you go to the vba you will see the error.



First I had this:

                     objExcelActiveWs.Cells(27, 1).Value = rst!Course


then changed to:

                objExcelActiveWs.Range("A27").Value = rst!Course


Still getting error on second row.
When I click end, and then F5, it does ignore the error.

However, really want to solve the error so that the user does not have to hit F5 for a long time.

the Init          New Key routine is here:  c056_InitNewKey
The finish off old Key routine is here:  c055_FinishOldKey

The objExcel objects are dimmed as Public here:  000_MyPublicConstants
 
2015-07-20-error1.png
2015-07-20-error2.png
thx so much for your help,
sandra
2015-EE-ARG-02-Feb-ElectiveNotification-
xxTemplate-ElectiveCatalog-Profile-ForWe
Washington-George-CBIO-N105-111111111.pd
Comment
Watch Question
Social distance - Wear a mask - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 5 Answers and 23 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE