Recover background file when code errors out

[VicM]

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

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[VicM]

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

 

[SkipVought]

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?

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

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:
[tt]Set objExcel = New Excel.Application '(in case of early binding, GetObject or CreateObject for late binding)[/tt]
make it visible:
[tt]objExcel.Visible = True[/tt]

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

 

[VicM]

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

 

[SkipVought]

Lookups.

Does your lookup formula account for not found errors?

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

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

 

[VicM]

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.

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?

 

[SkipVought]

Hi,

ImpFile.Visible [b]= True[/b]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[VicM]

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

 

[SkipVought]

Sleuthing.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

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

 

[VicM]

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