Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


4370 / 13644 ツリー ←次へ | 前へ→

【56990】リンクの更新 菜実 08/7/16(水) 16:26 質問[未読]
【57002】Re:リンクの更新 ハチ 08/7/17(木) 9:16 発言[未読]
【57003】Re:リンクの更新 菜実 08/7/17(木) 9:39 発言[未読]
【57005】Re:リンクの更新 ハチ 08/7/17(木) 11:16 発言[未読]
【57007】Re:リンクの更新 菜実 08/7/17(木) 11:31 お礼[未読]
【57009】Re:リンクの更新 ハチ 08/7/17(木) 13:01 発言[未読]
【57013】Re:リンクの更新 菜実 08/7/17(木) 13:58 お礼[未読]
【57035】Re:リンクの更新 菜実 08/7/18(金) 10:02 質問[未読]
【57036】Re:リンクの更新 ハチ 08/7/18(金) 11:17 発言[未読]
【57043】【訂正】Re:リンクの更新 ハチ 08/7/18(金) 17:08 発言[未読]
【57074】Re:【訂正】Re:リンクの更新 菜実 08/7/22(火) 9:39 お礼[未読]

【56990】リンクの更新
質問  菜実  - 08/7/16(水) 16:26 -

引用なし
パスワード
   こんにちは。
こちらで質問させていただいていいのか迷いましたが
先日、わかりやすい回答をいただきましたので、
こちらで質問させていただきたいと思います。
場違いかもしれませんが、よろしくお願いします。

ファイル起動時に、「リンクを更新しますか?」のメッセージが出てきます。
更新するを選ぶと
「このブックには更新できないリンクが1つ以上含まれています」というメッセージが出ます。
このメッセージを出さない設定はわかりますが、そのように設定した場合、
ファイルを開くまでに時間がかかって、もう少し早く開かないかなと思っています。
リンク元のファイルを開いていない状態でリンク先のファイルの参照セルの計算は
どうすればできますか?

【57002】Re:リンクの更新
発言  ハチ  - 08/7/17(木) 9:16 -

引用なし
パスワード
   ▼菜実 さん:
>ファイル起動時に、「リンクを更新しますか?」のメッセージが出てきます。
>更新するを選ぶと
>「このブックには更新できないリンクが1つ以上含まれています」というメッセージが出ます。
>このメッセージを出さない設定はわかりますが、

『メッセージを出さない設定』とは、なんの設定のことを指していますか?
「リンクの自動更新前にメッセージを表示する」のチェックを外すことですか?

>そのように設定した場合、
>ファイルを開くまでに時間がかかって、もう少し早く開かないかなと思っています。
>リンク元のファイルを開いていない状態でリンク先のファイルの参照セルの計算は
>どうすればできますか?

優先したい事項はなんでしょうか?

・ファイルを早く開きたい?
・リンク元のファイルを開きたくない?
・データは最新化したい?

【57003】Re:リンクの更新
発言  菜実  - 08/7/17(木) 9:39 -

引用なし
パスワード
   ▼ハチ さん:

おはようございます。
レスありがとうございます。

>▼菜実 さん:
>>ファイル起動時に、「リンクを更新しますか?」のメッセージが出てきます。
>>更新するを選ぶと
>>「このブックには更新できないリンクが1つ以上含まれています」というメッセージが出ます。
>>このメッセージを出さない設定はわかりますが、
>
>『メッセージを出さない設定』とは、なんの設定のことを指していますか?
>「リンクの自動更新前にメッセージを表示する」のチェックを外すことですか?

チェックをはずすことです。


>
>>そのように設定した場合、
>>ファイルを開くまでに時間がかかって、もう少し早く開かないかなと思っています。
>>リンク元のファイルを開いていない状態でリンク先のファイルの参照セルの計算は
>>どうすればできますか?
>
>優先したい事項はなんでしょうか?
>
>・ファイルを早く開きたい?
>・リンク元のファイルを開きたくない?
>・データは最新化したい?

