ERROR 1429 - A workbook must contain at least one visible worksheet.

[Nandhan]

I'm getting below error while trying to generate a Excel report through Fox pro. I get below error at times while doing so.

Error executing program: Error 1429, Line 4 -- OLE IDispatch exception code 0 from Microsoft Office Excel: A workbook must contain at least one visible worksheet. To hide, delete, or move the selected sheet(s), you must first insert a new sheet or unhide a sheet that is already hidden...

My understanding is that the report doesn't has any data to show, and hence no worksheets are created.

Question: Can't foxpro create empty worksheet, if there is no data?

 

[GriffMG]

It looks to me like you are using automation to create your excel export.

You probably need to add a new sheet to the workbook before attempting to write to it.

Most copies of Excel will create a workbook with three sheets by default, I suspect your copy is not set to do that and you may need to open excel manually and change the default before letting VFP loose, unless you programmatically manage the number of sheets.

Most of the time I need to remove sheets from the default three:

			OEXCEL = CREATEOBJECT("Excel.Application")
			* make excel visible during development
			OEXCEL.VISIBLE = .F.
			OEXCEL.WORKBOOKS.ADD
			OEXCEL.DISPLAYALERTS = .F.

			FOR EACH SH IN OEXCEL.WORKSHEETS
				IF UPPER(SH.NAME)<>"SHEET1"
					SH.DELETE
				ENDIF
			NEXT

The code above removes all bar Sheet1.
In your case you might test to see if any sheets exist, and add one if needed.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Tore Bleken]

An error report without sample code to reproduce the error is almost worthless information!

 

[Nandhan]

Here is the part of the code which generates the Excel:

=STATMSG("Creating the report...")
lnFirstRow=6
lnCurRow=lnFirstRow
oExcel=ExcelInit(gcTemplate,gcSaveAs,gcSourcePath,gcDestPath,"infocentral050.jpg",CompanyName)
oExcel.Sheets("Layout").Name="Template"
SELECT DISTINCT buyer,buyername FROM summary ORDER BY 1 INTO CURSOR buyers
IF _TALLY>0
SCAN
lnCurRow=lnFirstRow
oBefore=oExcel.Sheets("Template")
oExcel.Sheets("Template").Copy(oBefore)
oExcel.Sheets("Template (2)").Name="Layout"
SELECT jfitds,packsize,jfbrnd,vaitem,vapo,vatsdt,vaprsb,vartcq,vartsq,totalcost,vacuno,cuname,vaprma;
FROM summary WHERE buyer=buyers.buyer ORDER BY 1 INTO CURSOR toexcel
=ExcelData(oExcel,"toexcel","A3:M4","A","A",lnCurRow,.t.)
=ExcelBlankFormat(oExcel,"A6:M7","A",lnCurRow+RECCOUNT(),10.5)
=ExcelCell(oExcel,lnCurRow+RECCOUNT(),4,RECCOUNT())
=ExcelCell(oExcel,lnCurRow+RECCOUNT(),7,"Sum:")
=ExcelSum(oExcel,"H",lnFirstRow,lnCurRow+RECCOUNT()-1)
=ExcelSum(oExcel,"I",lnFirstRow,lnCurRow+RECCOUNT()-1)
=ExcelSum(oExcel,"J",lnFirstRow,lnCurRow+RECCOUNT()-1)
lnCurRow=lnCurRow+1
=ExcelCell(oExcel,5,13,"MA #")
=ExcelCell(oExcel,1,1,ReportName+" ("+STR(ReportId,3)+")")
=ExcelCell(oExcel,2,1,CompanyName)
IF gdStart = gdEnd
=ExcelCell(oExcel,3,1,"Report Date: "+DTOC(gdStart))
ELSE
=ExcelCell(oExcel,3,1,"Report Period: "+DTOC(gdStart)+" - "+DTOC(gdEnd))
ENDIF
=ExcelCell(oExcel,4,1,"Report by Product Specialist: " + ALLTRIM(buyers.buyer)+" - "+ ALLTRIM(buyers.buyername))
oExcel.activesheet.Name=ALLT(buyers.buyer)
ENDSCAN
ELSE
WAIT WINDOW NOWAIT "No Data Found"
=ExcelCell(oExcel,5,13,"MA #")
=ExcelCell(oExcel,1,1,ReportName+" ("+STR(ReportId,3)+")")
=ExcelCell(oExcel,2,1,CompanyName)
IF gdStart = gdEnd
=ExcelCell(oExcel,3,1,"Report Date: "+DTOC(gdStart))
ELSE
=ExcelCell(oExcel,3,1,"Report Period: "+DTOC(gdStart)+" - "+DTOC(gdEnd))
ENDIF
=ExcelCell(oExcel,4,1,"Report by Product Specialist")
=ExcelCell(oExcel,6,1,"No Data Found")
ENDIF

 

