Access (VBA)

Access VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 7 Professional : Access 2010)
エクセルのセルに定義された名前を取得する方法
投稿日時: 19/02/20 00:32:21
投稿者: 河童TKO

いつも大変お世話になっております。
 
エクセルのセルに定義された名前を取得する方法について教えて下さい。
 
エクセルのセルにランダムに名前を付けています。
例えば、A1のセルに「data_1」、A2のセルに「data_2」。
 
アクセスのVBAでエクセルのシートを開いて、
そこからどのようにして、エクセルのセルに定義された名前を
取得するのでしょうか?
 
エクセルシート全体のオブジェクト?のすべてを
繰り返し処理でセルの名前に「data_」が含まれているものを
見つけだして、そのセルのアドレスを知りたいです。
 
エクセルのセルに定義された名前はどのように調べるのでしょうか?
 
よろしくお願いいたします。

回答
投稿日時: 19/02/20 10:01:58
投稿者: sk

引用:
エクセルのセルに定義された名前を取得する方法

過去の記事より:
https://www.moug.net/faq/viewtopic.php?t=77639
 
以前されたのと同じご質問でしょうか。
 
引用:
エクセルシート全体のオブジェクト?のすべてを
繰り返し処理でセルの名前に「data_」が含まれているものを
見つけだして、そのセルのアドレスを知りたいです。

Excel.Name オブジェクトの RefersToRange プロパティが返す
Excel.Range オブジェクトの Address プロパティの値を
取得なさればよろしいかと。

回答
投稿日時: 19/02/20 10:24:53
投稿者: Suzu

Nameオブジェクトの、RefersTo プロパティーでも良いでしょう。

回答
投稿日時: 19/02/20 11:04:07
投稿者: sk

Suzu さんの引用:
Nameオブジェクトの、RefersTo プロパティー

先頭に = が付いててもよければ、ですが。
(アドレスではなく数式なので)

回答
投稿日時: 19/02/20 11:27:53
投稿者: Suzu

sk さんの引用:
Suzu さんの引用:
Nameオブジェクトの、RefersTo プロパティー

先頭に = が付いててもよければ、ですが。
(アドレスではなく数式なので)

 
シート名もないあった方がいいのかな。
シート名を、RefersToRange.Worksheet.Name を取得するのも 。。面倒かな。。。と。
どうせアドレスが必要なら、数式を得てしまえば良いのかなと考えました。

回答
投稿日時: 19/02/20 12:06:22
投稿者: sk

Suzu さんの引用:
シート名もないあった方がいいのかな。

それも場合によるのではないかと。
(外部参照を取得したければ Address プロパティを呼び出す際に
External オプションに True を指定する、という方法もありますし)
 
Suzu さんの引用:
どうせアドレスが必要なら、数式を得てしまえば良いのかなと考えました。

数式を設定するということは、INDEX や INDIRECT などの関数を呼び出したり、
別の名前付きセル範囲をネスト出来たりもする、ということでもあります。
 
https://twitter.com/sk_exe/status/1098049313068871680
 
あとは 河童TKO さんが知りたいのが、名前付きセル範囲の
「定義内容(=数式)」と「実際の参照先のアドレス」の
どちらであるか、何のためにそれを取得したいのか次第でしょう。

投稿日時: 19/02/20 18:46:07
投稿者: 河童TKO

皆さん、お返事ありがとうございます。
 
エクセルファイルにdataというシートがあり、
削除したい行のE列のセルに名前(dataで始まる)を付けています。
 
E列以外にも名前を付けているセルがあります。
 
すべての名前を検索対象として
「data」が含まれる名前の時に
それがE列にあるかどうか判定しています。
 
全てのシートの名前を検索するのではなく、
特定の列の名前だけ検索するにはどのようにすればよいでしょうか?
例えば、
E列の1行目から100行目までの名前を検索
 
よろしくお願いいたします。
 

    With exl_data.Worksheets("data")
            
        'E列のdata番号を取得して行全体を削除
        For Each lName In .Names
            If lName.name Like "*data*" Then 
                            
                ctl_Str = lName.name
                
                If .Range(ctl_Str).Column = 5 Then   'E列
                    '行全体削除
                    .Range(ctl_Str).EntireRow.Delete
                End If                
        Next
      
    End With

回答
投稿日時: 19/02/20 19:47:30
投稿者: Suzu

