[[20150721215732]]　『データ収集』（ちぃさん）　ページの最後に飛ぶ

『データ収集』（ちぃさん）

お世話になります。

デスクトップに保存されて閉じられている状態の「Book.xlsx」から

使用中の「Book1.xlsx」にデータを拾いたいのでマクロの記録をとったのですが

あと何を追加しればいいでしょうか？

Sub Macro5()
'
' Macro5 Macro
'

    Windows("Book.xlsx").Activate
    Columns("C:F").Select
    Selection.Copy
    Windows("Book1.xlsx").Activate
    Range("A1").Select
    ActiveSheet.Paste
End Sub

どうかお助けください。

宜しくお願い致します。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 ＞デスクトップに保存されて閉じられている状態の「Book.xlsx」から

 先ず、そのブックを開くことじゃないですか？

 そうじゃないと、
 これは出来なかったと思うのですけど？）
 　　↓
 ＞ Windows("Book.xlsx").Activate

（半平太） 2015/07/21(火) 22:13

 もし、両方のブックが開かれている状態で、それらとは別のマクロブックの、このマクロを動かして値をコピペするということであれば
 このマクロに付け加えるところは特にないですね。
 （コードの書き方自体は、もう少しブラッシュアップもできますが）

 付け加えたい部分というのは、Book.xlsx　を開いて処理するというところですか？

（β） 2015/07/21(火) 22:15

（半平太）さん　ありがとうございます。

＞先ず、そのブックを開くことじゃないですか？

その開くことができないです。
マクロの記録は2つのブックを開いた状態で記録しました。

1,閉じられている　Book.xlsx　をひらき　Book1.xlsx　に転記する。

2,転記後、開かれた　Book.xlsx　は閉じる。

をしたいです。

宜しくお願いします。

（β）さん　ありがとうございます。

＞付け加えたい部分というのは、Book.xlsx　を開いて処理するというところですか？

はい。そうです。

宜しくお願いします。
（ちぃさん） 2015/07/21(火) 22:22

 コピペはCopy->Pasteでもできますが Copy一本でもできます。
 あまりSelectやActivateを使わず処理することが望ましいですね。
 それと、値のハンドリングは、ブックではなく、あくまでシートを対象に行います。
 アップされたコードでは、Book.xlsxの中のたまたまアクティブシートになっているシートから
 Book1.xlsxの、たまたま、その時にアクティブになっていたシートに値を転記することになりますよ。

 一例です。

 Sub Test()
    Dim fSh As Worksheet
    Dim tSh As Worksheet

    '処理中の画面の動きを隠す
    Application.ScreenUpdating = False
    'すでに開かれているブックの転記先シートを規定
    Set tSh = Workbooks("Book1.xlsx").Sheets("Sheet1")
    'デスクトップに保存してあるブックを開き、転記元シートを規定
    Set fSh = Workbooks.Open(CreateObject("WScript.Shell").SpecialFolders("DeskTop") & "\Book.xlsx").Sheets("Sheet1")
    '転記
    fSh.Columns("C:F").Copy tSh.Range("A1")
    '転記元ブックを閉じる
    fSh.Parent.Close False
    '転記先シートを表示
    Application.Goto tSh.Range("A1")

 End Sub

（β） 2015/07/21(火) 22:29

 ＞その開くことができないです。 
 ＞マクロの記録は2つのブックを開いた状態で記録しました。 
 ＞
 ＞1,閉じられている　Book.xlsx　をひらき　Book1.xlsx　に転記する。

 開くところから記録しなければいけないので
 Book.xlsxは閉じた状態から、マクロの記録をスタートするのですよ。

 ・・・と云ったところで、今となっては聞く耳持たないでしょうけど。。。

（半平太） 2015/07/21(火) 22:44

（β）さん　ありがとうございます。

ご教授いただいたコード動作は希望する動作です。

すみません。
説明に足らずがありました。

転記するのは値のみにしたいのですが。

