[[20170509114259]]　『裏でデータ反映可否について』（JK）　ページの最後に飛ぶ

『裏でデータ反映可否について』（JK）

　またまたすいません。

現在、同一フォルダ内のマスターのシート内の値を持ってくるのに、
いちいち開くことなく(ある意味バックグラウンド的な)データ反映みたいなことが可能か教えてください。

<フォルダ構成>
作業フォルダ
　-研究データマスターファイルシート　-<1>
　-研究1シート　　　　　　　　　　　 -<2>
　-研究2シート
　-研究3シート
　　　・
　　　・
　　　・
　-研究10シート

　研究＊シート(<2>)を開き、マスターファイルシート<1>から値を
　持ってくる。
　それはVLOOKUP関数を考えている。

  関数イメージ
　「=IF(ISERROR(VLOOKUP($B24,("'研究データマスターファイルシート'!$B$11:$J$3205"),3,FALSE)),""
,VLOOKUP($B24,("'研究データマスターファイルシート'!$B$11:$J$3205"),3,FALSE))」

　ただし、これには都度「研究データマスターファイル」シートを開いておかないといけない。
※かつ、同一ファイルにもってこないといけない？

シート数が今後増えそうなので、都度シート移動してというのが
大変と想像しています。

よって、各研究シート<2>を開いただけで
研究データマスターファイルのデータを持ってくるようなことは可能でしょうか？

