I found this in a previous tread.

* Blair- Create a new column in SYSDICT called TAB. Make it a symbolic, where @ans=char(9). Change your statement to : OR_PRTF "" "LIST ORDER WITH BROKER EQ 15 AND WITH BALANCE TOTAL BALANCE TAB ITEM1 TAB NOTE1 ID-SUPP" "C:\TEST.XLS" Excel will know what to do. BTW- This also works in Arev, using PDISK. Hope it helps- Mike Ruane WinWin Solutions Inc. WWW.WinWinSol.Com *

I tried it but it does not work. Where did i go wrong? Anybody can help out?

what didn't work? what did the output look like? did you look at the output?

Try changing the

@ANS=CHAR(9)

to

@ANS=CHAR(34):CHAR(9):CHAR(34)

It still put all the columns to the first column in excell.The only difference is that now it got "" for the tab.Thats the 2 char(34) right? Previously its just blanks.

It put all the columns into the first column in excell.The tab is blanks between the other columns.Its suppose to put the columns into different columns in excell by using this tab which is the char(9).

William,

Have you tried looking at Run_Report? You can pass the same statement in Run_Report without having to use the Tab sysdict item. I don't have the Help at my fingertips at the moment but if you search the Help on Run_Report it will provide you with the details on creating reports to files.

Sean

did you edit the text file? what is actually between the 'columns' .. tab or spaces?

i just tried this and it appears to me that spaces are between the columns not tabs .. so THAT is why it puts it all into the first column

I use the Run_report method passing in CSV with the name of the file.

If you use a file with a "CSV" extension then Excel will automatically import this using a set of assumptions which are not necessarily correct. If you pass in Zip Code for instance, it will assume the data is numeric and strip any leading zeros.

If you use a file with a "TXT" extension then Excel will put you into an Import Wizard where you can control exactly what you want to happen. It does add extra steps but its very easy to train. You select delimitted, comma, then highlight all the columns and select "Text" then all the data comes in as expected.

I think that is the only "majic" that I have been able to figure out.

HTH,

John

Basically what you want is to have the resulting txt file to look like this, except the commas should be tabs…

"12345.67","XXX","YYY","ZZZ"

"222.22","XXX","SSS","AAA"

So what you need is to have a QUOTE symbolic being @ANS=CHAR(34) and also have a TAB sumbolic being @ANS=CHAR(34):CHAR(9):CHAR(34)

Now the statement should be like…

OR_PRTF "" "LIST ORDER WITH BROKER EQ 15 AND WITH BALANCE

QUOTE TOTAL BALANCE TAB ITEM1 TAB NOTE1 QUOTE ID-SUPP" "C:\TEST.XLS"

thanks john .. i think

My reply to William was to explain why this 'export' didn't work.

And by the way .. naming really has nothing to do with it. We have a client which we continually import export data from a system they bought which can really only work with their extension .. it exports to CSV (quoted text only) as a .imp file

Excel handles this equally well.

just a fyi

dsig

1) of course if he had looked at the data he would have seen there were no leading quotes which is why excel got lost. always look at the data

2) you only need the quotes IF you have tabs in your data .. otherwise excel is happy to import .. tab delimited coma delimited (or just about any character)

