EXCEL-Formulare gezielt ausfüllen

Einführung

Es gibt mittlerweile im SAS/System viele Möglichkeiten Daten in EXCEL-Spreadsheets darzustellen. Ein Sonderfall ist das Ausfüllen von EXCEL-Formularen. Dabei werden in vorbereiteten EXCEL-Sheets an definierten Positionen die aktuellen Werte geschrieben, ohne dass die gesamte Seite von der SAS-Anwendung selbst erzeugt wird. Im konkreten Anwendungsfall ging es um das Ausfüllen von Formularen des Verbands der privaten Bausparkassen. Dabei sind nur die Zellen frei gegeben, in denen die Bausparkasse die entsprechenden Werte eintragen soll. alle anderen Zellen sind vor einer Änderung geschützt.

Die Technik eignet sich aber auch für alle anderen Fälle, bei denen die Formulare in EXCEL selbst aufwendig gestaltet wurden, feststehen und nur bestimmte Zellen periodisch mit aktuellen Werten gefüllt werden sollen. 

Lösung

Mit dem DDE-Interface lässt sich EXCEL auch über die (alte) Macro-Sprache steuern. Während sonst eine Filename-Statement notwendig wäre, um jeden Bereich, in den SAS schreiben soll, explizit zu definieren, reicht ein Filename-Statement mit

	filename ddecmds dde "excel|system";

um beliebig viele EXECL-Sheets und -Blätter mit den entsprechenden Daten zu befüllen.

Die komplette Anleitung gibt es bei

	http://support.microsoft.com/support/kb/articles/Q128/1/85.ASP

(laut Google).

Für das Befüllen reichen aber 6 Befehle mit ein paar Varianten:

• [Open("dateiname")] lädt eine EXCEL-Datei (Mappe)
  
• [WORKBOOK.Activate("mappe")] aktiviert das entsprechende Blatt der EXCEL-Datei
  
• [A1.R1C1(FALSE)] schaltet auf die Adressierung nach Zeilen- und Spalten-Nummern um
  
• [Formula("HARRY","R1C2")] schreibt einen Text in die zweite Zelle der ersten Zeile
  
• [Formula("1.2","R2C4")] schreibt die Zahl 1,2 in die vierte Zelle der zweiten Zeile (unabhängig von der Spracheinstellung wird ein Dezimal-Punkt an EXCEL übergeben)
  
• [Formula("=R2C4/100","R2C5")] schreibt eine Formel in die fünfte Zelle der zweiten Zeile
  
• [A1.R1C1(TRUE)] aktiviert wieder die normale Addressierung des Spreadsheets (A1 usw.)
  
• [Close(TRUE)] speichert und schließt das Spreadsheet
  
• [Quit()] beendet EXCEL

Ein einfaches Testprogramm wäre:

/* DDE-Verbindung                                       */
Filename ddecmds dde "excel|system";

/* EXCEL starten (den Pfad anpassen)                    */ 
Options noxwait noxsync;
X "C:\Programme\Office2000\office\Excel";
Options xwait xsync;

/* Eine Sekunde auf das Starten von EXCEL warten        */ 
%Let rc = %sysfunc(sleep(1));

/* Datastep zum Testen der Befehle                      */ 
Data _null_ ;
   Input befehl $40.;
   File log;
      Put befehl=;
   File ddecmds  ;
      Put befehl +(-1);
/* Beispiel für zu testende Befehle                                   */
/* Die Datei test2.xls mit einer Mappe namens "Daten" muss existieren */ 
Cards;
[Open("F:\data\test2.xls")]
[WORKBOOK.Activate("Daten")]
[A1.R1C1(FALSE)]
[Formula("HARRY","R1C2")]
[Formula("Maria","R2C3")]                 * Text
[Formula("999.777","R3C4")]               * Zahl mit Dezimalpunkt
[Formula("1JAN2004","R4C4")]              * Datum, wie SAS-Datumswert
[Formula("=999777+566","R11C5")]          * einfache Berechnung
[Formula("=R10C4*2","R12C6")]             * Formel mit absoluter Adressierung
[Formula("=R[-1]C[0]+2000","R13C6")]      * Formel mit relativer Adressierung
[A1.R1C1(TRUE)]   
[Close(TRUE)] 
[Quit()]
;

