×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Recover background file when code errors out

Recover background file when code errors out

Recover background file when code errors out

(OP)
Hi,

I have a procedure in Access that imports data from an Excel file after coded manipulation of that data. This is done in the background; that is, the Excel app doesn't appear on the taskbar. When the code errors out and I have to stop the execution, the Excel app is still showing as a process in the task manager.
What I'd like to know, is there a way for me to bring the Excel application into view? It would be really helpful to see what transpired in the spreadsheet that caused the error. I've tried much error trapping, but have been unsuccessful in trapping this occurrence. At this point my only option is to kill the process and thereby lose the info in the spreadsheet.
Basically the error message is saying there is a type conversion error. But when I look at the line of data in the spreadsheet that it's referencing, I don't see an incorrect data type. And that particular value has been processed in a data record above the indicated line of error without itself throwing an error.

Thanks for any suggestions,
Vic

RE: Recover background file when code errors out

Hi,

Where’s your code, as two of us previously asked for?

Error trapping?

What things do you have going on in the sheet that gets lost? How about SAVING the workbook periodically during the process?

The Watch Window can be a means of looking at the Excel Objects when you get an error and Debug. FAQ707-4594: How to use the Watch Window as a Power Programming Tool

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Recover background file when code errors out

(OP)
Skip
I appreciate your response. The entire code is extensive and makes calls to other procedures. I could just post the code where I believe the error is occurring, but the error doesn't get captured by my "On Error" statement. This could possible be because the error itself may be happening in Excel and not in Access.

I have used the Watch Window and have set breaks based on the info returned as to the line number in the spreadsheet. But the code never halts there.

I have tried stepping through the code line by line. But when it reaches one line, it appears to hang for several seconds and then throws the error.

Again, the code manipulates the data in the spreadsheet in order to convert it into the format required to be stored in the Access table.

I'm going to read the FAQ you suggested to see if I can handle it differently. I do like the suggestion of periodically saving the spreadsheet. That makes a lot of sense to me. So I'm going to try that to see if that gives me more insight to the problem.

If I'm not able to figure it out from that, I'll post the subroutine in which the error occurs and indicate where in the code the error originates.

I'll let you know how it goes.

Thanks,
Vic

RE: Recover background file when code errors out

Quote:

But when it reaches one line, it appears to hang for several seconds and then throws the error.
What is supposed to be going on there in the workbook?

Quote:

Again, the code manipulates the data in the spreadsheet in order to convert it into the format required to be stored in the Access table.
What specific formatting is taking place on the sheet? Raw data to what format?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Recover background file when code errors out

Keep excel visible when you test manipulating excel from access. Hope that somewhere you have object variable that refers to excel or excel object (workbook, worhsheet). So after instantiating excel:
Set objExcel = New Excel.Application '(in case of early binding, GetObject or CreateObject for late binding)
make it visible:
objExcel.Visible = True

This will allow you to see what went wrong with excel and when. Sometimes excel may wait for your response, for example when you try to close changed workbook and excel was not told to close it without saving. You might also leave hidden excel application open.

It is likely that your problem may be linked with one of the above, otherwise you would get errors in your code in access. BTW, does your excel file contains macros?

For testing, you could also turn on "break on all errors" VBE error trapping mode (in VBE: Tools>Options, General tab) or "Break in class modules". This will allow to trap some cases when error does not raise in standard module.

combo

RE: Recover background file when code errors out

(OP)
Skip,

The conversions are, for example: the data supplier might indicate in the spreadsheet a department, in text, for the worker. The database stores a numeric value as a reference to that department. So there would be a lookup from a table to replace the text of the department with a numeric value. There are other similar conversions. Or maybe the data indicates the resident's state completely spelled out. The conversion takes place, again with a lookup, to replace the long state name with its postal 2-character code to be stored in the member table.

This has worked as expected in the past. What I'm not able to discern is whether the data supplier has modified anything in the spreadsheet that's not overt. On the surface, all looks identical to the past spreadsheets.

combo,

I'm in the process of changing the late binding to early binding. The code was written before I fully understood the benefits of early binding. So yes, once that's completed, I'll be able to set the spreadsheet to visible and get a better handle on what's going on.

Thank you both,
Vic

RE: Recover background file when code errors out

Lookups.

Does your lookup formula account for not found errors?

Quote:

the data supplier has modified anything in the spreadsheet
Well there’s a huge possibility. Maybe you need some sort of means of checking the validity of the structure of the workbook you receive from “the data supplier.”

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Recover background file when code errors out

Early binding may make writing code easier, but the problem is not there. To make excel visible you need only object variable that references excel.

combo

RE: Recover background file when code errors out

(OP)
Guys,

Sorry for not getting back sooner. Lots going on: selling home, moving to apt, then heading to FL for winter. Phew!

But I still need to resolve this issue.

Here's a snippet of code.

CODE

Public Sub XLopen()
Dim ImpFile As Excel.Application

Set ImpFile = New Excel.Application

ImpFile.Workbooks.Open "C:\Union\Company\Company Membership August 2018.xlsx", 0, True
ImpFile.ActiveWorkbook.SaveAs fileName:="C:\Union\Company\Company Membership August 2018 - Copy.xlsx", _
    FileFormat:=51, CreateBackup:=False
ImpFile.Workbooks.Close

ImpFile.Workbooks.Open "C:\Union\Company\Company Membership August 2018 - Copy.xlsx", , False
ImpFile.Visible

End Sub 

The problem here is that the line to make the application visible doesn't compile. It says it's an Invalid Use of Property.

So I have no way of monitoring it. Then it bombs later on as my original post indicated. Without that line, the snippet runs clean.

How do I get Excel to be visible?

RE: Recover background file when code errors out

Hi,

CODE

ImpFile.Visible = True 

BTW, moving from PA? Lived in Philly, Norristown, Pittsburgh, fyi.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Recover background file when code errors out

(OP)
Skip,

This boggles my mind. I actually had the code written as: ImpFile.Visible True. Did not have the = sign. There's so much inconsistency in the code. Many commands will work without the = sign.
It just never occurred to me to put the sign in.
Thanks, that made the spreadsheet visible.
Now for the remaining hard part: figuring out why it's bombing.

Vic

RE: Recover background file when code errors out

Sleuthing.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Recover background file when code errors out

Object browser is your friend. Check if you deal with property or this is method. One needs "=" for seting property. When executing methods you can have a list of argument values or list of values assigned (with ":=") to argument names. However, when when the object/value returned by method is assigned to variable, the list of method's arguments has to be in parenthesis.

combo

RE: Recover background file when code errors out

(OP)
Thanks combo. I need to refresh my memory of that. Truth be told, this is the only client I do work for and not very often any more. So I'm not submerged in Access and VBA as much as I was in the past.

And BTW guys, the visibility of the spreadsheet did indeed allow me to see what caused the error. Unfortunately the data supplier changed the format of one of the data columns. They had been instructed to not make any changes without passing it thru my client and then me. They don't respond in a timely fashion to requests for data and clarification of same either.

While the code is specialized to take care of the way they originally formatted the data, it removes extraneous info in each column that's not needed to populate my client's database. As you are aware, it's very difficult to code for all instances or changes in the data format. I do have some routines in place in some areas, but without a heads up from the supplier the code will always bomb if they make unidentified changes to their format.

Again, thanks to Skip and combo for your support. If you are here in the States, Happy Thanksgiving to you.

Vic

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close