?
Solved

I need help with the Excel Range collection and cell formatting using Automation.

Posted on 2006-07-04
7
Medium Priority
?
280 Views
Last Modified: 2012-05-05
Hi guys,

Question 1
I am trying to import an excel price list of books into my Delphi Application, the excel file is using custom formatting on each cell containing an ISBN number the formatting used is: (0000000000). In excel it displays the correct results ie: 0072226803.

When I access the cell using
Excel.Cells.Item[1,1]

it returns: 72226803, this throws the validation out because an ISBN number must be 10 characters in length and the first two zeros have gone AWOL.
How would I access the cells value and preserve the leading zeros? Keep in mind that the Spreadsheets differ from supplier to supplier so their ISBN columns may have different formatting, ultimately I want to access the value as its seen on the spreadsheet...

Question 2
Most of the documentation shows the Cells collection being accessed as follows:
Cells(rwIndex, colIndex).Value

My problem is that with the Excel_TLB, the only way to access an individual cell by row, col is with the Item property and this returns an OleVariant which pretty much only contains the cells content. How would I access other properties for an individual cell?



Any help on this will be greatly appreciated!
0
Comment
Question by:scandiumza
[X]
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
  • 3
  • 3
7 Comments
 
LVL 3

Expert Comment

by:Ubethatway
ID: 17035916
Hi,

Would a suitable solution be to simply pad the numbers with 0's up to 10 digits? e.g.:

function pad(X: String): String;
  padded := X;
 
  while (length(padded) <> 10) do
    padded := '0'+padded;

  result := padded;
end;

Remembering to convert the value to a string before passing it to the function.

Hope that helps, Mark.
0
 

Author Comment

by:scandiumza
ID: 17035951
Hey Mark,

I was thinking of padding it but the problem comes in with the validation. If, through human error, the ISBN numbers in the pricelist are the incorrect length and they get padded, they will be passed as valid when tested. These invalid entries should not appear in the application.

Thanks for the suggestion though!
0
 
LVL 10

Accepted Solution

by:
atul_parmar earned 1000 total points
ID: 17035962
Hi, try this out.

var
  s : string;
begin
  if x = Unassigned then
    x := CreateOleObject('Excel.Application');
  x.visible := True;
  x.WorkBooks.Add;
  x.WorkBooks[1].WorkSheets[1].Cells[1,1] := 12345;
  x.WorkBooks[1].WorkSheets[1].Cells[1,1].NumberFormat := '0000000000';
  s := x.WorkBooks[1].WorkSheets[1].Cells[1,1].Text;
  ShowMessage(s);
end;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:atul_parmar
ID: 17035999
var
  x : olevariant;  >>> Missed out
After you finish working with the object free it by
x := unassigned;

In your case, all you have to do is to use the olevariant or cast the object to IDispatch.
0
 

Author Comment

by:scandiumza
ID: 17036022
Now that works precisely how I need it to work!

Any chance of this working with the Excel Type Library?

Thanks atul!
0
 
LVL 10

Expert Comment

by:atul_parmar
ID: 17036055
Yep, As I said previously you will need to cast it to IDispatch

e.g.
var
x : olevariant;
myexcel : texcelapplication;
begin
  x := IDispatch(myexcel.defaultinterface);
  x.visible := True;
end;
0
 

Author Comment

by:scandiumza
ID: 17036067
Oh okay, I see now. Sorry, not too clued up with COM programming.

Thanks again!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

762 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