Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : 指定なし)
OpenDatabaseについて
投稿日時: 18/06/19 11:34:27
投稿者: けん太

見よう見まねで、EXCELのシートを指定して
ACCESSへ取込を考えております。
 
なんとか取込までは出来るようになりましたが、他のファイルで以下のエラーとなっています。
 
シート名に漢字で31文字以上設定されているため、エラーとなってしまいます。
シート名称をVBAで変更しようとしましたが、エラーとなってしまいました。
 
どのようなコマンドを使用すればよいのでしょうか、
ご教授お願いいたします。
 
  Set Mydb = OpenDatabase("C:Book1.xls", False, False, "excel 12.0;hdr=no;")
 
     wsName = Mydb.TableDefs(5).Name
     Mydb.TableDefs(5).Name = "KOUSHIN" ←このタイプのオブジェクトには実行できません。
                      とエラーになる。
 
 
 
    '取得したシート名に付加された"$"マークを取り除きます。
     wsName = Mid(wsName, 1, InStr(1, wsName, "$", 1) - 1)
 
mydb.close

回答
投稿日時: 18/06/19 14:09:24
投稿者: Suzu

今回のは、エラーメッセージ のままの意味でしょう。
排他モードでアクセスしないと権限が無いのでは?
 
でも、ExcelをソースとするTableDefの名称を変える事はできません。 と言う意味かと。
(TableDef となり得るのは、ワークシート/名前定義済範囲 だったと思います うろ覚え。。)
Excelのオブジェクトとしては全くの別物ですよね。
 
それを、Nameプロパティー を触るだけで、簡単に変える事ができたら。。チョット怖いです。。
なので、仕様として、Nameプロパティーは読み取り専用になってると思います。
 
必要なのであれば、オートメーションにて、Excelを立ち上げ、
ワークシートオブジェクトの名称を変えましょう。。

回答
投稿日時: 18/06/19 14:41:53
投稿者: sk

引用:
見よう見まねで、EXCELのシートを指定して
ACCESSへ取込を考えております。

引用:
シート名に漢字で31文字以上設定されているため、エラーとなってしまいます。

Excel ワークシートを Access のテーブルとして取り込む
(インポートする)操作/コードと、Excel ワークシートの
文字数との因果関係が不明瞭です。
 
引用:
シート名称をVBAで変更しようとしましたが、エラーとなってしまいました。

引用:
Set Mydb = OpenDatabase("C:Book1.xls", False, False, "excel 12.0;hdr=no;")
wsName = Mydb.TableDefs(5).Name
Mydb.TableDefs(5).Name = "KOUSHIN"

DAO の TableDef オブジェクトを介して
Excel ワークシートの名前を変更することは出来ません。
 
ワークシートの名前の変更は、オートメーションによる
Excel アプリケーションの操作の範疇で行なえばよいでしょう。
 
Suzu さんの引用:
排他モードでアクセスしないと権限が無いのでは?

排他モードで開いても無理です。
 
ここではあくまで、Excel ISAM ドライバーを介して
Excel ブックを 1 つのデータベース、その中に含まれている
それぞれのワークシートをテーブルに見立ててアクセスしている
に過ぎず、元来データベースファイルではない Excel ブックの
構造を書き換えることまでは出来ません。

投稿日時: 18/06/19 15:12:05
投稿者: けん太

ご回答、ありがとうございます。
 
通常ですと、OpenDatabaseを使用して、TransferSpreadsheetでACCESSへ取込ができるのですが、
EXCEL出力の条件が下記だったので、シート名を変更できれば、可能と判断して、
質問いたしました。
 
TransferSpreadsheetを使用せずに指定シートをインポートする方法は
あるのでしょうか?
 
-----------------------------------------
シート名:全角含む31文字以上で設定される。(システム仕様。 毎回名前は変わる)
取込シート:毎回決まった位置に設定される。

回答
投稿日時: 18/06/19 18:10:41
投稿者: sk

引用:
通常ですと、OpenDatabaseを使用して、TransferSpreadsheetでACCESSへ取込ができるのですが、
EXCEL出力の条件が下記だったので、シート名を変更できれば、可能と判断して、
質問いたしました。

「ワークシート変換によるインポート」自体は
基本的に TransferSpreadsheet メソッドにおける
引数の指定が正しければ実現可能であるはずですが、
具体的にどのようなコードを記述されているのでしょうか。

投稿日時: 18/06/20 09:24:55
投稿者: けん太

skさん
ありがとうございます。 
 
下記のようなロジックを組んでおります。
 
ファイルダイヤログで指定されたファイルパスより
OpenDatabaseで対象のExcel シートを取り出すものとなっております。
 
シート名は、現在、全角で27文字あります。(全角可変の値となる)
 
