SBP ' ************************************************************************* ' * MISDDE.SBP - Program for all DDE related procedures and functions. ' * ' * Release 4.10 ' * 26th November 1992 ' ************************************************************************* ' ************************************************************************* ' * Run the annual revenue DDE report ' ************************************************************************* DDERevenue() DDEReportSel(1)  ' ************************************************************************* ' * Run the annual stock DDE report ' ************************************************************************* DDEStock() DDEReportSel(2)  ' ************************************************************************* ' * DDEReportSel() - Allow the user to choose the year and the application ' * that should be reported and where DDE channels ' * should be created ' * ' * ReportNO%% - Holds the corresponding number of the report to run ' ************************************************************************* DDEReportSel(ReportNO%%)  SelectDATE$(4),RetVAL$(0) Application%%1 BaseYear%%6(Q) RetVAL$(0)(BaseYear%%,"9999") Loop%%0q4  LoopYear%%BaseYear%%(4Loop%%)  SelectDate$(Loop%%)(LoopYear%%,"9999") Loop%% "DDEREPORT",Response%% Response%%pExtractDATA(Application%%,ReportNO%%,RetVAL$(0))  ' ************************************************************************* ' * ExtractDATA() - Extract Data For DDE, Linking VIDEO, ITEMS & ORDER To ' * Extract appropriate And Save This In A Temporary File ' * So That It May Be Exported To An Excel File. ' * ' * Application%% - Holds The Number Of The Application To Send Report To ' * ReportNO%% - Holds The Number Of The Extraction Routine To Run ' * ReportYear$ - Holds The Year For Which The report Should Be Run ' ************************************************************************* ExtractDATA(Application%%,ReportNO%%,ReportYear$) e MISDir$ TempFILE$,TempINDEX$ TempFILE$ TempINDEX$ OJ("REPORT.SBD")p  "REPORT"  |"Category;TXT IXD;60;1;1"  |"QuantitySold;NUM;9999999999.;0;0"  |"Turnover;NUM;$ 9999999999.;0;0;"  "REPORT"  "REPORT"  ,8"REPORT" a"REPORT" i  Category.VIDEO Category.VIDEO  "REPORT"  Category.REPORTCategory.VIDEO  }Category.VIDEO  ,1"REPORT"  }  QuantitySold.REPORTQuantitySold.REPORTOrderQuantity.ITEMS  Turnover.REPORTTurnover.REPORTTotalPrice.ITEMS  ;  RecordKEY.ORDERRecordKEY.ITEMS VideoCode.ITEMSVideoCode.VIDEO 6(OrderDate.ORDER)0(RetVAL$(0))  Category.VIDEO  q "Extracting data"  ,2 ie  t "REPORT" A("")p  ReportNO%%  1  AnnualRev(Application%%,ReportYear$)  2  AnnualStock(Application%%,ReportYear$)  ^  ^  "No data available for "ReportYear$,"",113  TempFILE$ TempINDEX$ "REPORT"  ' ************************************************************************* ' * AnnualRev() - Selects The Fields To Be Graphed For The Year To ' * Date Revenue Report ' * ' * Application%% - Holds The Number Of The Application To Send Report To ' * ReportYear$ - Contains The Year For Which The Report Is Being Run ' ************************************************************************* AnnualRev(Application%%,ReportYear$) TempTitle$ Category,Turnover q"REPORT.XLS" DDESelect(Application%%,"\$#,###;[RED]-\$#,###","Revenue",1)  ' ************************************************************************* ' * AnnualStock() - Selects The Fields To Be Graphed For The Year To ' * Date Stock Sold Report ' * ' * Application%% - Holds The Number Of The Application To Send Report To ' * ReportYear$ - Contains The Year For Which The Report Is Being Run ' ************************************************************************* AnnualStock(Application%%,ReportYear$) TempTitle$ Category,QuantitySold q"REPORT.XLS" DDESelect(Application%%,"General","Stock Sold",2)  ' ************************************************************************* ' * DDESelect() - Depending On The Value held In Application%%, Load ' * Microsoft Excel Or Harvard Graphics. ' * ' * Application%% - Holds The Number Of The Application To Send Report To ' * FormatNo$ - Contains The Numeric Format For The Data To Be Graphed ' * ColTitle$ - Holds The Title For The Second Column Of Data ' * ReportNo%% - Contains The Number Of The Report Being Run ' ************************************************************************* DDESelect(Application%%,FormatNo$,ColTitle$,ReportNo%%) Application%% 1  DDEExcel("REPORT.XLS",FormatNo$,ColTitle$,ReportNo%%) 2  DDEHGW(ReportNo%%) ^   ' ************************************************************************* ' * DDEExcel() - This Procedure Creates DDE Links To Microsoft Excel, ' * Manipulates The Data In That Spreadsheet And Also Graphs ' * The Data that Has Been Exported From Superbase. ' * ' * FileNAME$ - Holds The Name Of The Excel Spreadsheet Exported For DDE. ' * FormatNo$ - Contains The Numeric Format For The Data To Be Graphed ' * ReportNo%% - Contains The Number Of The Report Being Run ' ************************************************************************* DDEExcel(FileNAME$,FormatNo$,ColTitle$,ReportNo%%) EXCELCOMM$,LoadedExcel%% StartExcel%%("",FileNAME$) 2  1  LoadedExcel%%0  RetVAL$""  DlgDDEExcelDIR$,"",4,Response%%,RetVAL$  -(RetVAL$,1)"\"pRetVAL$+(RetVAL$,/(RetVAL$)1)  Response%%p J(RetVAL$)p RetVAL$RetVAL$"\" StartExcel%%(RetVAL$,FileNAME$)32p LoadedExcel%%1  ^ "EXCEL.EXE was not found in :",RetVAL$,113  ^ "EXCEL.EXE was not found in :",RetVAL$,113   ^    0,1,3q32  "Error starting Excel!!!","",113 ^  LoadedExcel%%1    *******************************************************  * If Excel was loaded OK, use DDE to format spreadsheet  ******************************************************* LoadedExcel%%p  RowNo%%A("REPORT")  DEBUG ON ,100 ASK  1,"EXCEL",FileNAME$  1,"[FULL(TRUE)]  "{ESC 2}"  DDEEXEC 1,"[SELECT(" + CHR$ (34) + "R2C1:R2C2" + CHR$ (34) + ")]"  DDEEXEC 1,"[INSERT(2)]"  1,"[SELECT("(34)"R1C1"(34)")]"  1,"[FORMULA("(34)"Category"(34)")]"  1,"[SELECT("(34)"R1C2"(34)")]"  1,"[FORMULA("(34)ColTitle$(34)")]"  1,"[SELECT("(34)"R"H((RowNo%%2,"999"))(34)")]"  1,"[FORMULA("(34)"Total"(34)")]"  1,"[SELECT("(34)"R"H((RowNo%%2,"999"))"C2"(34)")]"  1,"[FORMULA("(34)"=SUM(R[-"H((RowNo%%,"999"))"]C2:R[-1]C2)"(34)")]"  1,"[SELECT("(34)"R"H((RowNo%%2,"999"))(34)")]"  1,"[FORMAT.FONT("(34)"Helv"(34)",10,TRUE,FALSE,FALSE,FALSE,3)]"  1,"[SELECT("(34)"C1"(34)")]"  1,"[COLUMN.WIDTH(,,,3)]"  1,"[SELECT("(34)"C2"(34)")]"  1,"[FORMAT.NUMBER("(34)FormatNo$(34)")]"  1,"[COLUMN.WIDTH(,,,3)]"  1,"[SELECT("(34)"R"H((RowNo%%2,"999"))"C2"(34)")]"  1,"[SELECT("(34)"R1C1:R"H((RowNo%%2,"999"))"C2"(34)")]"  "^{F11}"  1   ' ********************************************************************** ' *** StartExcel%%() - Use WinExec to cleanly start excel ' ********************************************************************** StartExcel%%(PathName$,FileNAME$)  "KERNEL.EXE","WinExec","ICI" StartExcel%%("WinExec",PathName$"EXCEL "FileNAME$,1)   ' ************************************************************************* ' * DDEHGW() - Envoke Harvard Graphics with a specific .PRS file ' * depending on the DDE report that has been run. ' * ' * ReportNo%% - Contains the report number that is to be run ' ************************************************************************* DDEHGW(ReportNo%%) ReportNo%% 1  HGWFile$"HGWDDE1.PRS" 2  HGWFile$"HGWDDE2.PRS" ^  StartHGW%%("",(MISDir$HGWFile$)) 2  1  RetVAL$""  DlgDDEHGWDIR$,"",4,Response%%,RetVAL$  -(RetVAL$,1)"\"pRetVAL$+(RetVAL$,/(RetVAL$)1)  Response%%p J(RetVAL$)p RetVAL$RetVAL$"\" StartHGW%%(RetVAL$,(MISDir$HGWFile$))32p  ^ "HGW.EXE was not found in :",RetVAL$,113  ^ "HGW.EXE was not found in :",RetVAL$,113   ^    0,1,3q32  "Error starting Harvard Graphics!!!","",113 ^    ' ********************************************************************** ' *** StartHGW%%() - Use WinExec to cleanly start HGW ' ********************************************************************** StartHGW%%(PathName$,FileNAME$)  "KERNEL.EXE","WinExec","ICI" StartHGW%%("WinExec",PathName$"HGW "FileNAME$,1)