＜EXCEL＞
当サイトの情報をご利用になる場合は、全て自己責任でお願いします |
| 第14号 2005/11/04 |
＜Excelマクロ(VBA)編＞
PHPで作成したデータを取り込むExcelのマクロを紹介させて頂きます。
サンプルのExcelファイルはこちら
(http://www.sing-ken-ken.com/data_shori/s02.htm より)
マクロ(VBA)の説明
Sub データ取込()
'処理を行うかどうかの確認のダイアログを出して、行わない場合はマクロを終了します。
If MsgBox("データの取り込みを実行しても良いですか？", vbYesNo) = vbNo Then
Exit Sub
End If
'PHPに与えるデータ(セルB4)を変数CHIKUに代入します
CHIKU = Range("B4").Value
'今回のPHPファイルでは、CHIKUにデータが無い場合はすべてのデータを対象にするようになっているので、新しい変数JでPHPへのデータの与え方を変えています
J = ""
If CHIKU <> "" Then
J = "?c=" & CHIKU
End If
'取り込んだデータを表示するブックを、Sheetデータをコピーして作ります
Sheets("データ").Copy
'新しいブックの名前を変数AWBNに代入します
AWBN = ActiveWorkbook.Name
'作業用のブックを作ります
Workbooks.Add
'作業用のブックにデータ(PHPファイル)を取り込みます。このとき変数Jの値を与えています
'赤文字の部分が取り込むデータの型の設定です。A列から順番にカンマで区切っていきます。(1=数字、2=文字、5=日付)
With ActiveSheet.QueryTables.Add(Connection:="TEXT;http://www.sing-ken-ken.com/data_shori/uriage_t.php/" & J, _
Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierNone
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(5, 1)
.Refresh BackgroundQuery:=False
End With
'作業用のブックの名前を変数TWBNに代入します
TWBN = ActiveWorkbook.Name
'データのA列の最後の行番号を変数ENDROWに取り込みます(セルA65536を選択してからCtrl+↑をするとデータの一番最後の行に移動しますが、その時の行番号を取り出しています。)
ENDROW = Range("A65536").End(xlUp).Row
'データが無かった時のための処理です。データが無かった場合は警告を表示して、作成したブックを閉じます。
If ENDROW = 1 And Range("A1").Value = "" Then
MsgBox "データがありませんでした。", vbOKOnly
Application.DisplayAlerts = False
Workbooks(AWBN).Close (False)
Workbooks(TWBN).Close (False)
Application.DisplayAlerts = True
Exit Sub
End If
'データをコピーします。
Range("A1:B" & ENDROW).Copy
'コピーしたデータを表示用のブックに貼り付けます。
Workbooks(AWBN).Activate
Range("B6").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'ここからはデータの見栄えを良くするための処理です。
'データの最後の行番号を調べます。(コピーするときにデータ数がわかっているので計算でも出せます)
ENDROW = Range("B65536").End(xlUp).Row
'６行目の書式を全データ行に反映させるためにコピーして書式の貼り付けを行います。
Rows("6:6").Copy
Rows("6:" & ENDROW).Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'一番最後の行の下線を太線にします。
Range("B1:C" & ENDROW).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
'セルC4に合計を求める計算式を代入します。
Range("C4").FormulaR1C1 = "=SUM(R6C:R" & ENDROW & "C)"
'変数CHIKUが空欄の時は“すべて”のデータを表しますので、CHIKUに“すべて”を代入します。これは、表に条件を表示するときに使用します。
If CHIKU = "" Then
CHIKU = "すべて"
End If
'印刷範囲を設定します。
ActiveSheet.PageSetup.PrintArea = "$B$1:$C$" & ENDROW
'C列の幅を適正な幅にします
Columns("C:C").EntireColumn.AutoFit
'各セルに表題や条件を表示します
Range("B1").Value = "売上実績"
Range("B2").Value = "地区:" & CHIKU
Range("B3").FormulaR1C1 = "=""年月日：〜""&TEXT(R" & ENDROW & "C,""yyyy/mm/dd"")"
Range("A1").Select
'作業用のブックを閉じます。Workbooks(TWBN).Close (False)だけでも良いのですが、念のため、事前に警告が出ないようにしています。
Application.DisplayAlerts = False
Workbooks(TWBN).Close (False)
Application.DisplayAlerts = True
End Sub
(プログラム終わり)
以上が、PHPで作成したデータを取り込むExcelのマクロです。
Excelには、実際に行った作業をマクロとして記録する機能が有りますので(ツール→マクロ→新しいマクロの記録)、基本的にはこの機能を使用して作られたマクロを修正して作ります。ただし、データ(PHPファイル)を取り込む部分は、Excel2000では外部データの取り込みでマクロが作成できたのですが、Excel2003で試したところ、うまく作成できませんでした。この部分は上記をコピーした方が良いかもしれません。
また、クロス集計をしたデータの場合は、少し工夫が必要になります。データの取込部分の型定義(赤文字部分)は一番短い場合で作成しておき、取り込んだデータの最後の列番号を下記で調べます。
ENDCOL = Range("IV1").End(xlToLeft).Column
その後、下記で行番号を行のアルファベットへ読み替えると上記と同じように処理できます。
If ENDCOL > 26 Then
ENDCOLA = Chr(64 + Int((ENDCOL - 1) / 26)) & Chr(64 + (ENDCOL - Int((ENDCOL - 1) / 26) * 26))
Else
ENDCOLA = Chr(64 + ENDCOL)
End If
************************************************************************
＜編集後記＞
今回は、Excelでのデータ(PHPファイル)の取り込み方を説明させていただきま
した。これは、事前にPHPファイルを作成しておき、各クライアントに自由にデ
ータを取り込んでもらうことを前提にしていますので、作成するのに少し手間が
かかります。（その代わりクライアントで好きにデータを取り込んでもらえます
ので一度作成すれば、後は何もしなくて良いのですが）
１回限りのデータ集計やデータの内容確認等の場合には、もう少し簡単な方法
を使っています。
次回は、batファイルでPHPファイルを実行してデータをTEXTファイルで出力す
る方法を紹介させて頂きます。
************************************************************************
|
|