書いてくださっている上の3個を実現できればと思いました。
リンク元を開かず、データを最新化して、できるだけ早くファイルが開ければと思っています。
簡単なキーワードでぐぐってみたのですが、どうも私の検索の仕方が悪いみたいで、
なかなかHITしませんでした。
もし、参考になるURLなどありましたら、教えていただけませんか?

【57005】Re:リンクの更新
発言  ハチ  - 08/7/17(木) 11:16 -

引用なし
パスワード
   ▼菜実 さん:
>>・ファイルを早く開きたい?
>>・リンク元のファイルを開きたくない?
>>・データは最新化したい?
>
>書いてくださっている上の3個を実現できればと思いました。
>リンク元を開かず、データを最新化して、できるだけ早くファイルが開ければと思っています。

そんなに都合の良い機能は、無いと思いますよ。
再計算を手動にすれば、多少早くはなるかもしれませんが効果は薄いと思います。

他Bookの参照で、VLOOKUPを多用すると異常に重くなりますが、
そういった使い方ですか?

どうしても直したいなら、
Bookの作り自体を見直す必要があるのでは?

【57007】Re:リンクの更新
お礼  菜実  - 08/7/17(木) 11:31 -

引用なし
パスワード
   ▼ハチ さん:

こんにちは。
お返事ありがとうございます。

>▼菜実 さん:
>>>・ファイルを早く開きたい?
>>>・リンク元のファイルを開きたくない?
>>>・データは最新化したい?
>>
>>書いてくださっている上の3個を実現できればと思いました。
>>リンク元を開かず、データを最新化して、できるだけ早くファイルが開ければと思っています。
>
>そんなに都合の良い機能は、無いと思いますよ。

そうなんですね。
なかなかファイルを開くのが遅くて困ってますが、
そこは我慢するしかないといった感じでしょうか?

>再計算を手動にすれば、多少早くはなるかもしれませんが効果は薄いと思います。
>
>他Bookの参照で、VLOOKUPを多用すると異常に重くなりますが、
>そういった使い方ですか?

Vlookupを使用しているのは、各ブックで1列だけです。
他は、リンク元の値を表示するのに単純な数式入れてます。

>どうしても直したいなら、
>Bookの作り自体を見直す必要があるのでは?

そうなんですね。
もう少し、考える必要があるということですね。

ありがとうございました。

【57009】Re:リンクの更新
発言  ハチ  - 08/7/17(木) 13:01 -

引用なし
パスワード
   ▼菜実 さん:

>Vlookupを使用しているのは、各ブックで1列だけです。
>他は、リンク元の値を表示するのに単純な数式入れてます。

何度も同じ値を参照するのであれば、
Book間のリンクは最小限にして、
自Bookにもってきた値を参照するようにすると改善されると思います。

以前に書いたサンプルがありましたので、
参考までに載せときます。(Book間VLOOKUPを改善したものです)

Sample1.xls, Sample2.xls, data.xlsの3つのBookが作成されます。
Sample1.xls と Sample2.xls の開く速度の違いを確認してください。

Option Explicit

Sub Sample_VLOOKUP()
  Dim Wb(2) As Workbook
  Dim i As Integer
  Dim myPath As String
  
  myPath = ThisWorkbook.Path
  
  'data.xls
  Set Wb(0) = Workbooks.Add(xlWBATWorksheet)
  With Wb(0)
    .Worksheets(1).Name = "data"
    With .Worksheets("data")
      .Range("A1:A1000").Formula = "=ROW()"
      .Range("B1:J1000").Formula = _
      "=""data"" & ROW() & ""-"" & COLUMN()"
      .UsedRange.Value = .UsedRange.Value
    End With
    .SaveAs myPath & "\data.xls"
  End With
  
  'Sample1.xls
  Set Wb(1) = Workbooks.Add(xlWBATWorksheet)
  With Wb(1)
    .Worksheets(1).Name = "main"
    With .Worksheets("main")
      .Range("A1:A1000").Formula = "=ROW()"
      .Range("A1:A1000").Value = .Range("A1:A1000").Value
      .Range("B1:J1000").Formula = _
      "=VLOOKUP($A1,'[data.xls]data'!$A$1:$J$1000,COLUMN(),0)"
    End With
    Wb(1).SaveAs myPath & "\Sample1.xls"
  End With
  
  'Sample2.xls
  Set Wb(2) = Workbooks.Add(xlWBATWorksheet)
  With Wb(2)
    .Worksheets.Add Before:=.Worksheets(1)
    .Worksheets(2).Name = "tmp"
    With .Worksheets("tmp")
      .Range("A1:J1000").Formula = "='[data.xls]data'!A1"
    End With
    
    .Worksheets(1).Name = "main"
    With .Worksheets("main")
      .Range("A1:A1000").Formula = "=ROW()"
      .Range("A1:A1000").Value = .Range("A1:A1000").Value
      .Range("B1:J1000").Formula = _
      "=VLOOKUP($A1,tmp!$A$1:$J$1000,COLUMN(),0)"
    End With
    Wb(2).SaveAs myPath & "\Sample2.xls"
  End With
  
  Application.AskToUpdateLinks = False
  
  For i = 0 To 2
    Wb(i).Close
    Set Wb(i) = Nothing
  Next
