エクセルでファイルが保存されているドライブの値を表示したい

質問

エクセルのマクロを利用して、ファイルが保存されているドライブの値を取りたいのですがうまくいきません。
皆さんのお知恵をお貸しください。
下記のようなマクロを利用して、ネットワーク上の共有ファイルに置いてあるエクセルを自動更新させたいと考えています。
各クライアントには、ネットワークドライブとして共有ファイルが設定されており、クライアントごとに割り振られているドライブ名が異なっています。
試行錯誤で単体では、うまくいくようになりました。

Sub auto_open()
Dim wStr As String
today = Format(Now(), "m月d日")
atai1 = ActiveWorkbook.FullName
atai2 = Left(atai1, 1)
ActiveSheet.Range("c2") = "=SUMPRODUCT(('atai2 & \出欠関係\" & today & "\[1nen.xls]11'!l4:l45=1)*1)"

単体では下記の記述でした。
ActiveSheet.Range("c7") = "=SUMPRODUCT(('F:\出欠関係\" & today & "\[1nen.xls]11'!l4:l45=6)*1)"

回答No.2

hana-hana3 · ベストアンサー

＞ネットワークドライブとして共有ファイルが設定されており、クライアントごとに割り振られているドライブ名が異なっています。

最初からネットワークドライブ名にしておけば解決すると思いますけど・・・。

'\\PC名\出欠関係\" & today & "\[1nen.xls]

＞"=SUMPRODUCT(('atai2 & \出欠関係\" & today & "\[1nen.xls]11'!l4:l45=1)*1)"

では、"=SUMPRODUCT(('atai2 & \出欠関係\" が文字列なので、変数値（atai2）が参照される事はありません。

"=SUMPRODUCT(('" & atai2 & ":\出欠関係\"　とすべきです。

回答No.1

mshr1962

ActiveSheet.Range("c2") = "=SUMPRODUCT(('" & atai2 & "\出欠関係\" & today & "\[1nen.xls]11'!l4:l45=1)*1)"
ではないかと．．．