Mysterious Fatal Error when Saving Excel File 1

[gryff15]

I have a form that saves an excel file. Mysterious because the fatal error that the users encountered does not appear in my computer nor to my co-programmers'. It appears right after the putfile code where a window browser pops up and when you click save, the fatal error occurs but the program continues to execute without any further errors. The output is correct.
lcFilename = Putfile( "Save As", lcPath+ lcTranID, "xlsx" )
I tried just xls as file format but the error still occurred. I can't do Set step on because the error does not appear in my machine. I also thought maybe because I have Windows 10 but I tried to run the program to my co-programmer who uses win 8.1, the error didn't appear in her computer. Please see the attached image for reference. I have been googling since yesterday but failed to find a solution.

 

[Mike Lewis]

It's highly unlikely that your PUTFILE() is causing the error. The error is more likely to be from Excel (or some other OLE server), and that has no connection with PUTFILE(). The error is probably caused by the line after the PUTFILE(). Perhaps you could show us the code for that line.

Also, given the error occurs on some computers and not others, you need to find out what's different between the ones where it occurs and the ones where it doesn't. You've eliminated the Windows version. My guess is that it is the Excel version that is different. You might want to check that before going any further.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[gryff15]

This is some of what's after the putfile():

oXLS = Createobject("Excel.Application")

With oXLS
oWB = .WorkBooks.Add

Do While !Eof()

*!* ------ Insertion of data
.Range("A"+lcCellNo ).Select
.ActiveCell.FormulaR1C1 = pr_num
.Range("B"+lcCellNo ).Select
.ActiveCell.FormulaR1C1 = Ttod(pr_date)
.Range("C"+lcCellNo ).Select
.ActiveCell.FormulaR1C1 = amount
.Range("D"+lcCellNo ).Select
.ActiveCell.FormulaR1C1 = Alltrim(Code)
.Range("E"+lcCellNo ).Select
.ActiveCell.FormulaR1C1 = Alltrim(slname)
.Range("F"+lcCellNo ).Select
.ActiveCell.FormulaR1C1 = Ttod(duedate)
.Range("G"+lcCellNo ).Select
.ActiveCell.FormulaR1C1 = Iif(llfVerify,Ttod(processdate),Iif(Alltrim(rush)=="T","RUSH",""))
.Range("H"+lcCellNo ).Select​

skip​

enddo

.Range("H"+lcCellNo).Select
.ActiveCell.FormulaR1C1 = "DATE "+Transform(Datetime())
**I used transform() hear instead of ttoc() just in case but still the error appeared

endwidth

Both xls and xlsx were created successfully but also both had Fatal Error messagebox.

 

[Olaf Doschke]