.Valueを付けたし

 Sub Test()
    Dim fSh As Worksheet
    Dim tSh As Worksheet

    '処理中の画面の動きを隠す
    Application.ScreenUpdating = False
    'すでに開かれているブックの転記先シートを規定
    Set tSh = Workbooks("Book1.xlsm").Sheets("Sheet1").Value
    'デスクトップに保存してあるブックを開き、転記元シートを規定
    Set fSh = Workbooks.Open(CreateObject("WScript.Shell").SpecialFolders("DeskTop") & "\Book.xlsx").Sheets("Sheet1").Value

    '転記
    fSh.Columns("C:F").Copy tSh.Range("A1")
    '転記元ブックを閉じる
    fSh.Parent.Close False
    '転記先シートを表示
    Application.Goto tSh.Range("A1")

 End Sub

としたのですが

 Set tSh = Workbooks("Book1.xlsm").Sheets("Sheet1").Value
ここで

438 オブジェクトは、このプロパティまたはメソッドをサポートしていません。

とでます。

変数の所を　Variant　にしても同じでした。

転記するものが値となる場合は全く内容がかわってしまうのでしょうか？

すみません。

今一度お願いできますでしょうか。

＞コピペはCopy->Pasteでもできますが Copy一本でもできます
どういうことでしょうか？

（半平太）さん　ありがとうございます。

＞Book.xlsxは閉じた状態から、マクロの記録をスタートするのですよ。
すみません。次回からそうしてみます。

（ちぃさん） 2015/07/22(水) 00:36

 コードの前に。

 半平太さんも指摘しておられますが、ブックを開く操作もマクロ記録できますし、また、「値のみの貼り付け」も、

 ・Ctrl/c
 ・貼り付けたい領域の左上隅のセルを選択
 ・形式を選択して貼り付け　値(V)

 こうすれば、値貼り付けのコードも取得できますよ。
 コードは以下に提示しますが、それとは別に、是非、ブックを開くところから値貼り付けのところまでの操作をマクロ記録してみてください。
 それを、βのコードと比較してみるのも有効かと思います。

 >>>コピペはCopy->Pasteでもできますが Copy一本でもできます 
 >>どういうことでしょうか？

 そちらのコードで、

 Selection.Copy
 ActiveSheet.Paste

 このようにわけて書いてありましたね。
 これは、書式も含めてすべてコピペするコードなんですが、それであれば

 転記元領域.Copy 転記先セル

 と一行で書くことができるということを申し上げました。βがアップしたコードでいえば

 fSh.Columns("C:F").Copy tSh.Range("A1")

 これです。

 で、コードです。コメントしたように、値貼り付けは、コピペコードでもできますし、マクロ記録をすると
 そのコピペコードが生成されますが、転記先領域の値 = 転記元領域の値　というコードでも可能ですし、通常はそちらを使います。
 そちらのコードで、列全体のコピペということだったので、βがアップしたコードもコピペでしたが、値のある部分のみの転記でいいようですから
 その部分も変更します。

    fSh.Columns("C:F").Copy tSh.Range("A1")

 これを

    With fSh.Range("A1", fSh.UsedRange).Columns("C:F")
        tSh.Range("A1:D1").Resize(.Rows.Count).Value = .Value
    End With

 にしてください。

（β） 2015/07/22(水) 04:53

（β）さん　ありがとうございます。

完璧です。

解決はできたのですがマクロの記録の部分で、転記元のBookを開く前からBook1に値で転記まで記録をとったのですが、

Sub Macro3()
'
' Macro3 Macro
'

    Columns("C:F").Select
    Selection.Copy
    Windows("Book1.xlsm").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E15").Select
End Sub

上記記録ではデータ元の「Book」が開かないのですが。

記録の取り方がわるいのでしょうか？

すみません。

今後のために今一度お願いできませんでしょうか。

