Unlock the Power of MEDITECH with MEDITECH KE

2

I have a few NPR reports that I open using the shell function call from a macro. This does not allow for any access to the excel object itself, so it's limited. Does anyone have any suggestions on building a vbscript from a macro to create the excel object, writing data from the report to that object, writing to pc and executing?

flag

3 Answers

2

Another possible solution to this problem is to use XML. When you save a spreadsheet as XML you can then open it in another editor to read the output and then have NPR put something similar out.

This XML file will output a very simple spreadsheet. you could then execute a vbs to convert this to XLS or file > open in excel will open this directly with formula's.

 <?xml version="1.0"?>
 <?mso-application progid="Excel.Sheet"?>
 <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"
   x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="Number">1</Data></Cell>
    <Cell ss:Formula="=RC[-1]"><Data ss:Type="Number">1</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">2</Data></Cell>
    <Cell ss:Formula="=RC[-1]"><Data ss:Type="Number">2</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>
link|flag
That's an excellent way to go for us programmers. Thank you for sharing, I'm sure I'll be using that in the near future as well! – John Sharpe♦ Apr 22 at 18:23
This also works for word documents. Simple way to create a mail merge type system. – Shane T♦ Apr 22 at 19:32
That's great! I'll have to try this one out! – Larry Carver♦ Apr 22 at 20:34
1

Ok, this is what I've come up with so far...use a macro to build a vb script, enabling access to the excel object, and building the excel sheet. Use the shell function to run the script and launch the workbook. Seems a little time consuming...and this doesn't include the report detail. Maybe someone could find some use for it...if I have to use a script, I'd rather use the script to run the report, instead of the report writing, then running the script.

IF{'/ONLY.ONCE 1^/ONLY.ONCE,@RUN},
EXIT;

RUN
%Z.pc(0),
IF{/.PC="N" @W.err(" Must use PC Workstation to create Excel. ");
@CK.FOR.EXCEL=0;
@BUILD.VBS,
@W.display(" Writing file to PC ..."),
@WRITE.PC.FILE}

CK.FOR.EXCEL
IF{%Z.dos.filename.valid("C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE")^CK,CK|1=1;
%Z.dos.filename.valid("C:\Program Files\Microsoft Office\OFFICE12\EXCEL.EXE")^CK,CK|1=1;
@W.err(" Error: Can not find EXCEL on PC. "),0}^RETURN,
RETURN

BUILD.VBS
@.today_@.now^/TDY,
""^/CNT,
"C:\test"/TDY".xls"^/PATH,
"Option Explicit"^/VBS[CNT+1^CNT],
"Dim strExcelPath, objExcel, objSheet, k, objGroup"^/VBS[CNT+1^CNT],
"strExcelPath = "D(34)/PATH_D(34)^/VBS[CNT+1^CNT],
"On Error Resume Next"^/VBS[CNT+1^CNT],
"Set objExcel = CreateObject("D(34)"Excel.Application"D(34)")"^/VBS[CNT+1^CNT],
"If (Err.Number <> 0) Then"^/VBS[CNT+1^CNT],
"On Error GoTo 0"^/VBS[CNT+1^CNT],
"Wscript.Echo "D(34)"Excel application not found."_D(34)^/VBS[CNT+1^CNT],
"Wscript.Quit"^/VBS[CNT+1^CNT],
"End If"^/VBS[CNT+1^CNT],
"On Error GoTo 0"^/VBS[CNT+1^CNT],
"objExcel.Workbooks.Add"^/VBS[CNT+1^CNT],
"Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)"^/VBS[CNT+1^CNT],
"objSheet.Name = "D(34)"Accounts"_D(34)^/VBS[CNT+1^CNT],
"objSheet.Cells(1, 1).Value = "D(34)"Account #"_D(34)^/VBS[CNT+1^CNT],
"objSheet.Cells(1, 2).Value = "D(34)"Name"_D(34)^/VBS[CNT+1^CNT],
"objSheet.Cells(1, 3).Value = "D(34)"Admit Date"_D(34)^/VBS[CNT+1^CNT],
"objSheet.Cells(1, 4).Value = "D(34)"D/C Date"_D(34)^/VBS[CNT+1^CNT],
"objSheet.Cells(1, 5).Value = "D(34)"Financial Class"_D(34)^/VBS[CNT+1^CNT],
"objSheet.Range("D(34)"A1:E1"D(34)").Font.Bold = True"^/VBS[CNT+1^CNT],
;"objSheet.Select"^/VBS[CNT+1^CNT],
"objSheet.Rows("D(34)"2:2"D(34)").Select"^/VBS[CNT+1^CNT],
"objExcel.ActiveWindow.FreezePanes = True"^/VBS[CNT+1^CNT],
"objExcel.Columns(1).ColumnWidth = 10"^/VBS[CNT+1^CNT],
"objExcel.Columns(2).ColumnWidth = 20"^/VBS[CNT+1^CNT],
"objExcel.Columns(3).ColumnWidth = 12"^/VBS[CNT+1^CNT],
"objExcel.Columns(4).ColumnWidth = 12"^/VBS[CNT+1^CNT],
"objExcel.Columns(5).ColumnWidth = 20"^/VBS[CNT+1^CNT],
"objExcel.ActiveWorkbook.SaveAs strExcelPath"^/VBS[CNT+1^CNT],
"objExcel.ActiveWorkbook.Close"^/VBS[CNT+1^CNT],
"objExcel.Application.Quit"^/VBS[CNT+1^CNT],
"Set objGroup = Nothing"^/VBS[CNT+1^CNT],
"Set objSheet = Nothing"^/VBS[CNT+1^CNT],
"Set objExcel = Nothing"^/VBS[CNT+1^CNT]

WRITE.PC.FILE
IF{/.PRT'="VIEW"'="SPOOL" 1,
C(!)^SAVE,
IF{/.PRT="LOCAL" %Z.print.local("OFF"),1^LOCAL},
%Z.dos.write(^/VBS,"C:\excel.vbs",1)^RESULT,
ZZ%OP(!,SAVE),
IF{LOCAL %Z.print.local("ON")},
IF{RESULT="Done" @EXECUTE.VBS;
IF{LOCAL %Z.print.local("OFF")},
@W.err("Error Writing File: "_RESULT),
IF{LOCAL %Z.print.local("ON")}}}

EXECUTE.VBS
%Z.link.to.shell("C:\excel.vbs"),
@EXECUTE.EXCEL

EXECUTE.EXCEL
1^/R.NO.PRT.MSGS,
%Z.rw.close.up(0),
H(2),
%Z.link.to.shell(/PATH)

link|flag
That's a job well done. – John Sharpe♦ Mar 2 at 23:25
Thanks for tidying it up! It was a bit hard to read. Seems a long way to go to do something rather simple, but it opens the door to other considerations, I think. – Larry Carver♦ Mar 2 at 23:30
0

Come check out Larry Carver's Text Import Utility: http://www.comstock-software.com/Blog/bid/38655/MEDITECH-Download-Microsoft-Excel-Import-Utility.

link|flag

Your Answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.