import difficulty

[martinwcole]

client has an Excel file in office xp

one of the fields stores all the html code for the web page for the item in inventory - maybe 200-600 characters

if i save the ecel file as an excel 4.0, it cuts off all but about 100 characters of the field.

any ideas on how i might get all of the data from that field to an alpha memo field

[AaronBBrown]

RE: import difficulty

can you save it as a DBF file? Preferably Foxpro 2.6.

[Stan Mathews]

RE: import difficulty

I've had luck with dBASE III or IV as the save as, Foxpro is not a listed choice on my Excel version.

[martinwcole]

RE: import difficulty

i can save as dbase3, and alpha can read it, but the filed is truncated - i can't figure out any way, so far. It zips up to a tiny file (about 114 records) which i will include, if anyone wants to take a crack at it.

i've tried everything i can think of

[Tim Stephens]

RE: import difficulty

I don't know how comfortable you are in OLE, but the only way I found to import your data was to write an ole script.

It goes like this:

dim xl as p
dim xlBook as p

dim record as n
dim row as c
dim col as c

constant MAX_RECORD = 16

' lets go ahead and create a new instance of excel
xl = ole.create("Excel.Application")

' set up some error handling so we dont get in too much trouble
ON ERROR GOTO error_handler

tbl = table.open("ztest",FILE_RW_EXCLUSIVE)

' open the template xls file
xlBook = xl.Workbooks.Open("c:\inventory.xls")

xl.Windows(1).Visible = .f.

record = 2
row = ltrim(str(record))
col = "B"

' import just the memo column
' can add more columns to import here
while record

[martinwcole]

RE: import difficulty

Now that is awesome
It is also a lefthanded way to deal with modern excel files

[Tim Stephens]

RE: import difficulty

Sorry, didn't realize it was still truncating (but not as much), I have fixed the script and it should no longer truncate.

Here's the new script:
I changed the line

fld.value_put( xl.ActiveCell.Value() )

to

xl.activeCell.copy()
fld.value_put( :clipboard.get_data() )

The problem was xl.ActiveCell.value() returned a truncated portion of the cell, the .copy() puts the whole value into the clipboard, and alpha lets us take the value from the clipboard using the :ClipBoard object.

PS. To get all the commands you can use with excel, open excel goto Tools->Macro->VB Editor, from the editor look in the object explorer. You can use most of the commands available to VBA in ole, but some adjustments in syntax may be necessary when written in xbasic.

Attached is the completed script that imports all the fields into a table called ztest. (note: it still may not work perfectly, it may need some work)