--------------------------
    With Application.FileDialog(msoFileDialogFilePicker)
 
        .InitialFileName = ""
        .AllowMultiSelect = True
        .Filters.Add "ファイル", "*.xls,*.xlsx"
     
     
        'FileDialogSelectedItems 複数選択されたファイルを検索
 
     
         If .Show = True Then
            For Item_cnt = 1 To .SelectedItems.Count
                file_pass = .SelectedItems(Item_cnt)
 
                findno = InStr(file_pass, B_PRO_FILE)
                 
                If findno <> 0 Then
                     
                    B_PRO_SECTION
 
                End If
                 
                 
            Next Item_cnt
        Else
            MsgBox "キャンセルのため処理を中止します。"
        End If
             
          
    End With
 
 -------------
B_PRO_SECTION の内容
 
 
    Set Mydb = OpenDatabase(file_pass, False, False, "excel 8.0;hdr=no;") '共有モード、更新でOPEN
        sheet_name = Mydb.TableDefs(5).Name
        '取得したシート名に付加された"$"マークを取り除きます。
        sheet_name = Mid(sheet_name, 1, InStr(1, sheet_name, "$", 1) - 1)
  
    Mydb.Close
           
             
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, B_PRO_TBL, file_pass, True, sheet_name & "!" '1行目をフィールド名とする。(true)

投稿日時: 18/06/20 10:31:18
投稿者: けん太

みなさん(skさん)、失礼いたしました。
 
シート名に全額で、#が入っていたのが、原因でした。
取込を行うには、シート名を変更しおなければ、なりませんが、
 
OpenDatabase ではエラーになるので、どのようなコマンドが
最適なのか、ご教授お願いいたします。
 
詳細は、ヘルプや、ネットで調査いたします。

回答
投稿日時: 18/06/20 11:10:54
投稿者: sk

引用:
シート名に全額で、#が入っていたのが、原因でした。

半角の # では?
 
引用:
sheet_name = Mydb.TableDefs(5).Name
'取得したシート名に付加された"$"マークを取り除きます。
sheet_name = Mid(sheet_name, 1, InStr(1, sheet_name, "$", 1) - 1)

sheet_name = Mydb.TableDefs(5).Name
If sheet_name Like "[']*[$][']" Then
    '取得したシート名に付加された"$"を"!"に置き換えます。
    sheet_name = Left(sheet_name, Len(sheet_name) - 2) & "!'"
End If
 
If sheet_name Like "*[$]" Then
    '取得したシート名に付加された"$"を"!"に置き換えます。
    sheet_name = Left(sheet_name, Len(sheet_name) - 1) & "!"
End If
 
引用:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, B_PRO_TBL, file_pass, True, sheet_name & "!"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, B_PRO_TBL, file_pass, True, sheet_name
 
-------------------------------------------------------------------------
 
とりあえず、以上のように書き換えてみて下さい。

投稿日時: 18/06/20 11:48:02
投稿者: けん太

SKさん
ありがとうございます。 
エラーとならずに、取込できました。
 
今後の為(勉強の為)に、原因は何にあるのでしょうか?

回答
投稿日時: 18/06/20 12:11:11
投稿者: sk

引用:
今後の為(勉強の為)に、原因は何にあるのでしょうか?

・DAO の Database オブジェクトを介して
 Excel ブックをデータベースとして開いた場合、
 その TableDefs コレクションによって返される
 (テーブルとして認識される)のは、ブック内の
 全ての「ワークシート」と「名前付きセル範囲」である。
 
・TableDef オブジェクトの参照先がワークシートである場合、
 そのテーブル定義の名前の末尾には "$" が付加される。
 逆に名前付きセル範囲である場合は付加されない。
 
・ワークシートの名前に "." のような特殊記号
 (データベースで予約されている修飾子)が含まれている場合、
 自動的に "#" のような別の記号に変換された文字列が
 そのテーブル定義の名前となる。
 
・ワークシートの元の名前に "#" が含まれている場合、
 更に名前全体が "'" で囲まれた文字列が、
 そのテーブル定義の名前となる。
 
・Access 側から TransferSpreadsheet メソッドによって
 インポートを実行する際、引数 Range に任意のワークシート名を
 指定する場合は、末尾の "$" の代わりに "!" を指定する。
 
・インポートを実行する際に "!" に書き換えるべきなのは
 あくまで「末尾の "$" 」だけであって、元のワークシート名に
 含まれている "$" まで書き換えるべきではない。
 (したがって InStr で「最初の "$" 」の位置を探そうとするのは危ない)
 また「 6 番目のテーブル定義」の参照先が常にワークシートである
 という保証がないなら、無条件に "!" を付加するべきではない。

投稿日時: 18/06/20 12:35:32
投稿者: けん太

SKさん
 
ありがとうございます。 勉強になりました。
自分で調べただけでは、ここまではわかりませんでした。