End Sub

【57013】Re:リンクの更新
お礼  菜実  - 08/7/17(木) 13:58 -

引用なし
パスワード
   ▼ハチ さん:

こんにちは。
ハチさんありがとうございます。
早速試しました。Sample2のほうが少し、早いような気がしました。
同じような方法で作り直してみようとおもいます。
リンク元の必要なデータを自ブックにもってくれば少し改善されるということですよね?

足りない頭で考えてみたんですが、
ブックを開くときに、リンク元の必要なデータをコピーして貼り付けて
それを参照するようにしてみようと思います。

他にいい方法あれば、アドバイスお願いします。


>▼菜実 さん:
>
>>Vlookupを使用しているのは、各ブックで1列だけです。
>>他は、リンク元の値を表示するのに単純な数式入れてます。
>
>何度も同じ値を参照するのであれば、
>Book間のリンクは最小限にして、
>自Bookにもってきた値を参照するようにすると改善されると思います。
>
>以前に書いたサンプルがありましたので、
>参考までに載せときます。(Book間VLOOKUPを改善したものです)
>
>Sample1.xls, Sample2.xls, data.xlsの3つのBookが作成されます。
>Sample1.xls と Sample2.xls の開く速度の違いを確認してください。
>
>Option Explicit
>
>Sub Sample_VLOOKUP()
>  Dim Wb(2) As Workbook
>  Dim i As Integer
>  Dim myPath As String
>  
>  myPath = ThisWorkbook.Path
>  
>  'data.xls
>  Set Wb(0) = Workbooks.Add(xlWBATWorksheet)
>  With Wb(0)
>    .Worksheets(1).Name = "data"
>    With .Worksheets("data")
>      .Range("A1:A1000").Formula = "=ROW()"
>      .Range("B1:J1000").Formula = _
>      "=""data"" & ROW() & ""-"" & COLUMN()"
>      .UsedRange.Value = .UsedRange.Value
>    End With
>    .SaveAs myPath & "\data.xls"
>  End With
>  
>  'Sample1.xls
>  Set Wb(1) = Workbooks.Add(xlWBATWorksheet)
>  With Wb(1)
>    .Worksheets(1).Name = "main"
>    With .Worksheets("main")
>      .Range("A1:A1000").Formula = "=ROW()"
>      .Range("A1:A1000").Value = .Range("A1:A1000").Value
>      .Range("B1:J1000").Formula = _
>      "=VLOOKUP($A1,'[data.xls]data'!$A$1:$J$1000,COLUMN(),0)"
>    End With
>    Wb(1).SaveAs myPath & "\Sample1.xls"
>  End With
>  
>  'Sample2.xls
>  Set Wb(2) = Workbooks.Add(xlWBATWorksheet)
>  With Wb(2)
>    .Worksheets.Add Before:=.Worksheets(1)
>    .Worksheets(2).Name = "tmp"
>    With .Worksheets("tmp")
>      .Range("A1:J1000").Formula = "='[data.xls]data'!A1"
>    End With
>    
>    .Worksheets(1).Name = "main"
>    With .Worksheets("main")
>      .Range("A1:A1000").Formula = "=ROW()"
>      .Range("A1:A1000").Value = .Range("A1:A1000").Value
>      .Range("B1:J1000").Formula = _
>      "=VLOOKUP($A1,tmp!$A$1:$J$1000,COLUMN(),0)"
>    End With
>    Wb(2).SaveAs myPath & "\Sample2.xls"
>  End With
>  
>  Application.AskToUpdateLinks = False
>  
>  For i = 0 To 2
>    Wb(i).Close
>    Set Wb(i) = Nothing
>  Next
>End Sub