どうぞ宜しくお願い致します。
（ちぃさん） 2015/07/22(水) 09:30

 転記先は Book1.xlsx　と書いておられましたが、このマクロブックなんですね？（Book1.xlsm)
 まぁ、それはそれとして、記録をとってみましょう。

 なるべく、生成されるコードがそのまま利用できる形で記録をとる「コツ」として、デスクトップに保存されている Book.xlsx を
 転記元シート（仮に Sheet1 とします）が表示されていない（ほかのシートが表示されている）状態で保存しておいてください。
 また、記録をとり始める前に、このマクロブックの転記先シート（これも、仮に Sheet1 にします）とは別のシートが
 表示されている状態にしてください。 (理由は後述)

 ではマクロ記録です。

 今、マクロブック(Book1.xlsm)の"Sheet1"以外が表示されている状態です。

 １．ファイルメニュー、「開く」で、デスクトップに保存してある Book.xlsx を開きます。
 ２．目的のシート、SHeet1 以外が表示されているはずですから、Sheet1を選択します。
 ３．C:F列を選択して Ctrl/c
 ４．マクロブックを選択します。
 ５．転記先シート（Sheet1)以外が表示されているはずですから、Sheet1 を選択します。
 ６．A1 を選択します
 ７．貼り付けメニューで形式を選択して貼り付け。値(v)を選んで　ＯＫ
 ８．Book.xlsx を選択します。
 ９．コピペ領域が、点線で囲まれコピー状態になっていますので、Escキーでリセットします。
 １０．Book.xlsx を　閉じます。

 これで、マクロ記録終了。

 両方のブック、対象外のシートを表示しておいてマクロ記録をとり始めました。
 なので、生成されたコードには Sheets("Sheet1").Select というものがあるはずです。
 これがないと、たまたま、そのシートがアクティブであればいいのですが、別のシートがアクティブだった場合
 その別のシートを相手に、参照したり書きこんだりしてしまいます。
 なので、目的のシートを確実に表示させるための　シート.Select コードが生成されるように、マクロ記録をとる前に
 ちょっと準備してもらいました。

 自動生成コードの宿命で　○○.Select とか ○○.Activate とか Selection.なんたら　といったコードが
 てんこ盛りになっています。本来は、これらをブラッシュアップして、整えて完成させるべきなんですが
 とりあえずは、こうして生成されたコードは、そのまま（下記を除き）使えます。

 ブックを開くところ、

 Workbooks.Open Filename:="C:\Users\xxxxxx\Desktop\Book.xlsx"

 このようなコードが生成されているはずです。この中の xxxxxx は、このＰＣのログインＩＤです。
 別のＰＣでは、この xxxxxx は異なる値です。ですから、このコードを別ＰＣで実行するとエラーになります。
 この固定コードをやめ、動的に適切なものを取得する方法がいくついかあります。
 その代表的なものが、（β） 2015/07/21(火) 22:29　でアップした WScript.Shell を使う方法です。

（β） 2015/07/22(水) 10:02

（β）さん　ありがとうございます。

遅くなりまして申し訳ありません。

＞転記先は Book1.xlsx　と書いておられましたが、このマクロブックなんですね？（Book1.xlsm)
転記先はBook1のつもりですがなにか間違いがあるのですよね。
なぜでしょう。。。

教えて頂いた手順に沿って試したつもりなのですがやはり出来ないです。

Sub Macro5()
'
' Macro5 Macro
'

    Windows("Book.xlsx").Activate
    Columns("C:F").Select
    Selection.Copy
    Windows("Book1.xlsx").Activate
    Range("A1").Select
    ActiveSheet.Paste
End Sub

動かせると

インデックスが有効範囲にありません。

とでます。

（β）さん　の言われている

＞Workbooks.Open Filename:="C:\Users\xxxxxx\Desktop\Book.xlsx"
この部分がないからですよね。

何故でしょうか。
（ちぃさん） 2015/07/22(水) 18:28

連続投稿すみません。

＞今、マクロブック(Book1.xlsm)の"Sheet1"以外が表示されている状態です。
転記するBook1.xlsmは開いていてBook1.xlsmに記録させるので正しいですか？
（ちぃさん） 2015/07/22(水) 18:35

度々すみません。

アップしたの違うの載せてました。

Sub Macro5()
'
' Macro5 Macro
'

    Columns("D:G").Select
    Selection.Copy
    Windows("Book1.xlsm").Activate
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Book.xlsx").Activate
    ActiveWindow.Close
End Sub

こちらです。
（ちぃさん） 2015/07/22(水) 18:37

 転記先が、マクロブックなのか、第三の通常ブック（Book1.xlsx)なのかは、そちらの要件次第です。
 問題は、ブックを開くところがマクロ記録では生成されない？　ということですね。

 本当に、「マクロの記録」をクリックしてから、ファイルメニューで開く操作をやりましたか？
 Book.xlsx を開いた後で、マクロ記録を開始すると、もちろん、その部分は記録されませんが？

 そもそも（？）私が申し上げた手順通りにやれば、Sheets("Sheet1").Select といったコードが生成されるはずですが？

（β） 2015/07/22(水) 19:53

（β）さん　ありがとうございます。

