EXCELセルへのデータの貼付け

質問

こんばんわ、下で質問していたのですが、内容を変えてもう一度
お伺い致します。

DBのデータを獲得し、セルに貼りつけます、
作るのは日報の様なものです（0～23時の時間毎のデータ）
現在はセルの位置を行列番号で指定して貼りつけています。
追加があったり、列の位置がかわった際など、改造が面倒です。
EXCELの列名、行名が使えればスマートにいくかなと思い質問させて
もらったのですが、無理な様です。

貼付け開始位置セルに名前をつけて
そこから行列番号を獲得し、貼り付けることができないかなと思うのですが、
こういった処理はどの様にするのがベターなのでしょうか？

回答No.2

noname#144013

こんにちは。　#1です。

> やっぱりセル名や列名、行名は検索できないのでしょうか？

以下のように、
　　Cells(3月,東京).Value=25.3
Range(x)や、Cells(x.x)の引数に直接、「名前」を指定することはできませんが、
「名前定義」により、セルに名前を割り振ることはできますので、それを利用して
割り振った名前からセルをアクセスすることは可能だと思います。

例えば、一例として、

１）予め、A列の各セル（行方向）と、1行目の各セル（列方向）に「名前」を定義
　　しておく。
　　　A列の各セル　　⇒　「行名」の名前を定義
　　　1行目の各セル　⇒　「列名」の名前を定義
２）１）で付けた「行名」と「列名」より、対応するセル位置にデータを設定する関数
　　を作成しておく。
３）メインの処理から、２）の関数を呼んで、「行名」と「列名」に対応したセルに
　　データをセットする。

以上のような処理を行えば、擬似的に、「行名」＆「列名」によるセルのアクセスが
可能だと思います。

注意点として、セルに付ける「名前」について、
　　・スペースを含んだ名前（文字列）は使用できない。
　　・セル参照と同じ形式の名前（文字列）は使用できない。
　　・先頭文字が半角or全角の「数字」の名前（文字列）は使用できない。
というような仕様になっているようです。
※↑はExcel2000で確認したものですが、他のバージョンのExcelでも同仕様か
　どうかは判りません。

以下に、上記の処理を使用したサンプルマクロを記載致します。

下記マクロは、ワークシートの構成が下記の場合を想定しています。

＜ワークシート構成　：　シート名＝"Sheet1"＞
　　　　　A　　　　B　　　　　C　　　　　D　　　　　E　　
　　1　　月　　　東京　　名古屋　　 大阪　　　福岡　
　　2　　1月　　XX.X　　　XX.X　　　XX.X　　　XX.X　
　　3　　2月　　XX.X　　　XX.X　　　XX.X　　　XX.X　
　　4　　3月　　XX.X　　　XX.X　　　XX.X　　　XX.X　
　　：　　　：　　　　：　　　　　：　　　　　：　　　　　：　
　　：　　　：　　　　：　　　　　：　　　　　：　　　　　：　
　13　　12月　 XX.X　　　XX.X　　　XX.X　　　XX.X　

　　・1行目　　列の項目名（文字列）
　　　　　　　　　※B列以降は「都市名」
　　　　　　　　　※この行の各セル（2列目以降）に対して、名前（列名）を定義します。
　　　　　　　　　※定義する名前は、セルの値（＝都市名）と同じとします。
　　・A列　　　月名（文字列）
　　　　　　　　　※この列の各セル（2行目以降）に対して、名前（行名）を定義します。
　　　　　　　　　※定義する名前は、「"行"＋番号」の形式とします。
　　　　　　　　　注）この場合、セルの値（＝月名）の先頭文字が「数字」ですので、
　　　　　　　　　　　この値自体（＝月名）を「名前」として定義できません。
　　・B列～　　都市毎のデータ

■サンプルマクロ
注１）標準モジュール用のマクロです。
注２）インデント等のため全角スペースを入れています。

＜各関数の概要＞
　Sub SetCellName()　 ・・・　行名、列名の名前定義を行う関数
　Sub SetCellData()　　・・・　行名、列名によりデータ設定を行う関数
　Sub SetDataMain()　 ・・・　メイン処理

/////↓ここから//////////
'== 行名、列名の登録 ==
Sub SetCellName()

　　Dim bk As Workbook　　 'Workbookオブジェクト
　　Dim sh As Worksheet　　'Worksheetオブジェクト
　　Dim rg As Range　　　　'Rangeオブジェクト
　　Dim cnt As Long　　　　'カウンタ変数
　　Dim nRowMax As Long　　'行数
　　Dim nColMax As Long　　'列数
　　Dim sRowName As String '行名
　　Dim sColName As String '列名

　　'Workbook、Worksheetのオブジェクト取得
　　Set bk = ThisWorkbook
　　Set sh = bk.Worksheets("Sheet1")