提示された コード で良さそうですが・・・
何か問題があるのでしょうか?

投稿日時: 19/02/20 20:27:34
投稿者: 河童TKO

問題はないのですが、シート全体を検索するよりか
特定の範囲にある名前を検索した方が良いかなと思いました。
 
名前の付いたセルを指定して行を削除することができません。
 
「data1_53」という名前が付いたセルは
「E129」から「Q138」をセル結合しています。
 
行全体を削除する時に、上記の名前を指定して
削除を実行しているのですが、行が削除されません。
 
エラーは発生していません。
名前が付いたセルが削除されているようです。
 
どのようにすれば結合されたセルで名前を指定して
行全体が削除することができますか?
 
よろしくお願いいたします。
 
 

With exl_data.Worksheets("data")

   'E列のdata番号を取得して行全体を削除
   For Each lName In .Names
      If lName.name Like "*data*" Then

         If .Range(lName.name).Column = 5 Then   'E列

            '名前からシート名(data!)を取り除く
             ctl_Str = Right(lName.name, Len(lName.name) - InStr(lName.name, "!") - Len("!") + 1)

             'data番号がオプションに無い場合は削除
             If fncStarOptionChek(seisan_id, ctl_Str) = False Then

                '行全体削除
                .Range(lName.name).EntireRow.Delete

           End If

         End If

      End If

   Next
End With

回答
投稿日時: 19/02/20 21:13:22
投稿者: hatena
投稿者のウェブサイトに移動

RefersToRangeプロパティがRangeオブジェクトを返すという回答が付いていますので、
それを使えばいいでしょう。
 

With exl_data.Worksheets("data")
    For Each lName In .Names
        If lName.Name Like "*data*" Then
            If Intersect(lName.RefersToRange, Range("E:E")) Then   'E列を含む

                '名前からシート名(data!)を取り除く
                ctl_Str = Right(lName.Name, Len(lName.Name) - InStr(lName.Name, "!") - Len("!") + 1)

                'data番号がオプションに無い場合は削除
                If fncStarOptionChek(seisan_id, ctl_Str) = False Then

                    '行全体削除
                    lName.RefersToRange.EntireRow.Delete
                    lName.Delete '行削除したら参照先がなくなるので名前定義も削除する
                End If
            End If
        End If
    Next
End With

 
 

投稿日時: 19/02/21 09:35:15
投稿者: 河童TKO

お返事ありがとうございます。
 
この部分で「型が一致していません」というエラーが発生します。
>>If Intersect(lName.RefersToRange, Range("E:E")) Then 'E列を含む
 
IntersectでE列が重複しているか判定したいと思います。
「lName.RefersToRange」だとセルの値が入っています。
先頭の「=」を除外すれば「lName.RefersTo」の方がいいのかな。
 
 
行全体削除を試してみたのですが、行全体を削除することができませんでした。
>>lName.RefersToRange.EntireRow.Delete
>>lName.Delete
 
エクセルのマクロで削除の操作を記録してコードを確認したのですが、
EntireRow.Delete
が使用されていました。
マクロでは削除したいセル(結合セル)を選択した状態で
行全体を削除しました。
 
アクセスで行を削除したいセル(名前がついている)を選択して
行全体を削除をためしたのですが、これもできませんでした。
.Range(lName.name).Select
.Range(lName.name).EntireRow.Delete
 
結合されているセル(名前付き)を選択して
行全体を削除するにはどのようにすれば良いでしょうか?
 
よろしくお願いいたします。
[/quote]

回答
投稿日時: 19/02/21 11:36:21
投稿者: hatena
投稿者のウェブサイトに移動

コードが間違ってました。
  
If Intersect(lName.RefersToRange, Range("E:E")) Then 'E列を含む
  
を下記に修正してください。
  
If Not Intersect(lName.RefersToRange, .Range("E:E")) Is Nothing Then 'E列を含む
  
  
Intersectはレンジを返すので、 Is Nothing で判定する必要がありました。
あと、Range の前に . が必要です。
 
また、
当方のサンプルでは、下記のコードで行全体が削除できています。
 
lName.RefersToRange.EntireRow.Delete
lName.Delete
 

回答
投稿日時: 19/02/21 14:26:49
投稿者: sk

河童TKO さんの引用:
エクセルファイルにdataというシートがあり、
削除したい行のE列のセルに名前(dataで始まる)を付けています
  