【57035】Re:リンクの更新
質問  菜実  - 08/7/18(金) 10:02 -

引用なし
パスワード
   おはようございます。
Dataというシートをあらかじめ用意して
そこに別のブックの必要な場所のデータを転記することにしました。
コードを書いてみたものの何か自分が勘違いしていないか不安で、
変なところがないか見ていただけませんか?
また、悩んでいるのですが、ファイルを開いたときにこのマクロを実行させたいと
考えています。
その場合はどうしたらいいかアドバイスください。

Sub 更新()

  Dim i As Worksheet
  Dim j As Worksheet
  Dim k As Worksheet
  
  Application.ScreenUpdating = False
  Sheets("Data").Cells.ClearContents

  Set i = Workbooks.Open(ThisWorkbook.Path & "AAA.xls").Worksheets("設定")
  Set j = Workbooks("BBB.xls").Worksheets("Data")
  
  With j
    .Range("C1").Value = i.Range("b1")
    .Range("C2").Value = i.Range("b3")
    .Range("d2").Value = i.Range("c3")
    .Range("C3").Value = i.Range("b5")
    .Range("C4").Value = i.Range("a24")
    .Range("d4").Value = i.Range("b24")
    .Range("C5").Value = i.Range("c9")
    .Range("d5").Value = i.Range("d9")
    .Range("e5").Value = i.Range("e9")
    .Range("f5").Value = i.Range("f9")
    .Range("g5").Value = i.Range("g9")
    .Range("h5").Value = i.Range("h9")
    .Range("C6").Value = i.Range("c24")
    .Range("d6").Value = i.Range("d24")
    .Range("e6").Value = i.Range("e24")
    .Range("f6").Value = i.Range("f24")
    .Range("g6").Value = i.Range("g24")
    .Range("h6").Value = i.Range("h24")
    .Range("i6").Value = i.Range("i24")
    .Range("j6").Value = i.Range("j24")
  End With
    Workbooks("AAA.xls").Close False
    Set j = Nothing
  
  Set k = Workbooks.Open("\\PPP\QQQ\RRR\SSS\TTT\UUU\コード一覧.xls").Worksheets("List")
  Set j = Workbooks("BBB.xls").Worksheets("Data")
   
  With j
  j.Range(j.Cells(1, 1), j.Cells(65536, 2)).Value = _
    k.Range(k.Cells(1, 1), k.Cells(65536, 2)).Value
   
  End With
    Workbooks("コード一覧.xls").Close False
    Set j = Nothing
  

End Sub

【57036】Re:リンクの更新
発言  ハチ  - 08/7/18(金) 11:17 -

引用なし
パスワード
   ▼菜実 さん:

>また、悩んでいるのですが、ファイルを開いたときにこのマクロを実行させたいと
>考えています。
>その場合はどうしたらいいかアドバイスください。

開いたときに実行するイベントがあります。
Private Sub Workbook_Open を調べてみてください。


>コードを書いてみたものの何か自分が勘違いしていないか不安で、
>変なところがないか見ていただけませんか?

>
>Sub 更新()
>
>  Dim i As Worksheet
>  Dim j As Worksheet
>  Dim k As Worksheet

間違ってないですが、通常i,jなどの1文字変数は
ループや一次的な値の保存として使うことが多いです。
別の変数名(たとえばWs1など)にしたほうが良いのでは?
好みによりますけど。

>    Workbooks("AAA.xls").Close False

i.Close False のほうが良いです。
.Closeで数箇所あります。

