VBA EXCEL 開かずにデータ操作

現在下記のようなコードでテストしています。

Sub Sample()

Dim TargetBook As Workbook

　　　Set TargetBook = Workbooks.Open("Book2.xls")
　　　TargetBook.Sheets("Sheet1").Cells(1, 5) = Cells(1, 1)
　　　TargetBook.Close

End Sub

Book2を開いてセルの読み書きはテストできました。
これをBook2をopenせず閉じた状態で操作したいと思っています。
Workbooksの表示されるメソッドを色々試してみますが出来ません。
OpenDatabaseが使用できるのかと思いますが、出来ません。
ご指導よろしくお願いします。

No.1 ベストアンサー 回答者： Wendy02 回答日時： 2011/01/06 13:04

Sub Sample2()

Dim myPath As String
Dim fn As String
　 myPath = Application.DefaultFilePath　'パス
　 fn = "Book2.xls"　'ファイル名
　 'R1C1型で書く
　 ret = Application.ExecuteExcel4Macro("'" & myPath & "[" & fn & "]Sheet1'!R1C1")
　 ThisWorkbook.Sheets("Sheet1").Cells(1, 5) = ret
End Sub

他にも、
>OpenDatabaseが使用できる
ODBC(Open Database Connectivity)という意味でしたら、[外部データの取り込み]から操作します。ただし、その使用する列全体を使用してしまいますので、取り込みには、その以下にデータを置くことは出来ない欠点があります。

Office をインストールしていれば、Jet はインストールしていますから、当然、ADODBなどは利用できますが、意外に面倒かもしれません。フィールドがなくても取得できます。

この回答への補足

bookを閉じた状態で読み込みする事が出来ました。
逆に書き込みを行うとした時にエラー424　オブジェクトが必要ですと出ます。
このメソッドで書き込みは出来ないのでしょうか？
Application.ExecuteExcel4Macro("'" & myPath & "[" & fn & "]Sheet1'!R1C1") = "test"

補足日時：2011/01/07 08:14

No.2 回答者： merlionXX 回答日時： 2011/01/06 13:16

開かないで操作する方法は存じませんが、開いたことを気づかせずに操作するなら以下でどうでしょう？

マクロを書いたBOOKのアクティブなシートのA1セルの値を転記するなら

Sub Sample01()
　　Dim TargetBook As Workbook
　　Dim ws As Worksheet
　　Set TargetBook = Workbooks.Open("Book2.xls")
　　Set ws = ThisWorkbook.ActiveSheet
　　With Application
　　　　.ScreenUpdating = False
　　　　TargetBook.Sheets("Sheet1").Cells(1, 5) = ws.Cells(1, 1)
　　　　TargetBook.Close (True)
　　　　.ScreenUpdating = True
　　End With
End Sub

そうではなく、BOOK２のシート1のA1セルの値を転記するなら

Sub Sample02()
　　Dim TargetBook As Workbook
　　Set TargetBook = Workbooks.Open("Book2.xls")
　　Application.ScreenUpdating = False
　　With TargetBook
　　　　.Sheets("Sheet1").Cells(1, 5) = .Sheets("Sheet1").Cells(1, 1)
　　　　.Close (True)
　　End With
　　Application.ScreenUpdating = True
End Sub

No.3 回答者： Wendy02 回答日時： 2011/01/07 09:07

>逆に書き込みを行うとした時にエラー424　オブジェクトが必要ですと出ます。

>このメソッドで書き込みは出来ないのでしょうか？

ExecuteExcel4Macro（一般数式）　Index 関数の利用も可能です。配列自体は取り出せませんが、配列からひとつの解を求めることは可能です。

これは読み出し専用です。書き込みするには、#2さんの方法や、ADODBやDAOを使いますが、ADODBをキーワードにして、Web検索をすればヒットするかとは思います。しかし、私自身、数年に1度書くかしないか程度で、実務的ではないし、しいてやれないことはない、という程度です。あまり、掲示板で披露した記憶がありません。

また、ADODBは、Excelの場合は、内部オブジェクトが備わっていないので、インスタンスを生成してから、値取得の段階に入っていきます。したがって、その時オーバーヘッドが掛かり、あまり思ったほどの効果はありません。

私からのアドバイスとしては、標準的な、ファイルを開いて、閉じる方法で良いのではないかと思います。

特に、#2さんの
Application.ScreenUpdating = False
　値-取得・値-書込
Application.ScreenUpdating = True
で、画面の変更のロスが減りますので、その分、時間が短縮できます。

私自身では、ADODB等で取得する場合は、データベースファイルに限ります。やはり、ファイルをOpenメソッドで開けて、それで書きこんで、保存するというオーソドックスな方法を使います。作成中に、目視して確認出来るという利点もあります。

なお、変則的には、私は、CreateObject("Excel.Application") というオートメーション化して使う場合があります。他の開いているブックに影響を受けないのと、マクロセキュリティに影響を受けない利点があります。しかし、これは、気を付けないと、メモリに残してしまうというミスを起こすことがあります。もうひとつは、絶対的とは言いませんが、.Value プロパティの省略などは、しないように注意しなければなりません。

No.4 回答者： Wendy02 回答日時： 2011/01/07 10:14

#3の追伸です。

ここをご覧になってください。

http://support.microsoft.com/kb/278973/ja
ADO を使用して Excel ブックのデータの読み取りおよび書き込みを行う方法 (ExcelADO)

サンプルファイル(ExcelADO.exe)もおいてあります。