in fact .. without adding tabs, quotes etc .. he could do the report and use excels fixed length ability .. as spaces are used for padding .. should work (but not in run report "TXTFILE" .. no padding there

dsig

Understood.. I was just pointing out that a "csv" extension will be interpreted by Excel a certain way. I didn't mean to imply that only a "txt" extension would force the import wizard. This is just how I handle it.

What would really be nice is if we could "datatype" the columns in the export file so Excel would know how to handle it without using an import wizard. Perhaps using a schema file or XML would accomplish this but that is way above the original question…

John

John,

You make a good point that while the created extension of a CSV file will be .csv, it should always be changed to .txt when importing to Excel for the reasons mentioned.

Now tell me what we can do about the individuals who demand that they be sent an "Excel" file, then refuse to learn how to use the import wizard? Of course, they are probably the same persons as turn off virus filtering for active content files…. This is the crux of why we are dissatisfied with either TXT or CSV report capture.

Thank you all for the respond.I have learnt a lot from all of u guys here.

After trying for some time, I tried writing a subroutine using oswrite instead and guess what, it works.With the extension being .xls the excell will just read it in and put them in its respective columns.Really sweet.Without the extension the wizard will come out asking questions on convertion.The only thing is that when i modify it and want to save, its type is still delimited text and not excell.I have to change the type myself to save it as excell.

I have also open the .xls file with notepad and it is spaces between my data.So why did the or_prtf did not work?

I have not try the run_report.

Thanks again guys.

my guess as to why it did not work .. put spaces .. is that it is reducing it to a report .. not to a file as such. Just as all pdisk output was spaces ..

now that is only a guess .. some testing would problably prove the point that it is all it will output OR show that my guess is incorrect ..

testing and looking at the data ..

OR .. rti could look at the source and see why it is outputing spaces and not tabs

or_prtf routine will be a legacy routine that enabled access to Report_Builder reports etc and which existed pre OIPI. My guess is that because it is probably NOW passing thru to OIPI some things get handled differently - and the Tab gets stripped or replaced by a ' '.

BTW if you wanted to try an alternative method of getting data into Excel you could look at DDE (Chapter 6 on help) and examples in the Knowledgebase.

With Oi 7.2 you can drive Excel nicely from OI.

For Example, you can load data from an edit table into a spreadsheet and graph it.

Use form Designer to create a new window, name it TEST_OLE_EXCEL

Put an Edit table named TABLE_1 on the window, give it 2 columns named "Label" and "Value"

Put a button on the Window, put the following code on the click Event:

/* Start of Code */

/*

** Drive Excel Graph from OI table

*/

$insert Logical

err=0

equ xlArea to 1

equ xlBar to 2

equ xlColumn to 3

equ xlLine to 4

equ xlPie to 5

equ xlRadar to -4151

equ xlXYScatter to -4169

equ xlCombination to -4111

equ xl3DArea to -4098

equ xl3DBar to -4099

equ xl3DColumn to -4100

equ xl3DLine to -4101

equ xl3DPie to -4102

equ xl3DSurface to -4103

equ xlDoughnut to -4120

equ vbRdto to 255

equ xlSheetVisible to -1

common /TEST_OLE_EXCEL_COMMON/xlapp,xlwkb,xlSht,XlChartObject,XlChart,initialized

if assigned(xlApp) else

 xlapp='

 xlwkb='

 xlSht='

 XlChartObject= ''

 XlChart='

 initialized='

end

if initialized else

xlApp=OleCreateInstance("excel.Application")

OlePutProperty(XlApp, 'Visible', xlSheetVisible)

if OleStatus() then Goto HadError

end

if initialized else

xlWorkBooks =OleGetProperty(xlApp, "Workbooks")

xlWkb=OleCallMethod(xlWorkbooks,"Add")

if OleStatus() then Goto HadError

end

if initialized else

xlSht=OleGetProperty(xlWkb, "Worksheets",1)

if OleStatus() then Goto HadError

end

* Add(Left As Double, Top As Double, Width As Double, Height As Double)

if initialized else

charts=OleGetProperty(xlSht, 'ChartObjects')

xlChartObject=OleCallMethod(Charts, 'Add', 100, 100, 400, 200)

xlChart=OleGetProperty(xlChartObject, 'Chart')

if OleStatus() then Goto HadError

end

OlePutProperty(xlChart, 'ChartType', xl3DPie )

if OleStatus() then Goto HadError

list=.Table_1-]List

limit=.Table_1-]Limit

rowlimit=limit

rowcount=0

for i=1 to rowlimit

  label=list

  value=list

  if len(label) then            

    range=OleGetProperty( xlSht, 'Range',"A":i)            

    OlePutProperty(range, 'Value', label)

    if OleStatus() then Goto HadError

    range=OleGetProperty( xlSht, 'Range',"B":i)

    OlePutProperty(range, 'Value', value)

    if OleStatus() then Goto HadError

    rowcount +=1

  end

Next

* Graph the data

if rowcount then

range=OleGetProperty( xlSht, 'Range',"A1","B":rowcount)

x=OleCallMethod(xlChart, 'SetSourceData', range)

