Formatting issue with Excel Automation

[mbh27]

When exporting fields (from dbf via a csv file) into Excel a serial number field (defined as C(25)) is displayed in Excel with scientific notation - if the serialno field exceeds a certain no of digits. I've tried formatting the cells as character (loCellRange.Numberformat = "@") during the Automation code in VFP but to no avail.

When this spreadsheet is read back to update the original dbf file, the serialno field is then changed from something like "12345678901234567890" to "1.234567890000E18".

I've tried prefixing the serialno data with a single quote mark just before it's read into excel (from the csv file) in the hope that excel would interpret the cell as character. This works, but the single quote mark is displayed in excel and is then imported back into the dbf file - thus changing the original data when it is not required.

Any suggestions as to how I can trick Excel into doing what I want? i.e. display the long string of digits as character and reading them back into VFP in the same format.

 

[StewartUK]

Dear mbh27,

Try putting a ' before the serial number. This tells Excel to treat a number as text.


Hope that helps,

Stewart
PS If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[JRB-Bldr]

When Excel displays numbers in scientific notation it is often due to the Column width being too narrow to display the entire number.

Try using VFP automation to adjust the column width.
If you have questions you might want to look at faq184-4428

Good Luck,
JRB-Bldr

 

[mbh27]

Thanks for these responses.

StewartUK - you'll see that I have tried putting a ' in front of the serial number. Whereas if you do this directly in the cell when in Excel it does, as you say, interpret what follows as character data. In this case, though, when the data is imported from the CSV file, it also shows the ' in front of the number. (It does show the whole number as character - but I must find some way in Automation of removing the ' once it has served it's purpose, before I import the data back into the DBF file.)

jrbbldr - I do, in fact, increase the column widths through automation but this happens after the data is in the spreadsheet. At this point, increasing the width has no other effect on the data.

Is there any bright spark out there that could offer anything else on this subject?

 

[JRB-Bldr]

"I do, in fact, increase the column widths through automation but this happens after the data is in the spreadsheet"

The column width should be adjusted after the data is in the worksheet.

Do you Auto-Fit the column width or merely adjust the width?
If not Auto-Fit, then try doing that.

If you use the VFP TRACE window, you should be able to watch the results of each separate line of Excel automation code and evaluate the results.

"before I import the data back into the DBF file"
Are you going through all of this Excel manipulation just to import the CSV data into a VFP data table?

If you are, then you might want to forget the Excel work altogether and do an APPEND with the appropriate option.

Good Luck
JRB-Bldr

 

[mbh27]

Thanks jrbbldr - some interesting points.

In this case I'm not using Autofit because I want to specify more precisely the width of the columns. Autofit has a good try but I want consistency rather than relying on the data in each sheet.

In outlining the problem I've tried to give just the essentials rather than the full picture. To be more explicit, what happens is:
1. Data drawn from DBF files and exported to a series of spreadsheets.
2. The XLS sheets are emailed to various other people (who have no interaction with the VFP application).
3. Certain items of data are updated by these other people using Excel.
4. The updated XLS sheets are emailed back to a specified 'import' folder.
5. The VFP app then uses the data in these spreadsheets to automatically update the DBF files.

I know it may seem as if there's lots of unnecessary changes of format, but, apart from this one issue, it would all work beautifully. I want the 'other people' to be able to use Excel to update their lists (it's a stockbook application), and I want the base data to be held in DBF format.

The problem at the moment is that to ensure the serialno is interpreted by Excel as character data I can prefix it with ' - but Excel insists on displaying the ' rather than treating it as a kind of 'meta-character'. Once we're in Excel I don't want to see the ' , and I certainly don't want it there when we update from Excel back into DBF.

Hope this rather lengthy explanation gives more of idea of what I'd like to do.

 

[EzLogic]

I use this in my automation code, and i never had an issue of my serial numbers not being treated as Characters.

local lnRow
IF TYPE("oExcel") <> 'O'
	PUBLIC oExcel
	oExcel = CREATEOBJECT("excel.application")
	oExcel.visible = .t. 
ENDIF 

oSheet = oExcel.ActiveSheet
Select a_ICIQTY
lnRow = 2  && omit the header.
Scan 
   oSheet.cells(x,1).value = ['] + alltrim(a_ICIQTY.qSerial)
   lnRow = lnRow + 1
endscan

Ali Koumaiha
Wireless Toyz
Farmington Hills, Michigan

 

[SnyAc]

Ali... that alias looks very familiar.... SBT AccPac ?

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

Hyperware Inc.
AmTech Software Inc.

 

[Olaf Doschke]

(loCellRange.Numberformat = "@")

Looks like you're setting NumberFormat for only a cell, while you want to set it for the whole column.

And in the online reference Numberformat is an object, so I wonder if this works at all.

Start excel, load some csv, start recording a macro, select a column by it's header, format it to be string format, stop macro recording and see at the code generated on how to do it.

Bye, Olaf.

 

[EzLogic]

Hey SnyAc,

Indeed, Pro series 7.5

how about a_icserl? and a_icitem?

Ali Koumaiha
Wireless Toyz
Farmington Hills, Michigan

 

[mbh27]

What I've decided on the end (though I know it's not a very elegant solution) is to add a ' to the front of the serialno in the CSV file - if the serialno is all numeric. When imported to Excel this is then interpreted as character data (even though the ' is displayed in the cell). If the user edits the cell in Excel and removes the ' the serialno is still displayed as character. When the spreadsheet is used to update the dbf files in the VFP application the leading ' (if present) in those cells are ignored.

As I say - not elegant, but it's one way of achieving what I want. Any better solutions will be gratefully received.

 

[JRB-Bldr]

Interesting...

I would have thought that all serial numbers would be the same number of text characters - unless you were recording SSN's for a wide variety of manufacturer's products.

Regardless, your Excel column must be wide enough to handle the worst case (longest SSN).

It wouldn't be too hard to just Auto-Fit Column width on that specific column.

Good Luck,
JRB-Bldr

 

[mbh27]

There's actually no consistency in the length or format of these serial nos. Many items may not even have a serial number at all. With Autofit I found that the column width was configured to the width of the first item in the list rather than the width of the longest item in the set. The max length of a serialno is 25 char, but few will actually be this long. Instead, I explicitly set the column width to be long enough to allow 25 characters/digits to be displayed/edited rather than using the AutoFit approach.

Apart, now, from displaying an unwanted quote mark on the numeric serial nos in Excel, everything works ok - but I can live with this for the time being.

 

[JRB-Bldr]

"With Autofit I found that the column width was configured to the width of the first item in the list"

Actually that is not true, or not that I have experienced.
Instead, Auto-fit will make the column as wide as the widest cell value (full expanded) within the entire selected Range. To make that happen I select the entire column.

Regardless, do what works for you.

Good Luck,
JRB-Bldr

 

[NasibKalsi]

Not so elegant, but may be acceptable.


Create a t_temp table with serialno lenth to 26. In the 26th postion add tab(chr 9) or any other invisible character except space, or even single quote.

like

replace all serialno with left(serialno,25)+chr(9)

Make sure excel column is formatted as text. Now bring this csv file to excel, play with it as you like, and import back to the original table where serialno length is 25, or run a replace statement to remove the unwanted char.

Note: Excel treats a field as a string if there is non numeric char(s) anywhere i.e. beginning, middle or end.

Hope this may help.

Nasib