　　'「A列のセル」と「1行目のセル」に設定されているデータ数を取得
　　'※データ（＝項目名など）が設定されているかチェックする
　　nRowMax = sh.Range("A" & sh.Rows.Count).End(xlUp).Row
　　nColMax = sh.Range(Cells(1, sh.Columns.Count), _
　　　　　　　　Cells(1, sh.Columns.Count)).End(xlToLeft).Column
　　If nRowMax < 2 Or nColMax < 2 Then
　　　　'データが設定されていなければ戻る
　　　　Set sh = Nothing
　　　　Set bk = Nothing
　　　　Exit Sub
　　End If

　　'エラーの回避
　　On Error Resume Next

　　'A列のセル（2行目以降）に「行名」を登録
　　'※このサンプルでは、行名は「"行"＋番号」としています。
　　cnt = 0
　　For Each rg In sh.Range("A2:A" & nRowMax)
　　　　cnt = cnt + 1
　　　　sRowName = "行" & cnt　'行名："行"＋番号
　　　　bk.Names.Add Name:=sRowName, _
　　　　　　RefersTo:="=" & sh.Name & "!" & rg.Address
　　Next rg

　　'1行目のセル（2列目以降）に「列名」を登録
　　'※このサンプルでは、列名はセルの値（文字列）としています。
　　cnt = 0
　　For Each rg In sh.Range(Cells(1, 2), Cells(1, nColMax))
　　　　'セルに文字列が設定されている時のみ名前定義を行う
　　　　If Len(Trim(rg.Text)) > 0 Then
　　　　　　cnt = cnt + 1
　　　　　　sColName = rg.Text　'列名：セルの値（文字列）
　　　　　　bk.Names.Add Name:=sColName, _
　　　　　　　　RefersTo:="=" & sh.Name & "!" & rg.Address
　　　　End If
　　Next rg

　　'エラー処理解除
　　On Error GoTo 0

　　'各オブジェクトの解放
　　Set rg = Nothing
　　Set sh = Nothing
　　Set bk = Nothing

End Sub
'
'== 行名、列名によるデータ設定 ==
'【引数】
'　ByVal sRowName As String ：行名（文字列）
'　ByVal sColName As String ：列名（文字列）
'　ByVal vData As Variant　 ：設定するデータ
'
Sub SetCellData(ByVal sRowName As String, _
　　　　　　　　ByVal sColName As String, _
　　　　　　　　ByVal vData As Variant)

　　Dim bk As Workbook　　 'Workbookオブジェクト
　　Dim sh As Worksheet　　'Worksheetオブジェクト
　　Dim nErr As Integer　　'エラー情報
　　Dim nRow As Long　　　 '行位置
　　Dim nCol As Long　　　 '列位置

　　'Workbook、Worksheetのオブジェクト取得
　　Set bk = ThisWorkbook
　　Set sh = bk.Worksheets("Sheet1")

　　'行名、列名から対応するセルを取得しデータセット
　　nErr = 0
　　On Error GoTo L_ERR1
　　nRow = bk.Names(sRowName).RefersToRange.Row
　　nCol = bk.Names(sColName).RefersToRange.Column
　　On Error GoTo 0
　　If nErr = 0 Then
　　　　'正常時（対応セルがあったとき）はデータセット
　　　　sh.Cells(nRow, nCol).Value = vData
　　End If

　　'各オブジェクトの解放
　　Set sh = Nothing
　　Set bk = Nothing
　　Exit Sub

'==エラー処理==
'※行名および列名が定義されていなかった場合のエラー処理
L_ERR1:
　　nErr = 1
　　Resume Next

End Sub
'
'== 行名、列名によるデータ設定（メイン） ==
Sub SetDataMain()

　　'行名、列名の登録
　　Call SetCellName

　　'行名、列名によるデータ設定（1行目）
　　Call SetCellData("行1", "東京", 10.1)
　　Call SetCellData("行1", "名古屋", 10.2)
　　Call SetCellData("行1", "大阪", 10.3)
　　Call SetCellData("行1", "福岡", 10.4)

　　'行名、列名によるデータ設定（2行目）
　　Call SetCellData("行2", "東京", 20.1)
　　Call SetCellData("行2", "名古屋", 20.2)
　　Call SetCellData("行2", "大阪", 20.3)
　　Call SetCellData("行2", "福岡", 20.4)

End Sub
/////↑ここまで//////////

添付画像は、上記サンプル実行後のExcelシート画面をキャプチャしたものです。

注）上記マクロは、Excelファイル内部のVBAによる処理ですので、外部アプリ
　　（VB、VCなど）からExcelファイルを操作する場合に応用できるかどうかは
　　判りません。

以上です。参考になれば幸いです。

お礼

お付き合い頂き、ありがとうございます。
現在試せる環境でないため、確認できていませんが、
早速、試させて頂きます。

なんでセル名でアクセスできないんでしょうね～
DB列名を変数で持ってセルにアクセスできれば、
コード上では貼付け位置を意識せずにすむのに・・・

回答No.1

noname#144013

こんにちは。

ご希望のものとは違うかもしれませんが。。。
（的外れでしたら、すみません。）

前回の下記ご質問、
・EXCELでセル名でのアクセス
http://okwave.jp/qa/q5596003.html
に回答しようと用意していたところ投稿前に締め切られてしまいましたので、
こちらに投稿させて頂きます。