Aber für einen produktiven Einsatz bietet sich der Einsatz einer standardisierten Steuerdatei an. Im folgenden Beispiel werden nur Zahlen nach EXCEL geschrieben. Die Steuerdatei hat dann folgende Variablen:

Das Programm zur Befüllung des/der EXCEL-Sheets hat dann folgenden Aufbau:

/* Macro-Variable mit dem Namen der Eingabe-Datei       */
%Let exceldata = work.exceldaten;

/* DDE-Verbindung                                       */
Filename ddecmds dde "excel|system";

/* EXCEL starten (den Pfad anpassen)                    */ 
Options noxwait noxsync;

X "C:\Programme\Office2000\office\Excel";

Options xwait xsync;

/* Eine Sekunde auf das Starten von EXCEL warten        */ 
%Let rc = %sysfunc(sleep(1));

/* Datastep zum Schreiben der Daten in die Excel-Files  */ 
Data _null_ ;* /DEBUG;
 Length Befehl $300;
 File ddecmds  ;
 Set &exceldata end = Ende;
 By Spreadsheet Workbook notsorted;
 
 if first.Spreadsheet then do;
    Befehl = "[Open("""!!trim(Spreadsheet)!!""")]";
    Put Befehl +(-1);
    Put "[A1.R1C1(FALSE)]";
 end;
 
 if first.Workbook then do;
    Befehl = "[WORKBOOK.Activate("""!!trim(Workbook)!!""")]";
    Put Befehl +(-1);
 end;
 
 Befehl = "[Formula("
           !!quote(trim(value))
           !!",""R"!!left(put(Row,10.))
           !!"C"!!left(put(Col,10.))
           !!""")]";
 Put Befehl +(-1);

 if last.Spreadsheet then do;
    Put "[A1.R1C1(TRUE)]";
    Put "[Close(TRUE)]";
 end;

 if ende then
    Put "[Quit()]";
Run;

Eine Demo-Datei zum Testen könnte z.B. so aufgebaut werden:

Data work.excelDaten; 
  Length Spreadsheet $40
         Workbook    $12
	 Row         8
	 Col         8
	 Value       $40
	 ;

  Spreadsheet = "F:\data\test1.xls";
  Workbook = "Tabelle1";
  Row = 1;
  do Col = 1 to 12;
     Value = put(intnx("Month","1dec2003"d,col),date9.);
     output;
  end;
  do row = 2 to 5;
     do col = 1 to 12;
        value = put(col/row,best16.);
		output;
     end;
  end;

  Spreadsheet = "F:\data\test2.xls";
  Workbook = "Tabelle1";
  do row = 1 to 5;
     do col = 1 to 12;
        value = "R"!!put(row,3.)!!"C"!!put(col,2.);
		output;
     end;
  end;
  Workbook = "Tabelle2";
  Row = 1;
  do Col = 1 to 12;
     Value = put(intnx("Month","1dec2001"d,col),date9.);
     output;
  end;
  do row = 2 to 5;
     do col = 1 to 12;
        value = put(sqrt(col*row),best16.);
		output;
     end;
  end;
Run; 

Dabei wird angenommen, dass die Dateien F:\data\test1.xls und F:\data\test2.xls mit den entsprechenden Arbeitsblättern existieren.

Getestet wurde das Ganze mit SAS Version 8.2, Windows 2000 und EXEL2000 (9.0.2812). Sollte es mit einer anderen Version (SAS, Windows oder EXCEL) nicht funktionieren, bitten wir um eine kurze Mitteilung.