ActiveX = OLE object?

[MoGrace]

I'm looking at the help to try and embed an Excel spreadsheet on a form. I tried to insert several of the Excel OLE objects and no matter which I choose I get:

Can't insert OLE object: comm

Is something missing in my v11 app? Or is something amiss in my 2003 Excel?

[Stan Mathews]

Re: ActiveX = OLE object?

Open Excel first. Go back to Alpha and insert the ole object.

[MoGrace]

Re: ActiveX = OLE object?

Thanks Stan, I'll try that.

[MoGrace]

Re: ActiveX = OLE object?

OK tried that and got a spreadsheet to appear, but I was hoping to link to an existing spreadsheet and it seems like too much for me to be able to do.

So - Stan, if you are here; how would you go about creating a spreadsheet that lists the fields in one column and the data in another instead of in the normal row format like you get with an export? Cause that's what I need to create with my Alpha data - one record at a time.

[Stan Mathews]

Re: ActiveX = OLE object?

I was hoping to link to an existing spreadsheet

When the ole opens you see an empty spreadsheet? You can open an existing spreadsheet and copy/paste into the ole'd object. Close the existing and the ole'd .

Is that sufficient?

[MoGrace]

Re: ActiveX = OLE object?

I'll try that - tomorrow, thanks

[Stan Mathews]

Re: ActiveX = OLE object?

You might also try opening Excel to the document you want to link. I haven't used the functionality in a while and that seems like what worked best.

[Mike Wilson]

Re: ActiveX = OLE object?

how would you go about creating a spreadsheet that lists the fields in one column and the data in another instead of in the normal row format like you get with an export? Cause that's what I need to create with my Alpha data - one record at a time.

Are you meaning that you want to produce a two column spreadsheet - one column containing the table field names and the second column the field value - for one record. And you want to generate one spreadsheet with that two column configuration for every record in the table? If that's what you want... no problem, a temporary table and a little coding and it's as good as done. If that's not what you are looking for, then maybe an example of the table data and the resulting spreadsheet you wish to make would help.

[MoGrace]

Re: ActiveX = OLE object?

Yes Mike that was the idea. I have a PDF editor / form filler program that will import from an Excel or .csv file - so i'm trying to get it working on a pdf file that is otherwise not editable. At this rate I will probably just go back to my old way of scanning the file as an image and laying my fields on top.

Unless of course you can give me a hint or two...!

[Stan Mathews]

Re: ActiveX = OLE object?

Should do it.

tdir = FILE.TEMP_PATH_GET()
table.create_begin("fieldname","c",30)
table.field_add("fieldvalue","c",30)
desttbl = table.create_end(tdir+chr(92)+"sample.dbf")
srctbl = table.open("atest")
flds = srctbl.field_name_get()
srctbl.fetch_first()
while .not. srctbl.fetch_eof()
	
for each foo in flds
	desttbl.enter_begin()
	desttbl.fieldname = foo.value
	desttbl.fieldvalue = convert_type(eval("srctbl."+foo.value),"C")
	desttbl.enter_end()
next

srctbl.fetch_next()
end while
srctbl.close()
desttbl.close()
file_add_to_db(tdir+chr(92)+"sample.dbf")

This creates the fieldname - fieldvalue table for any srctbl = table............

The export is then routine.

[MoGrace]

Re: ActiveX = OLE object?

Thanks Stan, I'll give it a try!

[Stan Mathews]

Re: ActiveX = OLE object?

Did some testing.

tdir = FILE.TEMP_PATH_GET()
table.create_begin("fieldname","c",30)
table.field_add("fieldvalue","c",30)
desttbl = table.create_end(tdir+chr(92)+"sample.dbf")

This creates a new table each time and wipes out the support files Alpha would create should you define an export. You would need to save the export xbasic and use that. Alternatively create your own destination table and remove that section of the code, zapping the destination table at the beginning of the code.

[MoGrace]

Re: ActiveX = OLE object?

This is a test form I made to help me visualize what I am trying to do - I filled in the table by copying and pasting the data - which I am sure could be scripted with a push of a button. However that is easier said than done since I have 4 tables in this set. So I thought perhaps an array could capture the data displayed on the left side of the form and then fetch thru the table on the right and change those records. This is the part that is causing me some trouble since I have to fetch thru both the array and the table. Initially I have to produce 30 forms, but after that it will only be one at a time for each new hire.

testexcel.jpgdlse_rep.jpg

[MoGrace]

Re: ActiveX = OLE object?

Here is my first try...

dim hdr as p
dim tbl as p
dim lst as c
dim newlst as c
dim ix as n = 1
dim tName as c
dim tData as c
hdr = table.current() 
tbl = table.get("dlse_tmp")

lst = topparent.Child_Enum("t;f=field")
cnt = line_count(lst)
newlst = *for_each(foo,eval("topparent:"+foo+".value"),lst)
DELETE arr
dim arr[cnt] as c
arr.initialize(newlst)
[COLOR="#FF0000"]'This is working Ok now[/COLOR]
tbl.fetch_first()
while .not. tbl.fetch_eof()	
	tbl.change_begin()
		tbl.FIELD_DATA = arr[ix]
		[COLOR="#0000FF"]tName = tbl.field_name    'this is just to output to the trace window
		tData = tbl.field_data
		Tln(tName,tdata)[/COLOR]
	tbl.change_end(.t.)	
        ix = ix+1
        tbl.fetch_next()
end while	

NEXT

Being the dumbest person in the room is humbling. The FOR...NEXT seemed like a good idea at the time.

[MoGrace]

Re: ActiveX = OLE object?

Working Ok is a bit subjective given this is the data from newlst:

?newlst
= Gutierrez, Elias
{04/01/2015}
S. K. Knits, Inc.
1745 Chapin Rd., Montebello, CA 90640
(323) 890-9080
9
13.5
Friday
Insurance Company Of The West
P. O. Box 85563, San Diego CA 92186-5563
(877) 442-9669
WVE-5029819-00
Robin Quinn
Elias Gutierrez
{04/21/2015}
{04/21/2015}

The source table is passing numeric and date values to a string and the target fields are all character. How can I manipulate the string before I initialize the array so I can display those fields properly as text when they are exported to Excel? Or should I try a CSV file instead?

Hmmm...

EDIT: Well that was easy to fix - I passed the 'foo.text' instead of the 'foo.value'