前回は、ご提示された表の構成が、行方向が「月単位」の表のようでしたが、
今回は、「時刻単位」の表構成のようですので、作成していたサンプルマクロ
も対応するものに修正してみました。

例として、ワークシートの表構成が下記のような場合だったとします。

　　　　　　A　　　　　　B　　　　　　C　　　　　D　　　　　E　　　　　F　　
　　1　　月日　　　　時分　　　　東京　　名古屋　　　大阪　　　福岡　
　　2　　1月1日　　 0時00分　　XX.X　　　XX.X　　　XX.X　　　XX.X　
　　3　　1月1日　　 1時00分　　XX.X　　　XX.X　　　XX.X　　　XX.X　
　　4　　1月1日　　 2時00分　　XX.X　　　XX.X　　　XX.X　　　XX.X　
　　5　　1月1日　　 3時00分　　XX.X　　　XX.X　　　XX.X　　　XX.X　
　　：　　　　：　　　　　　：　　　　　　：　　　　　：　　　　　：　　　　　：　　
　　：　　　　：　　　　　　：　　　　　　：　　　　　：　　　　　：　　　　　：　　
　25　　1月1日　　23時00分　　XX.X　　　XX.X　　　XX.X　　　XX.X　

　　・1行目　　列の項目名
　　　　　　　　　※C列以降は「都市名」
　　・A列　　　月日データ
　　　　　　　　　※データ形式は「文字列」ではなく、「日付シリアル値」と仮定します。
　　・B列　　　時分データ
　　　　　　　　　※データ形式は「文字列」ではなく、「時刻シリアル値」と仮定します。
　　・C列～　 都市毎のデータ

　　※データは時系列（下方向に月日及び時刻が進んでいく）に並んでいるもの
　　　とします。

上記の構成で、「月日」、「時分」及び「都市名」をキーにして、対応するセル位置
を検索し、そのセルにデータをセットするマクロのサンプルを掲載いたします。

■サンプルマクロ
注１）標準モジュール用のマクロです。
注２）インデント等のため全角スペースを入れています。

/////↓ここから//////////
'== 行列項目によるセル操作のテスト ==
Sub SetCell()

　　Dim nRow As Long　　 '行位置
　　Dim nCol As Long　　 '列位置
　　Dim rgDate As Range　 '月日のセル範囲取得用
　　Dim rgTime As Range　 '時分のセル範囲取得用
　　Dim rgCity As Range　 '都市名のセル範囲取得用
　　Dim rg1 As Range　　 'セル取得用(1)
　　Dim rg2 As Range　　 'セル取得用(2)
　　Dim rg3 As Range　　 'セル取得用(3)
　　Dim vDate As Date　　'検索月日
　　Dim vTime As Date　　'検索時分
　　Dim sCity As String　'検索都市名

　　'操作対象シートの指定
　　With Worksheets("Sheet1")
　　　　'検索キーのセット
　　　　vDate = DateValue("1月1日")　 '検索月日
　　　　vTime = TimeValue("3時00分")　'検索時分
　　　　sCity = "東京"　　　　　　　　'検索都市名

　　　　'月日かつ時分が一致するセルの検索及び行位置の取得
　　　　Set rgDate = .Range("A:A")
　　　　Set rg1 = rgDate.Find(vDate, _
　　　　　　　　　　LookIn:=xlFormulas, LookAt:=xlWhole)
　　　　nRow = 0
　　　　If Not rg1 Is Nothing Then
　　　　　　Set rgTime = .Range("B" & rg1.Row & ":B" & .Rows.Count)
　　　　　　Set rg2 = rgTime.Find(vTime, _
　　　　　　　　　　　　LookIn:=xlFormulas, LookAt:=xlWhole)
　　　　　　If Not rg2 Is Nothing Then
　　　　　　　　If rg2.Offset(0, -1).Value = vDate Then nRow = rg2.Row
　　　　　　End If
　　　　End If

　　　　'都市名が一致するセルの検索及び列位置の取得
　　　　Set rgCity = .Range("1:1")
　　　　Set rg3 = rgCity.Find(sCity, _
　　　　　　　　　　LookIn:=xlValues, LookAt:=xlWhole)
　　　　nCol = 0
　　　　If Not rg3 Is Nothing Then nCol = rg3.Column

　　　　'対応セルにデータセット
　　　　If nRow >= 1 And nCol >= 1 Then
　　　　　　.Cells(nRow, nCol).Value = 25.3
　　　　End If
　　End With

　　'Rangeオブジェクトを解放
　　Set rgDate = Nothing
　　Set rgTime = Nothing
　　Set rgCity = Nothing
　　Set rg1 = Nothing
　　Set rg2 = Nothing
　　Set rg3 = Nothing

End Sub
/////↑ここまで//////////

添付画像は、上記サンプル実行後のExcelシート画面をキャプチャしたものです。

以上です。参考になれば幸いです。

お礼

ご回答ありがとうございます。
セルの値を('東京')検索して、貼付け位置を獲得するということですね、参考になります。
やっぱりセル名や列名、行名は検索できないのでしょうか？