＞今、マクロブック(Book1.xlsm)の"Sheet1"以外が表示されている状態です。
私がこの部分が勘違いしてたみたいです。

Sub Macro1()
'
' Macro1 Macro
'

    Workbooks.Open Filename:="C:\Users\Owner\Desktop\Book1.xlsm"
    Columns("D:D").Select
    Workbooks.Open Filename:="C:\Users\Owner\Desktop\Book.xlsx"
    Columns("D:G").Select
    Selection.Copy
    Windows("Book1.xlsm").Activate
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Book.xlsx").Activate
    ActiveWindow.Close
End Sub

ですがこれを　Book1　に書き動作させると、当たり前ですが
　
Book1は既に開いています。

とでます。

 Workbooks.Open Filename:="C:\Users\Owner\Desktop\Book1.xlsm"

この部分を消すと

実行時エラー'1004'

がでます。

これまだ記録の取り方が間違っているのでしょうか？
（ちぃさん） 2015/07/22(水) 22:41

 Book1.xlsx とか Book.xlsx とか Book1.xlsm という名前だと紛らわしいので
 転記元ブック と 転記先ブック　という呼び方で整理しましょう。

 また、転記先ブックとして、このマクロが書かれているマクロブックを考えておられるのか
 そうではなく別のブックを考えておられるのか、それによって、手順も変わってきます。

 また、マクロ実行時に、マクロブック以外は読みこまれていない状態を想定しておられるのか
 転記元ブックか、転記先ブックのいずれかがすでに（人間の手のよって）開かれている状態を
 考えておられるのかによっても手順がかわります。

 さらに、処理後、関連のブックは保存して閉じてしまうのか、そのままエクセルに残しておくのか
 それによっても手順が異なります。

 もう一度整理して、↑について、教えていただけますか。

（β） 2015/07/23(木) 06:32

（β）さん有難うございます。

遅くなり申し訳ありません。

転記元ブックは特に内容を変更する事はないです。

転記元のシート内には計算式が入っています。

計算式が入っていますので転記先にデータ値をとった時に｢0｣とか｢エラー表示｣がされるので値の転記を希望しました。

1.まず転記先シートを開きます。
2.転記元の必要な部分の内容のみ転記先に欲しいのでデータ採取します。

  この転記元は色々な人が使用します。
  転記元は値の変更が多々あります。
  データ値を採取する時は常に最新の値が欲しいです。
3.データ採取後は開いた時と何も変化のない状態でとじます。

転記元と転記先をこのように使います。

この説明でわかりますでしょうか？

ちょっと今、忙しく書き込みが遅くなってしまったりする場合があるかもしれません。

こちらの都合で本当に申し訳ございません。
（ちぃさん） 2015/07/23(木) 21:30

 それは全く気にしていません。時間ができたら、ゆっくりと。
 というか、すでに、マクロ記録の要領に関しては、おわかりになっていると思います。
 こちらが提示した手順の中で、やれマクロブックだの、やれ第三のブックだのという、ややこしいことを申し上げたため
 混乱させてしまったようです。

 転記先ブックが、マクロブックのことなのか、それとは異なるブックなのかが不明だったので。
 どうも、転記先ブックは、このマクロブックだったようですね。

 もう、すっかりおわかりのことですが、３つのポイントを、復習をかねて。
 時間ができて、さらなる疑問点があれば、遠慮なくアップしてください。

 １．マクロ記録は【マクロの記録】をクリックしたあと、【記録終了】をクリックするまでのすべての操作が記録される。

 　　マクロの記録をクリックする前に行った操作は記録されませんし、記録終了の後行った操作も記録されません。
 　　ですから、あるブックを開く　ということもマクロで実行したければ、マクロの記録をクリックした後、そのブックを開きます。
 　　逆に、マクロ実行時には、そのブックは開かれているという状態を前提にするなら、手作業で、そのブックを開いた後に
 　　マクロの記録を開始します。

 　　転記先ブックがマクロブック自身なら、当然、そのブックは開かれているわけですから、それを開くという操作は行いません。

 ２．自動記録マクロに限らず、マクロは、そこに書かれていること、そのものを、そのまま実行します。

 　　あたりまえですよね。でも、重要なポイントです。

 　　たとえば、あるブックを開くという操作をマクロ記録したとします。

 　　Sub Macro1()
         Workbooks.Open Filename:="C:\Users\Owner\Desktop\あるブック.xlsx"
     End Sub

 　　こんなコードが生成されますね。

 　　で、このマクロを実行するタイミングで、もし、エクセル区画で、この　あるブック.xlsx が開かれていた場合
 　　当然に、「すでに開かれているぞ～～！！」と、叱られますね。

 　　マクロを実行するタイミングでは、その時のエクセル区画の状態が【マクロを記録した時と同じ状態】でなければいけません。
 　　マクロを記録した時点では、エクセル区画に　あるブック.xlsx は開かれていませんでしたよね。
 　　開かれていなかったから、それを開くという操作ができたわけですし、結果としてコードが生成されたわけですから。

 ３．状況依存

 　　これは、ちょっとわかりにくいかもしれません。
 　　たとえば、あるブック.xlsx を開き、その Sheet1 の A1 の値を　マクロブックの Sheet2 の　B10 にコピペ。
 　　この操作をしたとします。

 　　１）あるブック.xlsx を開きます。
 　　２）たまたま、Sheet1 が表示された形で保存されていたとすると、当たり前ですが、目の前に Sheet1 があり、コピーすべき A1 も目の前にあります。
