import XLSX 4

[linousa]

I know there many topics/solutions out there dated all the way from 2007 and I've already spent hours and hours going through them without any luck finding that works well. Some of them will import all the data as memo records, others will have 600-1500 lines of code and when I run them, I get error messages in languages I can't even identify, some use external interim engines/libraries/apps that are missing or outdated, others semi-automated with so many steps, that I feel going in excel and converting there will be a lot easier way, etc. Does anyone have a working solution already in 2018? Thank you!

 

[Mike Gagnon]

http://praisachion.blogspot.com/2017/08/importfromxlsx-40.html

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[linousa]

importxlsx 4.0 - works.
appendfromxlsx3.1 - works on its own, but when I add it to my project, during compilation I get this error:
Unknown LAFIELD - Undefined
Any ideas?

 

[Mike Gagnon]

Declare LAFIELDS as an external array.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[linousa]

If someone will need it, add this line to "appendfromxlsx.prg", thanks to Mike!

EXTERNAL ARRAY LAFIELD

 

[Olaf Doschke]

You can also simply ignore the compilation error. The generated code is the same, you only tell the compiler there's no missing procedure to worry about.
The problem is an array access looks just like a function call, even if you use square brackets to distinguish it. Because you can also use them for a function call. Demo:

? square[3]
? int[42.1] && also for native functions
Function square()
   lparameters tnX
   If NOT Vartype(tnX)="N"
      Error 9
   EndIf
   Return tnX*tnX

And that's even valid for 2d arrays because similarly, that could be a call of a function with two parameters.
So when the compiler reports Unknown LAFIELD - Undefined it looked for a PRG or procedure or function or stored proc definition of that name.

It's also good to know once an array of a certain name exists it has priority over the same function. Therefore array names are checked against a more strict rule to not be native function names (reserved words). For example, try to generate an array named "str":

Local Array str[42] && causes error 1652

Another good reason to more generally avoid reserved words for any names of variables, fields, files, anything.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[linousa]

Thank you Olaf.

I have another problem now-after having it working for a few days, now I get another error: File 'appendfromxlsx.prg' does not exist. It worked before as 'appendfromxlsx(cFile,"nn",,,,.T.)' and nothing has changed.
Prg is still in my project under: code->programs->appendfromxlsx.prg, and I can't bring it as class method either(so I can reuse it later), getting an error: 'Methods and events cannot contain nested procedures or class definitions.'
How do I call for it then? Do I tell it that it is external somehow too?

 

[Olaf Doschke]

The error "Methods and events cannot contain nested procedures or class definitions" occurs, whem you put procedure or functions inside methods, eg try to do that in the Init() of a form, it gives that error.
It has to stay in a separate PRG.

You have to SET PROCEDRE TO PRG files to be able to use them.

Init code in main should do that. If you do it manually, it's only working until you close and reopen a project.

So any good starting code has some lines of SET PROCEDURE and also SET CLASSLIB to let things be known for usage,.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[linousa]

Thanks Olaf,
It worked, somehow I thought that if they were included in the project, that I don't need to set them anymore. At least it worked for me before.

 

[Olaf Doschke]

That thought is right, but only after you build an EXE. When you run code in the IDE, even if it's all organized in a PJX, it doesn't know each other, the only "glue" is SET PATH and SET PROCEDURE and SET CLASSLIB.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

By the way, the Environment Manager can help you keep some settings associated to one or more projects.

You can start it from the task pane or directly with [tt]DO HOME() + "ENVMGR.APP"[/tt]. Click New and you have a new set you can name as you like and make settings in all the tabs, including paths and all settings, also PROCEDURE and CASSLIB. In the last tab associate it with your PJX and you have some automatism in the IDE the project itself doesn't manage.

Another concept which might be helpful is a project hook class you can associate with your project within project info. It cannot only hook into opening a project, look at all the hook events the project hook class offers and you might even automatically add a new VCX you create during your work in the project into some metadata or your environment settings.

In conjunction with the scripts you can define in the environment manager, you can also keep the setting in sync with what you did in the programming session.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name