Importing from Excel with data populated by macros

Posted on 2005-03-03
Medium Priority
Last Modified: 2010-04-05
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?

Question by:Dennis_Treacy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 11

Expert Comment

ID: 13448357
<< 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...

Expert Comment

ID: 13457311
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.


Author Comment

ID: 13463212
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.




Accepted Solution

modulo earned 0 total points
ID: 14560748
PAQed with points refunded (250)

Community Support Moderator

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month12 days, 12 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question