Fri, May 06, 2005 11:20:21

XMLとExcel PartⅡ

テーマ:プチOA

昨日の続き。今度はこれに対するVBAの書き方。

Excelのブックはtest.xlsと命名している。


Excelの「ツール」→「マクロ」→「Visual Basic Editor」でエディタを

立ち上げて新規に標準モジュールを作成する。

そこに以下のスクリプトを記述する。


ExcelからXMLを読む

【VBA】

昨日も書いたように半分はVB自動生成機能で書いたものなので

決して褒められたソースではないが、まあとりあえず。
原理は上の図の通り。


Sub Main_Routine()

' 変数宣言
Dim sht(3)
Dim subject(3)
Dim XmlFile(3)
Dim i

sht(1) = "sheet1"
sht(2) = "sheet2"
sht(3) = "sheet3"

subject(1) = "数学"
subject(2) = "国語"
subject(3) = "英語"

XmlFile(1) = "math.xml"
XmlFile(2) = "japanese.xml"
XmlFile(3) = "english.xml"

basefilepath = Worksheets("Guide").Range("b4")

    For i = 1 To 3
        XmlFileName = basefilepath & XmlFile(i)
       
        ' シートを新規に作成
        Call CreateNewSheet(subject(i), sht(i))
       
        ' XMLファイルを開く
        Call UseOpenXML(XmlFileName)
       
        ' 順位データを取得する
        Call get_ranking(XmlFile(i))
       
        ' XMLファイルを閉じる
        Workbooks(XmlFile(i)).Close SaveChanges:=False
    Next
       
End Sub


Sub UseOpenXML(XmlFileName)

'   XMLファイル読み込み
    Application.Workbooks.OpenXML FileName:=XmlFileName

End Sub

Sub get_ranking(XmlFile)
    Windows(XmlFile).Activate
    Range("F3:F5,M3:M5,H3:H5").Select
    Selection.Copy
    Windows("test.xls").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub
Sub CreateNewSheet(subject, sht)

    Sheets.Add Type:=xlWorksheet
    Sheets(sht).Select
    Sheets(sht).Name = subject
    ActiveCell.FormulaR1C1 = "順位"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "点数"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "氏名"
    Range("A1:C1").Select
    Range("C1").Activate
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
    Range("B1").Select
    Columns("B:B").ColumnWidth = 20
    Columns("C:C").ColumnWidth = 20
    Range("A2").Select
End Sub

Sub closethisfile()
    Workbooks("test.xls").Close SaveChanges:=False
End Sub

最近の画像つき記事
 もっと見る >>
Thu, May 05, 2005 19:05:00

XMLとExcel PartⅠ

テーマ:プチOA

XMLのサンプルを作ってEXCELで2次加工するプログラムを

作ってみました。とはいっても半分はEXCELのVBA自動生成機能

を使ったんだけどね。


【お題】

数学、国語、英語の教科別順位一覧のXMLを読み込んで

各々新規シートに上位3名の名前と点数を表示する。


【Excel画面イメージ】

作成ボタンをVBAのmain_routineに結び付けてます。

セルのB4にXMLファイルへのパスを入力。

このシートを「guide」という名前にしています。


 guideというシート名で作ってみました


【作成ボタン押下後の結果】


 数学の得点ランキングの例

【XMLサンプル】

これは”数学”でmath.xmlとしてます。同じように”国語”はjapanese.xml

”英語”はenglish.xmlとして微妙に順位を変えてみました。

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE root [
<!ENTITY lt "&#38;#60;">
<!ENTITY gt "&#62;">
<!ENTITY amp "&#38;#38;">
<!ENTITY apos "&#39;">
<!ENTITY quot "&#34;">
<!ENTITY nbsp "&#160;">
]>

<root>
 
 <data count="555">
  <rank id="1">
   <Number value="1" name="順位" prev="0" />
   <Student_id value="00001" />
   <Student_name value="Taro" />
   <Point value="80" />
   <Point_updown value="up" />
  </rank>
  <rank id="2">
   <Number value="2" name="順位" prev="0" />
   <Student_id value="00004" />
   <Student_name value="JiTaro" />
   <Point value="78" />
   <Point_updown value="up" />
  </rank>
  <rank id="3">
   <Number value="3" name="順位" prev="0" />
   <Student_id value="00011" />
   <Student_name value="SanTaro" />
   <Point value="77" />
   <Point_updown value="down" />
  </rank>
  <rank id="4">
   <Number value="4" name="順位" prev="0" />
   <Student_id value="00101" />
   <Student_name value="YonTaro" />
   <Point value="76" />
   <Point_updown value="up" />
  </rank>
  <rank id="5">
   <Number value="5" name="順位" prev="0" />
   <Student_id value="00201" />
   <Student_name value="GoTaro" />
   <Point value="74" />
   <Point_updown value="stay" />
  </rank>
 </data>
</root>


(続く)