　       さらに、「たまたま」A1 が選択されていたとします。
 　　３）操作としては 【そのまま】、Ctrl/c
 　　４）次に、マクロブックを選択します。
 　　５）この時も、「たまたま」目的のSheet2 が（マクロ自動記録以前の操作で）表示されていたとします。コピー先の B10 も目の前にあります。
 　　６）なので、操作としては、B10 を選択して Ctrl/v

 　　さぁ、これで完璧だ？

 　　できあがったコードは以下です。

  Sub Macro1()
 '
 ' Macro1 Macro
 '

 '
     Workbooks.Open Filename:="C:\Users\xxxxxx\Desktop\あるブック.xlsx"
     Selection.Copy
     Windows("マクロブック.xlsm").Activate
     Range("B10").Select
     ActiveSheet.Paste
 End Sub

 　　で、あるとき、これを実行します。
 　　あるブック.xlsx は、それ以前の保存時、Sheet2 の F20 が選択された状態で保存されていたとします。
 　　そうすると・・・・
 　　開いて、そのままの状態で、Ctrl/c ですね。つまり、マクロ記録時は、確かに Sheet1 の A1 をコピーしたはずなのに
 　　Sheet2 の F20 がコピーされてしまいます。

 　　さらに、マクロブックを選択しますね。これも、たまたま 目的の Sheet2 ではない別のシートが（マクロ実行前の操作で）選択されていたとします。
 　　そうすると、Range("B10").Select　は、Sheet2のB10ではなく、別のシートの B10 になり、そこにペーストされます。

 　　結果として、転記元も転記先も、意図通りではないということになってしまいます。
 　　こういったコードを【状況依存】コードと呼びます。
 　　これって、記録マクロであれ、自分で書いたマクロであれ、おうおうにしてあることなんです。

 　　ですから、複数のブック、そこに複数のシートも存在する環境でのマクロでは、必ず　「どのブック」の「どのシート」なのかを
 　　コードの中で明示してやることが必須です。

 　この状況依存のコードになってしまうリスクを回避するため、（β） 2015/07/22(水) 10:02 　で、マクロ記録をとるときの【コツ】ということをコメントしました。

（β） 2015/07/24(金) 07:46

（β）さん　ありがとうございます。

解決できました。

Sub Macro2()
'
' Macro2 Macro
'

    Sheets("Sheet1").Select
    Workbooks.Open Filename:="C:\Users\Owner\Desktop\転記元.xlsx"
    Columns("B:D").Select
    Selection.Copy
    Windows("転記先.xlsm").Activate
    Columns("B:D").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("転記元.xlsx").Activate
    ActiveWindow.Close
End Sub

なぜできなかったのか原因は分かりませんので繰り返し作成してみます。

長々とお付き合い頂きまして感謝します。

また解決できないときに、お助け頂ければ幸いです。

ありがとうございました。
（ちぃさん） 2015/07/24(金) 17:56

コメント返信：

[ 一覧(最新更新順) ]

[[20150721215732]] 『データ収集』（ちぃさん） ページの最後に飛ぶ

[[20150721215732]]　『データ収集』（ちぃさん）　ページの最後に飛ぶ