You should have a better error handling, even the code suggested in the help topic about ON EORROR inclide parameterizing your error handling routine with [tt]ERROR( ), MESSAGE( ), MESSAGE(1), [highlight #FCE94F]PROGRAM( ), LINENO( )[/highlight][/tt]. All this and more also is available in the arrays AERROR and ASTACKINFO create.

From your error message screenshot the message does come from another line, don't debug with the knowledge of what you last saw, this is the level a user can only know about the application, but as developer you can know more. As Mike assumes, I strongly see no OLE problem in PUTFILE, as it's not having any relation to an OLE object at all. Your message also shows the line failing is SELECT sqlprlst, and is coming from a PRG called ERRCATH, presumably your wrongly named error handling PRG code (shoudl be ERRCATCH, perhaps). So while you have some errhandling in place, it's not very thorooughly done, it may even fail itself in having prerequisites like the workarea sqlprlst being available. If your error handling has errors you call yourself "recursive" and the real error gets buried by not being displayed, so it's most impoortant to have an error handler not needing any prerequisites to function.

One best practice to avoid the eror handler to cause itself to happen again when any error happens within it is to unset ON ERRROR within the handler, another solultion since VFP8 is to use TRY..CATCH within the handler. You seem to have such a case as your errhandling routine reports itself, if I interpret the name ERRCATH.PRG correctly.

Bye, Olaf.

 

[Mike Lewis]

One other point: Does your code test for the user cancelling out of the Save As dialogue?

If the user hits ESC or presses Cancel, then PUTFILE() won't return a filename, and your subsequent code might fail. I'm not saying this is necessarily the reason for the error, but it is something that you need to look out for.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[gryff15]

So that's why I couldn't find the table sqlprlist in the current form I work on. It's from others.
I saw from the VFP 7 help this code:
ON ERROR DO errhand WITH ;
ERROR( ), MESSAGE( ), MESSAGE(1), PROGRAM( ), LINENO( )
*** The next line should cause an error ***
USE nodatabase
ON ERROR && restore system error handlerPROCEDURE errhand
PARAMETER merror, mess, mess1, mprog, mlinenoCLEAR
? 'Error number: ' + LTRIM(STR(merror))? 'Error message: ' + mess
? 'Line of code with error: ' + mess1
? 'Line number of error: ' + LTRIM(STR(mlineno))? 'Program with error: ' + mprog

and I tried creating errhand.prg, and inserted the on error commands in my code under putfile() because it is where the fatal error occurred. The problem is, I don't know which table I can replace for that "USE nodatabase". Should it be the "sqlprlist that appeared" in the fatar error?

Another major thing that bugs me is that the error does not show in my computer. Everything works well. I always have to remote view the user and copy the exe file to her pc and run the system there so I would see if my fixes work.

 

[gryff15]

Sir Mike, I have

If lcFilename == ""
Return
Endif

after that putfile command, i just omitted it at the first place.

 

[Mike Lewis]

I think you are misunderstanding the way that ON ERROR is used. You don't put it "under putfile() because it is where the fatal error occurred". Rather, you put it at the start of your main program, so that it is in force throughout the session. Also, you shouldn't be looking for "which table I can replace for that "USE nodatabase". That USE command is just an example in the Help topic, to demonstrate a line that might cause an error.

What you need to do is to write an error-handler that captures as much information as possible about the environment at the time the error occurred. That includes values of all in-scope variables; aliases of all open work areas; record pointers, index orders, etc. for all open work areas; calling stack; value of all SET commands; and anything else you can think of.

The error-handler should write that information to a log file of some kind (a text file is usually adequate). When the error occurs at the user's site, you arrange for the user to send the log file to you, so that you have the information you need to track down the error.

Once it has written the log file, the error-handler should issue a friendly message to the user, and then close down.

Now, having said all that, I'm not certain that an error-handler would help you in this particular case. Judging from your screen shot, the error message your users sees in not a VFP error, and will therefore not fire the error-handler. But I'm only guessing about that. Perhaps you can confirm either way. But in any case, whatever kind of application you are developing, you really should have an error-handler.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

1. The sample code just had the line USE nodatabase as an erroneous line intentionally, just to test the error handling. Using a file, that is no database/table or doesn't even exist will of course trigger either file not found or file... is not a table error. That's not what you need in your code, that's just to test the error handling, to trigger or force an error and let happen, what was defined by ON ERROR. For example to test it and see, whether itself runs error free.

2. You do the ON ERROR command before everything. In itself it does nothing, it simply tells VFP to handle any error with the command given when it happens ON ERROR spoken in detail means "ON" the condition an "ERROR" event happens, do this or that...(whatever command you write after ON ERROR). It does not happen righgt away and it's not a command you put somewhere to do something on the condition an error happened in the previous line, it always and only does what you define from that moment onwards.

So no, you don't put ON ERROR after the PUTFILE call, you put it as soon as possible before executing any further code. The typical start code involves some SETs and an ON ERROR definition. If you have your errorhandling routine as a separate PRG you first should have some SET PATH and SET PROCEDURE settings, if you call a procedure within your main.prg code or you specify the full path to the PRG in a [tt]DO D:\path\to\ERRCATCH.PRG WITH parameterss[/tt] (pseudocode, don't take it literally), the ON ERROR could also be the first line of your code, so anything following, when causing any error, will trigger the error handling routine. Of course it's also one major condition, that the command you set up for error handling finds the PRG or function you let it execute.

Your original error handler seems to have PROGRAM within its own code, so it displays its own name ERRCATH in a messagebox, that's obviously not how this should be done, because you don
t really see where the error occurs, you can only guess from the last thing the user (or yourself) saw happening. The error could be next line, it could be far down the drain already, much code can happen in split seconds. Since it has to do with some OLE control the expected context is that control, maybe something you do with excel automation with the given filename.

In the context of ON ERROR being triggered PROGRAM() will have the value of the progrm having the error, within the error handling routine PROGRAM() will be the name of the errhandler prg, that's why it's important you have PROGRAM() as parameter, not within the handling code.

