エクセルVBAで他Bookを閉じたまま参照・訂正

質問

Cells(1, 1) = Application.ExecuteExcel4Macro _
("'C:\My Documents\[Book2.xls]Sheet1'!R1C1")
でBook2を開かずにセルを参照できますが、

１．別の記述方法があるのでしょうか。

２．逆に、閉じたBook2のセルに対し、値を訂正できないでしょうか。

よろしくお願いいたします。

回答No.1

kazuhiko5681 · ベストアンサー

こんにちわ。
以前私もあなたと同じことをやりたくてマイクロソフトのサポートセンターへ問い合わせたことがありますが、外部参照という機能を使わない限りブックを開かずにセルを参照することはできないそうです。
ですから、あなた様が書かれたコード以外書く方法はないと思います。
また、ブックのセルの値を訂正するには、そのブックを開かない限り訂正することができないそうです。

私でよろしければ、またサンプルマクロを作ってみたいと思います。ご希望の節は、あなた様がどの様なことをおやりになりたいのかお知らせ下さい。

お手数をおかけいたしますが、よろしくお願いいたします。

補足

度々の回答ありがとうございます。
そうでしたか、無理だったんですね。
今まで１台のＰＣで売上管理を行っていたのですが、その商品マスタとなるデータを
他のＰＣ（ＬＡＮ経由）と共有して、一方のＰＣを発注管理用としたかったのです。
このような事はエクセルでは、無理な使用法でしょうか。

回答No.4

kazuhiko5681

こんにちわ。
ちょっと次の操作をしてみて下さい。

１.現在ファイルが保存してあるＰＣでファイルを開く。
２.ツールメニューバーからツール→ブックの共有を順にクリックし、ブックの共有ダイアログボックスを開く
３.編集タブをクリックし、その中の複数のユーザーが同時に編集する。をチェックしてＯＫボタンをクリックする。
４.保存を確認するメッセージが表示されるのでＯＫボタンをクリックする。

もしこの方法でよろしければ、詳細につきましてはヘルプを参照して下さい。

１.ツールメニューバーからヘルプ→マイクロソフトヘルプを順にクリック。
２.質問タブをクリックし、ブックの共有と入力し、ＯＫボタンをクリック。
３.複数のユーザーが同じブックで作業する方法をダブルクリック。

もし、違っていた場合は、お許し下さい。

お礼

回答ありがとうございます。
共有ブックという機能を、全く知りませんでした。
試してみます。
ちょっと、質問の表題と異なる内容になってきましたので、締め切ります。

回答No.3

kazuhiko5681

こんばんわ。

私は、１台のＰＣでしか操作をしたことがないので、ネットワークのことに関しては全くの無知です。でも、何かあなた様がおやりになりたいことは、できる方法があるように思います。
そこで提案なのですが、マイクロソフトのサポートセンターに問い合わせてみたらいかがでしょうか。Office製品は、無償サーポートという特典がついております。電話番号をお知らせいたします。
０３(５３５４)４５００　です。

私が答えられれば答えたいのですが、ネットワークに関しては解りませんのでお許し下さい。

お礼

情報ありがとうございます。
電話してみたのですが、OfficeXPはＶＢＡが絡む質問は有料との事でした。
また、こちらが質問して、「それは無理です。」で終わっても１件と数えるそうなので、そこでやめてしまいました。
また、別の形で質問を出すかもしれませんが、よろしくお願い致します。

回答No.2

kazuhiko5681

こんばんわ。前回私の作ったサンプルマクロの内容がお解りにならないということですので、この場をお借りして説明をさせていただきたいと思います。

Private Sub Workbook_Open()
このイベントは、このブック(商品台帳)が開いたときに走るイベントです。

Dim myBook As Workbook　
Dim myWbn As String

For Each myBook In Workbooks
現在開いているブックの名前をすべてチェックします。
開いているブックの数だけ繰り返されます。
myWbn = myBook.Name
ブックの名前を変数に代入します。
If myBook.Name = "商品コード.xls" Then Exit Sub
商品台帳が開いていたら処理を終了させます。
Next
商品台帳が閉じていた時のみ以下のコードが実行されます。
Workbooks.Open ("C:\My Documents\商品コード.xls")
Workbooks("商品台帳.xls").Activate

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