E列以外にも名前を付けているセルがあります
  
すべての名前を検索対象として
「data」が含まれる名前の時に
それがE列にあるかどうか判定しています

・その名前付きセル範囲の参照範囲は、
 E 列のいずれかの( 1 つの)セルである。
 
・その名前付きセル範囲の参照範囲は、
 E 列のいずれかのセルを基点(左上)とする
 セル領域である。
 
・その名前付きセル範囲の参照範囲は、
 E 列のいずれかのセルが含まれているセル領域である。
 ( E 列のセルがその領域の基点であるとは限らない)。
 
では意味が異なるのですが、どのケースを指しているのでしょうか。
 
河童TKO さんの引用:
「data1_53」という名前が付いたセル
「E129」から「Q138」をセル結合しています。

また上記の表現では、以下のどれに該当しているのかが不明瞭です。
 
・名前付きセル範囲 data1_53 の参照範囲として、
 「=data!$E$129:$Q$138」という数式が設定されている。
 (その上で、そのセル領域全体を結合している)
 
・名前付きセル範囲 data1_53 の参照範囲として、
 「=data!$E$129」のように E 列のいずれかのセルを
 参照する数式が設定されている。
 (その上で、セル領域 data!E129:Q138 を結合している)
 
・名前付きセル範囲 data1_53 の参照範囲は
 セル領域 data!F129:Q138 のいずれかのセル範囲
 ( E 列のどのセルも含まれていない)である。
 (その上でセル領域 data!E129:Q138 を結合している)
 
例えば「 data1_53 の参照範囲を F130 セルとする」という設定と
「 E129 から Q138 までの( F130 セルを含む)セル領域を結合する」
という設定は両立します。
 
仮にその状況で「 data1_53(=date!$F$130)は E 列にあるかどうか」を
判定した場合、その結果は False です。
 
また、「 data1_53 の参照範囲を E132 セルとする」という設定と
C129 から F138 までの( E132 セルを含む)セル領域を結合する」
という設定が行われた状態で「 data1_53 は E 列にあるかどうか」を
判定したのであれば、その結果は True となります。
 
河童TKO さんの引用:
If .Range(ctl_Str).Column = 5 Then 'E列

hatena さんの引用:
If Not Intersect(lName.RefersToRange, Range("E:E")) Is Nothing Then 'E列を含む

また、この 2 つの条件は同一の結果をもたらすものではありません。
 
例えば、その名前付きセル範囲の参照先が D1:E1 である場合、
前者の結果は False となりますが、後者の結果は True となります。
 
・2 列以上に渡るセル領域を参照する Range オブジェクトの
 Column プロパティによって返されるのは、その領域の
 基点である左上のセルの列番号である。
 (つまり、この場合の Column プロパティの値は 4)
 
・Intersect メソッドは「複数のセル範囲の共有セル範囲」への
 参照を返す命令である。
 E 列のいずれかのセルが領域の基点であるかどうかまでは評価していない
 
そして、これらのことと、セル結合の設定とは一切関係がありません。
 
河童TKO さんの引用:
行全体を削除する時に、上記の名前を指定して
削除を実行しているのですが、行が削除されません。

河童TKO さんの引用:
結合されているセル(名前付き)を選択して
行全体を削除するにはどのようにすれば良いでしょうか?

その名前付きセル範囲が含まれる行を削除する」のと
『その名前付きセル範囲が含まれている結合セル』を含む行を削除する」
のとは別の操作です。
 
引用:
・名前付きセル範囲 data1_53 の参照範囲として、
 「=data!$E$129」のように E 列のいずれかのセルを
 参照する数式が設定されている。
 (その上で、セル領域 data!E129:Q138 を結合している)

上記のケースに置き換えるなら、
前者は 129 行目だけを削除する操作、
後者は 129 行目から 138 行目までの 10 行を削除する操作である、
ということになります。
 
しかし、そのどちらを実行したいのかが、ここまでのご説明では
明確にされていません。
 
以上の問題点を踏まえられた上で、最終的にどのような行を削除したいのか、
その具体的な条件について整理されることをお奨めします。

投稿日時: 19/02/21 16:07:56
投稿者: 河童TKO

 skさん、ありがとうございます。少しわかってきました。
 
セルの結合は、
必ずE列から始まりQ列まで結合します。
基点はE列です。
 
行全体が削除できないのは、名前の参照範囲が
=denki!$E$129
になっていました。
 