[GriffMG]

Well, if that is the code that is generating the error, the line in question is No. 4

oExcel=ExcelInit(gcTemplate,gcSaveAs,gcSourcePath,gcDestPath,"infocentral050.jpg",CompanyName)

We would need an idea as to the contents of the various variables

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Nandhan]

Hope the below detail helps:

gcTemplate = name of the template file already created for specific reports
gcSaveAs = name in which the generated report should be saved
gcSourcePath = path in which the template file is placed
gcDestPath = path where the generated report should be placed
"infocentral050.jpg" = just an image to be imported into the report
CompanyName = Company name that should be displayed in the report

Appreciate the help!!

 

[GriffMG]

That is excellent, but I meant the actual contents of the variables, so gcTemplate might be "c:\templates\xltamplate.xls"
you could also check to see if that file exists and open it in excel to see if it has a sheet called 'TEMPLATE'

Also, I suspect you have a function somewhere called ExcelInit which returns a object which is probably an Excel workbook.

We would need to see the details of that as well.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

Line 4 of which code errors? Surely not the code you posted, because in itself it just calls the ExcelInit function in its Line4, and that is a VFP user defined function as far as I can deduct from its name and from it not calling a method of an OLE object, but a function or procedure. It returns the excel object, it seems, which you then use in further lines. But only the usage of oExcel here or within the ExcelInit function can cause an OLE error. So you look for a line4 of code doing something along the lines of [tt]oExcel.method(params)[/tt]. Mere simple straight forward logic, as the error states it comes from Microsoft Office Excel.

It seems to be line 4 of ExcelInit is failing, or of any code ExcelInit is calling. The code you show gets its oExcel object from line 4, but first use is in line 5, where you have another prerequisite of the code to work: a sheet named "Layout" renamed to "Template". IF you want error free code, you have to check such prerequisites and not rely on them. And your error handling should not only report line number, but also PROGRAM(), then you'd know what code exactly fails. Or if you use the VFP IDE without error handler, click on "Suspend" and then start the debugger and the trace window and execution stack window will show you where the error happens. You might also read about ASTACKINFO to get program stack information at time of the error, when implementing an error handling routine to at least log as much info of the current state of the application as you can get at runtime for later reproduction of an error.

The error itself must come from a line of code trying to hide, delete or move a sheet, but your template may have none, so a prerequisite for ExcelInit to work would be a gcTemplate excel file with at least an empty sheet in the workbook. It must be in the template file already, when ExcelInit should do something on it, eg put the image you have as a parameter on a sheet. We also know that a valid template needs a Layout sheet. This code does have some requirements to be fulfilled before it can work.

Griff showed you how easy it is to create a new document by calling oExcel.Workbooks.Add. In the same manner you may call into oExcel.ActiveWorkBook.Sheets.Add() to add a sheet. But you can't mend this problem here as aftermath, as your first chance to act is past the ExcelInit line. If the ExcelInit function already tries to work on the template passed in and assumes and requires a sheet in it, it would explain the error. Would it be so difficult even having no data to create a new empty excel file (meaning the usual initial state of a workbook with an empty Sheet1, not a fully empty workbook without any sheets) and use that as template? I think that would already solve your problem.

Bye, Olaf.

 

[GriffMG]

Olaf, if it's not line four of the code he has provided, it surely probably won't be ExcelInit() that is throwing the error will it? It might be any line in the code shown, so the actual error could be in ExcelData(), ExcelCell(), ExcelBlank() or anywhere...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

Griff,

yes indeed the error could also be later in any other function, but up to line 4 in this code is no excel usage. The assumption it may be in Excelinit() is based on the error message. It would perhaps be a natural step to take a template with data, initialise it with the passed in info (eg an image), so I assume the ExcelInit is doing more than just creating an Excel.Application object. That wouldn't need all the parameters. Other function names don't suggest to me to hide, delete or move a sheet, but rather process or change cells. So I don't conclude this because ExcelInit is in line 4, the line 4 specification has to be the line no of whatever detail code failing on its line 4. It's a mere coincidence, but as already explained very broadly the ExcelInit call itself would cause a VFP error other than 1429 OLE error, eg when passing in more parameters as the function can take.