>  With j
>  j.Range(j.Cells(1, 1), j.Cells(65536, 2)).Value = _
>    k.Range(k.Cells(1, 1), k.Cells(65536, 2)).Value
>   
>  End With

Withが効いてません。
j.Range("A:B").Value = k.Range("A:B").Value としたほうが、
わかりやすいと思います。

【57043】【訂正】Re:リンクの更新
発言  ハチ  - 08/7/18(金) 17:08 -

引用なし
パスワード
   ▼菜実 さん:
>>  Set i = Workbooks.Open(ThisWorkbook.Path & "AAA.xls").Worksheets("設定")

見落としてました。
.Path & "\AAA.xls"

>>    Workbooks("AAA.xls").Close False

>i.Close False のほうが良いです。

→ i.Parent.Close False
ですね・・・失礼しました。

自分だったら・・・

1.データを持ってくる処理が、2種類あるのならSubを2つに分けて、
メインのプロシジャからCallする。

2.開く処理を少しでも早くするのなら、
更新処理の前にファイルの最終更新日を取得して
更新処理が必要か判定してから実行する。

Option Explicit

Sub Test()
  Dim myFile As String
  Dim ChkRng As Range
  Dim buf As Variant

  myFile = ThisWorkbook.Path & "\AAA.xls"
  
  'ファイルの最終更新日を保存して置くセル
  Set ChkRng = ThisWorkbook.Worksheets(1).Range("A1")
  
  buf = func_LastMod(myFile)
  If buf = "" Then
    MsgBox "ファイルが見つかりません"
    Exit Sub
  End If
  
  If ChkRng.Value = buf Then
    MsgBox "更新なし"
  Else
    MsgBox "データ更新開始"
    Call Exec1(myFile)
    ChkRng.Value = buf
  End If
  
  Set ChkRng = Nothing
End Sub

Private Sub Exec1(ByVal myFile As String)
  Dim Ws As Worksheet
  
  Set Ws = Workbooks.Open(myFile).Worksheets(1)
  MsgBox Ws.Parent.Name & "." & Ws.Name & "から処理"
  Ws.Parent.Close False
End Sub

'ファイルの最終更新日を取得するファンクション
'ファイルが存在しない場合は、""を返す
Private Function func_LastMod(ByVal myFile As String) As Variant
  Dim FSO As Object
  
  Set FSO = CreateObject("Scripting.FileSystemObject")
  If Dir(myFile) <> "" Then
    func_LastMod = FSO.GetFile(myFile).DateLastModified
  Else
    func_LastMod = ""
  End If
  
  Set FSO = Nothing
End Function

【57074】Re:【訂正】Re:リンクの更新
お礼  菜実  - 08/7/22(火) 9:39 -

引用なし
パスワード
   ▼ハチ さん:

おはようございます。
コメントありがとうございました。

>▼菜実 さん:
>>>  Set i = Workbooks.Open(ThisWorkbook.Path & "AAA.xls").Worksheets("設定")
>
>見落としてました。
>.Path & "\AAA.xls"
>
>>>    Workbooks("AAA.xls").Close False
>
>>i.Close False のほうが良いです。
>
>→ i.Parent.Close False
>ですね・・・失礼しました。

いえいえ。コメントいただいただけでもありがたくおもっています。
>>i.Close False
↑にしてみて、止まってしまうので、悩みつつ、何が違うのか
また、どうすれば動くのか調べてましたが、スキル不足のためわかりませんでした(涙
いろいろ難しくて、自分はまだまだですね。。。

>自分だったら・・・
>
>1.データを持ってくる処理が、2種類あるのならSubを2つに分けて、
>メインのプロシジャからCallする。
>
>2.開く処理を少しでも早くするのなら、
>更新処理の前にファイルの最終更新日を取得して
>更新処理が必要か判定してから実行する。

この方法、いいなと思いました。
マクロ自体初心者、思いつきもしませんでした。すみません。
書いていただいたコードの意味を調べながら理解できる範囲で理解して
参考にさせていただきたいと思います。

本当に、ありがとうございました。
また、質問させていただくこともあると思います。
そのときは、ご迷惑とお手数おかけするかと思いますが、
よろしくお願いします。

4370 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free