[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

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.

[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

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.

[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

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.

[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

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.

[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

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!

[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

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.

[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

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

[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

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

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register:

Scanning file for viruses.

This file cannot be downloaded