It's totally unclear where the OLE error really is casued, but Nandhan, as you post this part of your code, you seem to think its in there, but it surely is not, I explained why. You can only get forward with your analysis when you either know PROGRAM(), ASTACKINFO() or Suspend and start up the debugger at the point the error message appears. It's a very important thing to make yourself comfortable with debugging code via the debugger in any development environment, or you easily end up in wrong assumptions.

Griff, it's understood, of course an error happening within called code will report that codes line number, not this codes line number, that doesn't rule out the error is indeed within ExcelInit and it's mere coincidence that call to the failing code also is line 4 in this code. As said the line erroring must be a call of a method of an OLE excel application object. That is unmisleadingly clear from the error number being 1429 (read AERROR help topic, Nandhan, it has spcific info on that error number) and it's also clear from the error message.

If I were you, Nandhan, I'd put a breakpoint here even before ExcelInit and single step through the code. I expect the ExcelInit has some call to hide another expected sheet of data, which should merely be the source of an excel graph object or anything like that happening within that code. It would be a good reasoning to hide a sheet to hide ugly raw data, if you create a graph object, but reasons are manyfold. My first advice still is, add abreakppint there and single step through the code.

Bye, Olaf.

 

[Olaf Doschke]

My understanding is that the report doesn't has any data to show, and hence no worksheets are created.

Is that an assumption about the gcTemplate? Then it shows you this code requires some raw data to exist in there, at least an empty sheet.

Yes, ExcelInit or whatever code throwing that error could instead of failing check the existence of whatever sheet it expects to exist and create an empty sheet or report a warning message instead of failing "miserably", but in general code is no intelligence mending itself. Your code also simply assumes a Layout sheet to exist and would fail with a similar error message, if it wouldn't. In a way very clean programming would check requirements and health of a current state before doing the next step, the next line of code, but surely you wouldn't get any productivity, if code would have many lines of asserts and other checcks, which themselves also could error by the way, before doing the slightest thing. You have to fulfill the requirements of code to use it or expand it to handle such corner cases.

A very similar example is how FRX reports simply quit, if the report driving data table or cursor is empty or FOR condition is not fulfilled in any record of report data. In a way it's more stable, as it doesn't error, but it simply leaves you clueless unless you check your prerequisites. Don't expect any code to mend a situation it may be able to mend, if eg the excel developer would have thought of such corner cases. The intelligence of code should not come from the intelligence of the lowest level simplest parts of the code, but the outmost level of business logic code, which itself needs to take care of requirements and controls a hierarchy of increasingly simpler classes and objects to do the overall task. Speaking metaphorically, the job of a waiter is to take your order and deliver it, but he's neither the cook nor does he take care of electrics, if a fuse blows and the light fails. While at the topic of electronics: The outmost error handler there typically are fuses, which blow in case of errors, there is no extra circuitry replacement unless we talk of redundance for safety of a space ship.

In essence: Don't blame a framework of functions not acting cool and intelligent for you, blame yourself on not knowing enough to make use of the functions. It's always frustrating if something you bought for saving you the time to implement it yourself now takes time to understand it. But you can't really appreciate how much time it saves you and how little it is asked of you to make yourself familiar with how a library works and has to be used and needs whatever preconditions and outset to be fulfilled or configured.

And be thankful for the error, it hints on whats wrong, and you should simply have a better "fuse" a better general error handling routine to know what really fails.

Bye, Olaf.

 

[Olaf Doschke]

doesn't has any data to show

...and if you know this always causes that error, your simplest precheck and action would be to do:

If reccount("reportalias")=0
  MessageBox("No data - no report",64,_screen.Caption+" Info")
  Return
Endif

Before going into creating an excel sheet you know will fail anyway or have no data in it anyway, even if it wouldn't fail.

Bye, Olaf.

 

[JRB-Bldr]

First - you might want to look at the FAQ:

http://www.tek-tips.com/faqs.cfm?fid=4428

Next, before you attempt to do something in Excel via VFP Automation, it is advised that you do it without VFP within Excel itself and record your actions as a Macro.
Then when complete you can examine the Macro to see what & how VFP Automation needs to do things. Sure the code is not exactly the same, but it will give you the idea of how to proceed.
That process has worked pretty well for me MANY times with quite a few VFP Automation projects.

Oh, I'll add one more thing..
Generally when I start developing my VFP Automation code, I set the Excel Object VISIBLE = .T..
In that way I can use the VFP Trace Window and single step through my VFP Automation code and watch the results occur within Excel.
Once the development is done I set the Excel Object VISIBLE = .F.

Good Luck,
JRB-Bldr