Highlighted
Absent Member.
Absent Member.
1183 views

[archive] Importing external data into Excel

[Migrated content. Thread originally posted on 31 March 2005]

I am trying to import data from a CSV file into an existing XLS file using Excel QueryTables. Everything works O.K. up until I call the Add method on the QueryTables. Then I get the error message:

"Class is not licensed for use (error 800A03EC)"

What does this error mean?

Code snippet follows:


77 H-APP HANDLE OF @APPLICATION.
77 H-WORKBOOK HANDLE OF @WORKBOOK.
77 H-WORKSHEET HANDLE OF @WORKSHEET.
77 H-RANGE HANDLE OF @RANGE.
77 H-QUERYTABLE HANDLE OF @QUERYTABLE.

05 EXCEL-CSV-FILE-NAME PIC X(110) VALUE SPACES.
05 EXCEL-XLS-FILE-NAME PIC X(110) VALUE SPACES.



CREATE @Application OF @Excel HANDLE IN H-APP.

MODIFY H-APP Workbooks::Open(EXCEL-XLS-FILE-NAME)
GIVING H-WORKBOOK.

INQUIRE H-WORKBOOK @Worksheets::Item(1)
IN H-WORKSHEET.

* Get the next open cell to insert to
INQUIRE H-WORKSHEET Range("A65536")::End(xlUP)
IN H-RANGE.

INQUIRE H-RANGE @Offset(1,0) IN H-RANGE.

* Import the CSV data
*** This is the line generating the error ***
MODIFY H-WORKSHEET QueryTables::Add
(BY NAME @Connection EXCEL-CSV-FILE-NAME,
BY NAME @Destination H-RANGE)
GIVING H-QUERYTABLE.

.
.
.

TIA!
Kelli
0 Likes
6 Replies
Highlighted
Absent Member.
Absent Member.

RE: [archive] Importing external data into Excel

Attached is a program I use to import data into excel. You will not be able to compile and run because of copy books and linkage but this might give you an idea. My data is in a file. I read the file and add each line in.

below is the linkage copybook.

01 MY-EXCEL-DATA.
05 MY-EXCEL-FILE-NAME PIC X(50).
05 MY-EXCEL-NO-COL PIC 9(04).
05 MY-EXCEL-TITLE PIC X(35).
05 MY-EXCEL-COL-DATA OCCURS 25.
*type = D = DATE C = Comma T = Text
10 MY-EXCEL-COL-TYPE PIC X(01).
10 MY-EXCEL-COL-TITLE PIC X(40).


Hope this helps.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Importing external data into Excel

Originally posted by KelliM
I am trying to import data from a CSV file into an existing XLS file using Excel QueryTables. Everything works O.K. up until I call the Add method on the QueryTables. Then I get the error message:

"Class is not licensed for use (error 800A03EC)"


I am not going to say much about your error, that takes too much time. I will rather focus on the fact that you are doing things way too complicated.
If you want a csv file into excel, just open it. Like:

MODIFY H-APP Workbooks::Open("Mycsvfile.csv")
GIVING H-WORKBOOK.

Then, to store this as xls, use SaveAs and set the type.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Importing external data into Excel

I'm not wanting to simply open a CSV file and save it as XLS. I'm wanting to append (i.e. import) the data from a CSV file into an existing XLS file that already has other data in it.

I'm almost there - I had to prefix the filename for the QueryTables.Add with "TEXT;". I no longer get the error message. Works like a dream.

The only issue I'm still working on is that Excel.exe is still running after the routine is done. I call Close() and Quit(), and I'm destroying every handle in hierarchical order. So I don't know why it's still hanging out there.

Hard to get back into this 15 months after I took the class! :rolleyes: But I'm getting there.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Importing external data into Excel

If Excel still hangs around, that means something is holding on to it. There is nothing you can do but to doublecheck that all handles are destroyed. And that they are destroyed in the opposite direction of how they were allocated.

Good work!
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Importing external data into Excel

Originally posted by KelliM

The only issue I'm still working on is that Excel.exe is still running after the routine is done. I call Close() and Quit(), and I'm destroying every handle in hierarchical order. So I don't know why it's still hanging out there.


Like Gisle said, destroy the handles in the reverse order they were allocated. This ensures that child handles are destroyed before their parent handles and no dangling child references to a destroyed parent remain, which would keep excel in memory.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] Importing external data into Excel

Originally posted by gforseth
If Excel still hangs around, that means something is holding on to it. There is nothing you can do but to doublecheck that all handles are destroyed. And that they are destroyed in the opposite direction of how they were allocated.

Good work!


i had a few fights with similar problems. There are some more things you could check out:

Ms-Site

If you use a macro inside the workbook

maybe you find something useful on these sites
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.