You should use code references tool, to find out where in your project the error handler is and where it is established as handler, where you have an ON ERROR line, instead of defining your new error handler now, and then extend it's parameterization and it's inner working. Mike assumes this isn't an error handling routine showing that messagebox in your screenshot, I assume it is a VFP messagebox, not the native one you get with no error handler routine established, but something, that's poorly constructed. It's important you find this, because whatever you set up as new error handling most probably isn't in effect later, as the poorly written error handling routine takes over the moment anywhere in your project ON ERROR redirects handling of errors to the already existing error routine.

Bye, Olaf.

 

[gryff15]

Thanks. I will still try to find out where that ERRCATH is located as I am not yet familiar with the whole system I work in. I only work on one of the forms and have yet to trace the flow of the codes and I hope to figure out where exactly the error occurs and why it only appears in the users' computers.
I just replaced XLS to CSV output (just in case it's the Excel format), still, the persistent fatal error occurred when the user tested the program. I just hate that I don't encounter the same error in my own computer. It always works just fine in me.

 

[Mike Lewis]

At this stage, it would really be helpful if you could determine if the error message in your screen shot was produced by your application or by some other component (such as Excel). You need to do this for the reasons that both Olaf and I have explained. If necessary, search your code for some of the text of the message, or perhaps the words Fatal Error.

If the message is issued by your application, that would definitely point to something wrong with your error handling. What you are doing is displaying a message saying there has been a fatal error, but then letting the program continue to run. That is definitely not right.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Since it's an OLE error the simplest possibility is a missing OLE object/class. Though that would error at the moment you instanciate some form needing eg an ActiveX control or an embedded ole element (Olecontrol/Oleboundcontrol).
OLE classes need their registration at installation, they don't link to a VFP EXE nor are supported by the VFP runtime natively, they always are external additional components, eg DLLs, OCXes or even a whole application suite like Office. Not every Office Version for example includes same Office related controls, like MSGRAPH, that would for example explain why it works on your PC but not at the customer.

Bye, Olaf.

 

[gryff15]

Hi, I just found out that the error appeared not on the PUTFILE part but on the Print Setup part of my macro excel code. And I fixed it by going to my Control Panel > Devices and Printers > set my Default Printer to the printer it can currently connect to. I can't just delete this code because I want the excel to be ready to print so user won't have to adjust the paper size and margin. I don't get it why the printer connection has something to do with it? Is it possible to make it not depend whether it can currently connect to printer, besides, it will only be saved, not automatically be printed.
[pre]
*!* --------- Print margin and size page setup -------
*
.Application.PrintCommunication = .F.
With .ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
Endwith
.Application.PrintCommunication = .T.
.ActiveSheet.PageSetup.PrintArea = ""

With .ActiveSheet.PageSetup
**error pops up here

.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = .Application.InchesToPoints(0.25)
.RightMargin = .Application.InchesToPoints(0.25)
.TopMargin = .Application.InchesToPoints(0.2)
.BottomMargin = .Application.InchesToPoints(0.05)
.HeaderMargin = .Application.InchesToPoints(0.2)
.FooterMargin = .Application.InchesToPoints(0)
.PrintHeadings = .F.
.PrintGridlines = .F.
.PrintComments = xlPrintNoComments
.Printquality = 600
.CenterHorizontally = .F.
.CenterVertically = .F.
.Orientation = xlLandscape
.Draft = .F.
.Papersize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = .F.
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = .F.
.DifferentFirstPageHeaderFooter = .F.
.ScaleWithDocHeaderFooter = .T.
.AlignMarginsHeaderFooter = .T.
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
Endwith

.Application.PrintCommunication = .T.
*--------------------------------------------------------****



[/pre]

 

[gryff15]

Is there a way to catch the error when the program detects it currently does not have any printer to connect to when I'm saving an excel? The main purpose is just to save the excel. Not worry about whether the pc is connected to a printer or not.

- webrider -

 

[Olaf Doschke]

That error then is happening from Excel and a question about excel capabilities to suppress such errors. IIRC there is some Alert property.

In VFP you can handle errors with ON ERROR. Why don't you do that already? It's a first thing to implement in any VFP application to have an error handler logging errors and shutting down an application, as it then can't guarantee to continue stable, perhaps also handling a few known errors by suppressing them. In this case a local TRY...CATCH around the whole process could help. Or the Error event of the object doing the Excel file genration, PUTFILE and triggering the macro printing.

All types of error handling is shown in

http://doughennig.com/papers/Pub/ErrorHandlinginVFP8.pdf

with a focus in the then new structured TRY...CATCH error handling.
Also see

http://doughennig.com/papers/Pub/errorh.pdf

and

http://doughennig.com/papers/Pub/Jan98.pdf

Bye, Olaf.