このイベントはシートに変化が起きたときに走るイベントです。
・セルに値を入力し、エンターキー等でフォーカスを移した時。
・セルの値を削除し、エンターキー等でフォーカスを移した時
・行選択をして行を削除した時。範囲選択をして選択された行を削除した時。
このような動作が行われた時、すべて走ります。

Dim myRow As Integer

myRow = Target.Row
If Target.Address = Range("A" & myRow).Address _
Or Target.Address = Range("B" & myRow).Address Then
target.rowは、行選択をした時先頭行の行番号・範囲選択をした時も先頭の行番号・セル選択の時もそのセルの行番号を返しています。
target.addressは行選択をした時＄○：＄△・範囲選択の時例えば$A$3:$H$10・セル選択の時例えば$B$3という値を返しています。
今回はＡ列とＢ列で一つのセルの値が変化した時のみマクロを走らせたいので、このように書きます。

Set myWsn = Workbooks("商品コード.xls").Worksheets(1)
myWsnがWorkbooks("商品コード.xls").Worksheets(1)の代わりとして使えるように、setステートメントを使ってmyWsnに代入しています。

myCell = myWsn.Range("A1").CurrentRegion.SpecialCells(xlCellTypeLastCell).Address
CurrentRegionは、データの入力されている範囲を参照しています。
SpecialCells(xlCellTypeLastCell)は上の範囲のデータの入っている最終セルを参照しています。
つまり、myCellに商品コードにデータの入力されている最終セルのセル番地を代入しているのです。

Set myRange = myWsn.Range("A2:" & myCell).Find(Target.Value, lookat:=xlWhole)
target.valueは、商品台帳に入力された商品コードを表しています。
つまり、商品コードのA2から入力された最終行の範囲の中に入力された商品コードがあるかどうか探し、あればレンジオブジェクトをなければNothingを
変数に返します。

Application.EnableEvents = False
If myRange Is Nothing Then
入力された商品コード・商品名がなかった時の処理
If Target.Address = Range("A" & myRow).Address Then
入力された商品コードがなかった時の処理
myWsn.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Target.Value
Rangeオブジェクトの表し方は２通りあります。Ａ５を例にするとRange("A5")という方法とCells(5,1)という方法です。Range("A5")の5を変数で表す時は、Range("A" & 変数)となります。
Cells(Rows.Count,1)は、Ａ列の最終行を表しています。
Cells(Rows.Count,1).End(xlup)は、Ａ列の最終行から上方向へ入力されている最終セルを参照しています。
Offsetは、セル番地を移動する時に使います。表し方は、Offset(行,列)です。B2を基準にA1はoffset(-1,-1),B1はoffset(-1,0),C1はoffset(-1,1),C2はoffset(0,1),C3はoffset(1,1),B3はOffset(1,0),A3はOffset(1,-1),A2はOffset(0,-1)と表します。
つまり、この式の意味は、商品コードのＡ列に入力されている最終行の１つ下のセルに商品台帳に入力された商品コードを入力せよということになります。
Else
入力された商品名がなかった時の処理
myWsn.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = Target.Value
End If
Else
商品台帳に入力された商品コードがあったときの処理
Target.Offset(0, 1).Value = myRange.Offset(0, 1).Value
Target.Offset(0, 1).Columns.EntireColumn.AutoFit
End If
Application.EnableEvents = True
End If

End Sub

まだ解らないことがありましたら、遠慮なくお知らせ下さい。
それから、私がＶＢＡを勉強するのに役に立った本をご紹介させていただきます。技術評論社から出ている大村あつしさんが書いた簡単プログラミングＥｘｃｌｅ２０００ＶＢＡという本です。基礎編・関数偏・応用編の３部作になっています。これはとっても解りやすく書かれています。その他に辞書としてメソッドやプロパティを調べるのに使っている本は、ローカスという出版社から出ている逆引きＥｘｃｅｌ２０００ＶＢＡ偏です。ＶＢＡが持っているすべての機能やプロパティ・メソッド等が記載されています。この４冊があれば、十分です。

お互いにこれからも頑張っていきましょう。

お礼

ありがとうございます、理解できました。
辞書の様な本も必要と思ってました、さっそく本屋に行ってきます。