if OleStatus() then Goto HadError

end

initialized=true$

RETURN 0

hadError:

xlChart='

Charts='

range='

xlSht='

xlWkb='

xlWorkBooks='

x=OleCallMethod(xlApp, 'Quit')

return 0

/* end of code */

Now for the neat bit:

On the poschanged event of the edit table, put a quickevent to

send the CLICK event to the button. Change data or add rows to the edit table,

the graph will show the new values.

FANTASTIC! This is all news to me , I dont know about anyone else.

Bob, any chance of getting this into Knowledge Base, so we can find it quickly.

Bob,

This is EXCELLENT. Very neat.

Yes, any chance that this be documented soon. I am imagining ways to make things easier and "cooler". Just as this can drive "Excel". Are the principles the same for Word?

Many thanks for the info.

Ray Chan

Hi Ray

Yes, the same principles apply to Word, Outlook, ADO, MsXml, etc. The Visual Basic for Applications (VBA ) window is available under tools / Macros / Visual basic Editor in Excel or Word. I usually google for something like 'Excel vbscript Example', translate to OI, then use the VBA macro recorder to build what I cannot find in google. You can use the VBA immediate window to find the value of constants. For example, to find the value of wdReplaceAll, you type ?wdReplaceAll in the VBA immediate window.

The Excel example is something I cooked up for a recent Spectrum show. I also have an Excel spreadsheet which uses Xrev.dll plus a little VBA to implement Xlate. It's fun to see '=Xlate("RATES",$A4,B$1)' in the formula window. Using Excel's SheetChange Event, Excel updates OI whenever I change a cell. You could do the similar things in Word or Outlook. OI-driven workflow applications anyone?

Note that Ole Automation, like ODBC, is a legacy approach. Before you do too much with it, look at saving documents in the MSOffice XML formats, then using OSREAD, SWAP, OSWRITE and Utility("RUNWIN", filename) to create spreadsheets and word docs on the fly. You can accomplish a lot with simple text operations.

Will probably take these examples to Rev Roadshows, crank out a whitepaper after I clean up the examples.

BTW

I have started doing mail merges by just typing the document as a regular word file, using {FIELDNAME}, then using OLE automation to do a global replace of "{FIELDNAME} with {FIELDNAME}

It looks something like:

* … Assume an array of colnames and values set elsewhere, plus

* outfile is a path/filename to copy of the original word doc ….

* Automate swap of '{}'?

delim_start={'

      delim_end=}'

oWrd=OleCreateInstance("Word.Application")

oDocuments=OleGetProperty( oWrd, 'Documents')

oDoc=OleCallMethod(oDocuments, 'Open', OutFile)

oSelection=OleGetProperty(oDoc, 'SELECTION')

oFind=OleGetProperty(oSelection, 'Find')

oReplacement=OleGetProperty(oFind, 'Replacement')

colcount=count(colnames, @vm) + ( colnames # "" )

ConvDT  =0

for i=1 to colcount

	colname=colnames

	token  =delim_start : colname : delim_end

	value  =values[i]

	Convert @vm to char(10) In value

	x=OleCallMethod(oFind,'ClearFormatting')

	x=OleCallMethod(oReplacement,'ClearFormatting')

	OlePutProperty(oFind, 'Text',  token)

	OlePutProperty(oReplacement, 'Text', value)

	OlePutProperty(oFind, 'Forward', True$)

	OlePutProperty(oFind, 'Wrap',  wdFindContinue)

	OlePutProperty(oFind, 'Format', False$)

	OlePutProperty(oFind, 'MatchCase', False$)

	OlePutProperty(oFind, 'MatchWholeWord', False$)

	OlePutProperty(oFind, 'MatchWildcards', False$)

	OlePutProperty(oFind, 'MatchSoundsLike', False$)

	OlePutProperty(oFind, 'MatchAllWordForms', False$)

	x=OleCallMethod(oFind, 'Execute','Replace:=:wdReplaceAll)

Next

return

MSWORD

]]crank out a whitepaper after I clean up the examples

Any idea on a timeframe?

What are the values for - wdReplaceAll and wdFindContinue

View this thread on the Works forum...