10行分のセルが結合されているとき
名前が付いていた1行分だけ削除されていました。
 
試しに参照範囲を
=denki!$E$129:$Q$138
に設定すると結合されたセル分で行削除ができました。
 
この対応策として、
その1.セルに名前付けるときに手作業で参照範囲を変えていく。
その2.セルが結合されている行数分、削除を繰り返し処理を追加する。
 
できたら、
結合されたセルの行数分で削除の繰り返し処理を作成してみたいです。
 

回答
投稿日時: 19/02/21 16:32:33
投稿者: hatena
投稿者のウェブサイトに移動

名前定義の範囲に、結合セルがあるかどうかしらべて、結合セルなら、
結合セルの範囲の行全体を削除すればいいでしょう。
 
MergeCellsプロパティで結合セルかどうが判定できます。
MergeAreaプロパティで結合セルの範囲を取得できます。
 
Office TANAKA - Excel VBA Tips[結合セルを調べる]
http://officetanaka.net/excel/vba/tips/tips50.htm

回答
投稿日時: 19/02/21 18:14:02
投稿者: sk

引用:
セルの結合は、
必ずE列から始まりQ列まで結合します。
基点はE列です。

引用:
行全体が削除できないのは、名前の参照範囲が
=denki!$E$129
になっていました。

data1_53( E129 セル)の値を別の数式セルから参照したり、
data1_53 の値をコードで書き換えるようなことをされているのであれば、
この定義内容自体はむしろ妥当なのではないかと思います。
 
引用:
試しに参照範囲を
=denki!$E$129:$Q$138
に設定する

引用:
その1.セルに名前付けるときに手作業で参照範囲を変えていく

名前付きセル範囲の定義内容にそうした変更を加えることによって
他の計算や入出力処理に支障が出ないかどうかについては
念のため検討された方がよいでしょう。
(というか、元々はそちらが名前定義の主目的ではないかと思うのですが)
 
例えば、「=ROWS(data1_53)」という数式が
設定された数式セルが存在しているとして、
この時の data1_53 の参照範囲が E129 セルだけなのか、
「 E129 を含む 2 行以上に渡るセル領域」なのかによって、
ROWS 関数の戻り値は異なりますので。
 
引用:
その2.セルが結合されている行数分、削除を繰り返し処理を追加する。

引用:
できたら、
結合されたセルの行数分で削除の繰り返し処理を作成してみたいです。

これに関しては hatena さんが回答されているように、
ReferToRange プロパティから取得した Range オブジェクトの
MergeCells プロパティと MergeArea プロパティを使用して
結合セルへの参照を取得し、その結合セルを含む行を
まるごと削除するという方法が考えられます。
(ループは不要)

投稿日時: 19/02/21 18:51:14
投稿者: 河童TKO

削除の処理の前に教えて頂きたいことがあります。
 
別に投稿をした方が良いかもしれませんが、
このまま続きでさせてください。
 
エクセルには名前を付けているセルがあるのですが、
For Each lName In .Names
Next
で名前が取得できずに削除の処理を通過しません。
 
.Namesで取得されるエクセル側の名前は登録できているのですが、
処理を通過しません。
 
.Namesの中身や確認方法を教えて頂けないでしょうか?
 
 

    Set exl_data = exl.Workbooks.Open(CurrentProject.path & "\DATA\記録表\" & strFileName)
            
    With exl_data.Worksheets("data")

	'エクセルブックごとの処理は省略    
    
        'E列の番号を取得して行全体を削除
        For Each lName In .Names
           '削除の処理
        Next

    End With
    
    'Excel解放
    exl_denki.Close: Set exl_denki = Nothing

投稿日時: 19/02/21 20:21:31
投稿者: 河童TKO

名前の範囲がブックになっていました。
シートを一度コピーして、そのシート名を「data」に
変更すると名前を読込むようになりました。
 
 
 

回答
投稿日時: 19/02/22 15:15:06
投稿者: sk

引用:
エクセルには名前を付けているセルがあるのですが、
For Each lName In .Names
Next
で名前が取得できずに削除の処理を通過しません。

引用:
With exl_data.Worksheets("data")

引用:
名前の範囲がブックになっていました。

Workbook オブジェクトの Names プロパティではなく
Worksheet オブジェクトの Names プロパティを
参照しているのですから、それはそうでしょう。

トピックに返信