ここから本文です

Access VBA で動的配列について教えてください。 Access2016を用いてExcelブック...

ven********さん

2017/8/807:52:32

Access VBA で動的配列について教えてください。
Access2016を用いてExcelブックのシート名を取得したいと考えています。
対象となるExcelブックが変更されるため、シート数は固定されていな

い状態です。

テスト的に「商品テーブル」というExcelブックを作成し、「野菜」「果物」「加工品」の
3つのシートを作りました。

VBAは以下のように作りました。

Dim DB As DAO.Database
Dim TBD As DAO.TableDef
Dim wsPass As String
Dim wsName As String
ReDim SheetName(0) As String
Dim I As Integer
Dim J As Integer
Dim K As Integer

wsPass = "D:\商品テーブル.xlsx"
Set DB = OpenDatabase(wsPass, False, False, "excel 12.0;HDR=NO;")

For I = 0 To DB.TableDefs.Count - 1
wsName = DB.TableDefs(I).Name
If Right(wsName, 1) = "$" Then
J = UBound(SheetName) + 1
ReDim SheetName(J)
SheetName(J) = wsName

Debug.Print SheetName(J) ←(A)

End If
Next I

For K = 0 To UBound(SheetName)
Debug.Print K & "番目 :" & SheetName(K) ←(B)
Next K

DB.Close

これを実行すると、イミディエイトウインドウに以下のように表示されます
野菜$ ←(A)
果物$ ←(A)
加工品$ ←(A)
0番目 : ←(B)
1番目 : ←(B)
2番目 : ←(B)
3番目 :加工品$ ←(B)

(A)のDebug.Print ではシート名が格納されているようなのですが、
(B)では最後の「加工品」のみで、1番目と2番目は何も入っていません。

質問1 3つのシート名を格納するにはどうしたら良いのでしょうか?

質問2 シート名を取得しようとすると定義した名前も取得されてしますため、
末尾が"$"かどうかでシート名を判定していますが、シート名のみを
取得する方法があるのでしょうか?

同時に2つの質問で申し訳ありませんが、よろしくお願いいたします。

閲覧数:
128
回答数:
2
お礼:
100枚

違反報告

ベストアンサーに選ばれた回答

プロフィール画像

カテゴリマスター

nor********さん

2017/8/812:51:21

私も onyourside555さん
と同じやり方を取ります。
ただ、なぜうまく行かないのかが分からないとモヤモヤが残ると思うのでその点だけ。

それはRedimする時にPreserveを指定していないのて、配列がクリアされるからです。
下記のようにして試してください。
ただ繰り返しですが、ワークシート名を取得するならExcel.Application使ったほうがいいように思いますけど。

Dim DB As DAO.Database
Dim TBD As DAO.TableDef
Dim wsPass As String
Dim wsName As String
ReDim SheetName(0) As String
Dim I As Integer
Dim J As Integer
Dim K As Integer

wsPass = "D:\商品テーブル.xlsx"
Set DB = OpenDatabase(wsPass, False, False, "excel 12.0;HDR=NO;")

For I = 0 To DB.TableDefs.Count - 1
wsName = DB.TableDefs(I).Name
If Right(wsName, 1) = "$" Then
J = UBound(SheetName) + 1
ReDim Preserve SheetName(J) '修正
SheetName(J) = wsName

Debug.Print J, SheetName(J) '←(A) 修正

End If
Next I

For K = 0 To UBound(SheetName)
Debug.Print K & "番目 :" & SheetName(K) '←(B)
Next K

DB.Close

  • 質問者

    ven********さん

    2017/8/816:04:30

    nora1962jp様
    回答ありがとうございました!

    Preserveという引数を知らないでいました。
    今回質問でアップしたコードでは、(A)の処で全てのシート名が格納されているように見えて、実際はクリアされた配列の最後の部分のみが表示されていたのですね。

    ご指摘のように修正したら、全てのシート名が配列変数に格納されていました。

    nora1962jp様も仰るように、シート名を取得はonyourside555様の方法で行うことにしました。
    試してみたら xlBook.worksheets.Count でシート数が分かる様でしたので、ReDim もその都度ではなく、最初にシート数で拡張しておけば良いので、コードもスッキリしました。

    今回の質問主旨から、ベストアンサーに選ばさせていただきました。
    ありがとうございました!


返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

ベストアンサー以外の回答

1〜1件/1件中

プロフィール画像

カテゴリマスター

ony********さん

2017/8/809:14:18

質問されていることに答えなくて申し訳ないのですが、
私はEXCELのバージョンに左右されたくないので、
こんな風にシート名を取得しています。
(参照設定不要+接続文字列未使用⇒バージョン指定なし)

Public Function GetSheetName()

Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("D:\商品テーブル.xlsx", 0, False)

For Each xlSheet In xlBook.WorkSheets
Debug.Print xlSheet.Name
Next

xlBook.Close
xlApp.Quit

End Function

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる