Importing from Excel with data populated by macros

I have successfuly imported data from Excel into my Delphi application except with a particular worksheet where the data was populated by macros within Excel. The recordcount in the clientdataset is zero after I try to import from Excel.

If I copy/paste the data to another Excel workbook, it imports fine. If I copy/paste the data back onto itself, it will not import.

It seems weird that the presence of the macros could interfere with the import process.

Any suggestion how I could make the import work?

Who is Participating?
moduloConnect With a Mentor Commented:
PAQed with points refunded (250)

Community Support Moderator
<< If I copy/paste the data back onto itself, it will not import.>> Try to export the data to a new sheet, not on the old one.
I do not know what exactly does the macro on your sheet.
Be more explicit.
What exactly do you want to achieve?

For more infos on working with Excel and importing/exporting to/from excel  try to find them here:

It is very usefull...
If you read excel using a COM object, you can read the formulas or the text in any cell, may be the system you are using read directly the formulas, but should read the text... or should call before a "execute macro" in excel before reading the cell texts.

Witch method are you using to import the data?

Could you try to read it directly and populate data by code?

If you need code to directly read a cell text from a excel file, it is easy, but if you need to heve the macros run before reading any data, then it could help to know in witch moment those macros are supposed to by executed inside excel: On recalculate, on open, etc.

Dennis_TreacyAuthor Commented:
Thanks for your comments so far.

I use Provider=Microsoft.Jet.OLEDB.4.0 with ADO to import that data from Excel. As I said, it works greate except with this one sheet that has a macro in it. We don't run the macro from Dellphi, it runs within Excel and does some fairly simple calculations eg

 Worksheets("Student").Cells(RowNo, 1) = (Worksheets("Course").Cells(RowNo, 1) & " " & Worksheets("Course").Cells(RowNo, 2)  etc

The cells are formated as General

After saving and closing the Excel worksheeet we try to import the results. No data come into the dataset

The solution we are using as from yesterday is import the raw data from Excel and to do the macro calculations within Delphi so, to that extent, the problem is now solved - in terms of our immediate requirements.

We can do this import using the raw data still in the Excel WorkBook with the macro, but we cannot import the calculated data that was created by the macro.



All Courses

From novice to tech pro — start learning today.