少し分かりづらい文章かもしれませんが、よろしくお願いします。

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

 一旦普通にマスタを開いた状態で、研究シートにリンク式を埋め込んでから、
 マスタを閉じればいいんじゃないですか？

 以後、いちいちマスタを開かなくても 自動的に更新されるハズですけど？
 （その後マスタのデータが更新されて、保存されていればの話ですけどね）

 慣れたら、マスタを開かないで、直接(長ーいパスの付いた）数式を入力すればいいです。

 ＞※かつ、同一ファイルにもってこないといけない？

 ちょっと真意が不明ですが、そんなことはないハズです。

 あと、バージョンがEx2010なら、IFERRORが使えるので、↓で事足ります。

  =IFERROR(VLOOKUP(E1,'パス\[研究データマスター.xlsx]ファイルシート'!$B$11:$J$3205,3,FALSE),"")
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↑
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　ここなんですけど、
                                          3列分しかないのにJ列まで指定するのは無駄です。
                                          多分、他にも、無駄なことをしている箇所があるんでしょうが、
                                          本題じゃないので、深入りはしません。

（半平太） 2017/05/09(火) 19:38

半平太様

　お世話になります。

＞一旦普通にマスタを開いた状態で、研究シートにリンク式を埋め込んでから、

 　マスタを閉じればいいんじゃないですか？

リンクと言いますと…？

=IFERROR(VLOOKUP(E1,'パス\～の数式を入れれば、データマスターのシートを開かなくても…ではなさそうですね。
ファイルを同時に開くと表示されたのですが、データマスターのシートを閉じ研究シートを立ち上げ直すと見事にクリアされました…。

＞一旦普通にマスタを開いた状態で、研究シートにリンク式を埋め込んでから、
＞マスタを閉じればいいんじゃないですか？
やはりここに書かれている通りですね。

＞以後、いちいちマスタを開かなくても自動的に更新されるハズですけど？
＞（その後マスタのデータが更新されて、保存されていればの話ですけどね）

＞慣れたら、マスタを開かないで、直接(長ーいパスの付いた）数式を入力すればいいです。
すいません、ここの記載が分からなくて。
一度、リンク？を通せば、次からマスタファイルを開かなくても行けるということでしょうか？

＞＞※かつ、同一ファイルにもってこないといけない？
説明が不足していました。
今まで私は同時にファイルを開き、マスタファイルの必要シートを
研究シートのファイルにシート毎コピーし、マスタファイルを閉じる
という手法でやっていました。
ただ、これだと都度シート移動が必要となるので、何かいい手法がないか
思案していたところでした。

＞ここなんですけど、
＞3列分しかないのにJ列まで指定するのは無駄です。
＞多分、他にも、無駄なことをしている箇所があるんでしょうが、
＞本題じゃないので、深入りはしません。
すいません、私の指定ミスでした。
そこは修正いたします。
（JK） 2017/05/10(水) 16:52

 ＞ファイルを同時に開くと表示されたのですが、
 ＞データマスターのシートを閉じ研究シートを立ち上げ直すと
 ＞見事にクリアされました…。

 うーん、私にはそちらの状況が全く把握できていない気がします。

 クリアされたと言うことは、全部エラーになったのと同義だとすると
 肝心なところにINDIRECT()関数を使っているのではないか、・・・
 と言う位の想像力しか働きません。

 想像力豊かな回答者のレスをお待ちください。

（半平太） 2017/05/10(水) 22:18

半平太様

　色々ご指導、ありがとうございました。
まだVBAに対する知識が低いことを痛感いたしました。
もっと本や他の人の指定を見て勉強したいと思います。

もう1つ可能であれば教えてください。

今回のシート作成の最後にCSVファイルを作成すると言うステップを組み込みます。

<コードイメージ>

Sub test02()
Worksheets("研究データマスター").Activate

    'strfilename = ActiveWorkbook.Path & "\結果data.csv"
    'Open strfilename For Output As #1

Range("B11", Cells(Rows.Count, "H")).Select

    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("a1：a19999").SpecialCells(xlCellTypeBlanks).EntireRow.Delete'ここが重い
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp

'CSVファイル作成

  Dim lastRow As Long, row As Long
  Dim fileNumber As Integer, csvFile As String

  csvFile = ActiveWorkbook.Path & "\Data\結果data.csv"

  lastRow = Range("A1").End(xlDown).row

  fileNumber = FreeFile

  Open csvFile For Output As #fileNumber

  For row = 1 To lastRow
    Write #fileNumber, Cells(row, 6), Cells(row, 1), Cells(row, 1), Cells(row, 7)
  Next

  Close fileNumber
'ここまで
End Sub

1.研究データマスターのシートよりセルB11～H列までをコピーする
2.コピーしたデータをSheet1へ貼り付ける
3.その際、A列セルのデータが空白セルのものがあれば行ごと削除する
4.データ加工が終わったものをcsvファイルへ書き出す

という流れです。

お伺いしたいのが、空白を探して削除するというステップが
約30秒～1分ほどかかってしまうので、何か解消する構文がないか？と言う点です。
データ量的には1万～あっても2万件以内です。
※実行するとたまにEXCELが「応答なし」になり、しばらくすると動き出すを
　繰り返しており、少し心配になったため。
　PCの性能？なのかもしれませんが…

もし解消方法等、ご存じでしたら教えてください。
また別スレにすべき等のご指摘がありましたら、そのように対応したいと思います。

どうぞよろしくお願いします。

（JK） 2017/05/12(金) 22:25

 ＞    Range("a1：a19999").SpecialCells(xlCellTypeBlanks).EntireRow.Delete'ここが重い

 Deleteは行数が多くなると重くなるのかも知れないですね。

 と言っても、１，２万行ならそれ程とも思えないのですが・・・

 発想を変えて、Deleteはしないで、CSVを作る時に選別したらどうですか？

 ＞  For row = 1 To lastRow
 ＞    Write #fileNumber, Cells(row, 6), Cells(row, 1), Cells(row, 1), Cells(row, 7)
 ＞  Next

     Dim Vals
     Vals = Range("A1:G" & lastRow).Value
     For Row = 1 To lastRow
         If Vals <> "" Then
             Write #fileNumber, Vals(Row, 6), Vals(Row, 1), Vals(Row, 1), Vals(Row, 7)
         End If
     Next

 ＞また別スレにすべき等のご指摘がありましたら、そのように対応したいと思います。

 上の案で解決しない場合は、別スレにしてください。
 その際、新規の質問に関連する記述は(私のレスも含めて）完全に削除してください。
 そうしないと、一人時間差(？）マルチポストみたいになってややこしくなりますので。

（半平太） 2017/05/12(金) 23:10

半平太様

　さっそくの回答ありがとうございます。

どうも時間がかかる理由としては

    Range("a1：a19999").SpecialCells(xlCellTypeBlanks).EntireRow.Delete'ここが重い
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp

ここで範囲指定するが、空白セルを削除する際に列全ての空白セルを削除しているように見えました。
結果後にスクロールすると一番下部(約100万行？)あたりまで選択されていたので…。

正直、理由や回避策は分かりませんが、CSVファイルは作成されていますので
これで行ってみたいと思います。

色々ご指摘ありがとうございます。

（JK） 2017/05/13(土) 07:41

 済みません。間違っていました。

 ＞  If Vals <> "" Then

  　 If Vals(Row,1) <> "" Then

（半平太） 2017/05/13(土) 08:04

コメント返信：

[ 一覧(最新更新順) ]

[[20170509114259]] 『裏でデータ反映可否について』（JK） ページの最後に飛ぶ

[[20170509114259]]　『裏でデータ反映可否について』（JK）　ページの最後に飛ぶ