HOME > Q&A > Excel （VBA）

Excel （VBA）
Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2013)
別エクセルの値読込時の不具合について
投稿日時: 23/05/26 08:18:32
投稿者: やま３

１列目にエクセルシート名があります約４００行
これは指定フォルダーからエクセル名のみを自動で書き込んでいます
 
次にこの１列目のデータを順次読み込で指定フォルダーの同一エクセル名を開かずに必要なセルの値を２列目以降に転記　例えば　CELL(3.15)を２列目にCELL(4,5)を３列目
 
VBAは２列目に "='" & "[" & RNAME & "]原紙'!R3C15" と数式を設定
 注　RNAMEは１列目のエクセル名をこの前に取得しています　原紙はシート名
 
一度開いたエクセルはリンクされ上記数式の設定通りの値が取得されます
リンクなしで値を取得する方法
又はリンクを全て一旦解除してから、再度全てのエクセルデータをリンクする方法をご教示ください
 
 
回答
投稿日時: 23/05/26 11:48:02
投稿者: Suzu

引用:
VBAは２列目に "='" & "[" & RNAME & "]原紙'!R3C15" と数式を設定
 注　RNAMEは１列目のエクセル名をこの前に取得しています　原紙はシート名
 
一度開いたエクセルはリンクされ上記数式の設定通りの値が取得されます
リンクなしで値を取得する方法
又はリンクを全て一旦解除してから、再度全てのエクセルデータをリンクする方法をご教示ください

 
数式として、参照すれば、リンクとなるのは当然の事と思います。
どのタイミングで値を更新する様にしたいか？ にも依りますが
 
簡単なのは、
　VBAの実行時に、同様に セルの数式にリンク先を与え、、セル範囲をコピー、同じ範囲に値貼り付け
でしょう。
投稿日時: 23/05/26 13:04:08
投稿者: やま３

早速の回答ありがとうございます
VBAでリンクを作成する方法とVBA終了時に値だけをコピーしてリンクを解除して終了したいと思いますので、リンク解除のVBAも教示していただければ幸いです

回答
投稿日時: 23/05/26 15:18:43
投稿者: WinArrow

やま３ さんの引用:

ンク解除のVBAも教示していただければ幸いです

 
「値」に変更すれば、リンクは解除でいると思います。
 
Rabge("xxx").Value = Rabge("xxx").Value
回答
投稿日時: 23/05/26 15:20:34
投稿者: Suzu

セルの数式に 外部ファイルへの参照 を含むので リンクになっているのです。
数式に 参照式が含まれない様にすれば、リンクは解除されます。
 
ですから、
1. 数式を入れ、リンクが成立させ、参照値を更新する。
2. 参照値が更新されたら、セルをコピーし、同じセルに 値の貼り付け を行えば 数式は 消える事になるので
　保存時には、リンクが解除される事になります。
 
　A列の 複数行に ファイル名が入っているのであれば
　その全てに対し 参照を成立させた後に、A列の セルをコピーし、値貼り付け を行う様にします。
回答
投稿日時: 23/05/26 15:52:36
投稿者: WinArrow

入力ミス
 
>Rabge("xxx").Value = Rabge("xxx").Value
↓
Range("xxx").Value = Range("xxx").Value
 
手操作では、
コピー
↓
「値」で貼付け

回答
投稿日時: 23/05/26 16:46:06
投稿者: WinArrow

引用:
再度全てのエクセルデータをリンクする方法をご教示ください

 
数式を文字列として、別セルに複写しておけば、再現可能です。
文字列の先頭に「=」があると数式とみなされるので、
例えば、
①別な文字に変換する
②「=」を除く
など、工夫すれば文字列にできます。
 
なお
「=」を付けたまま、文字列にするには、③別セルを表示形式「文字列」にっ設定しておきます。
 
何れにしても、何もせずに「再リンク」はできません。
 
サンプルコード
 
    '①「=」を「◆」に変換
    Range("C3").Value = Replace(Range("B3").Formula, "=", "◆")
    
    '②「=」を除く
    Range("D3").Value = Mid(Range("B3").Formula, 2)
    
    '②「=」を残したまま文字列化する
    Range("E3").NumberFormatLocal = "@"
    Range("E3").Value = Range("B3").Formula

 
 
投稿日時: 23/05/27 07:04:59
投稿者: やま３

別のエクセルを開かないでリンクだけを行うvbaのコードをご教示頂ければ幸いです
１列目にエクセル名があり順次読み込んではリンクを繰り返すコードです
回答
投稿日時: 23/05/27 08:12:46
投稿者: WinArrow

やま３ さんの引用:
別のエクセルを開かないでリンクだけを行うvbaのコードをご教示頂ければ幸いです
１列目にエクセル名があり順次読み込んではリンクを繰り返すコードです

 
>エクセル名
は、理解できませんから
勝手に項目のレイアウトを想定して、１つの数式（リンク情報）をセルに代入するコードを紹介します。
 
Ａ列：フォルダ名
Ｂ列：ファイル名
Ｃ列：シート名
 
Dim FOName As String
Dim FLName As String
Dim STName As String

    FOName = "D:\TEST"
    FLName = "A.xlsx"
    STName = "Sheet1"
    

    Range("F2").Formula = _
        "='" & FOName & "\[" & FLName & "]" & STName & "'!A1"

実体に適応するようアレンジして、ループ処理の中に埋め込めば、よいと思います。

投稿日時: 23/05/28 08:34:57
投稿者: やま３

皆様　本当にありがとうございました
うまくいきました