リンクのVBAを教えてください。

質問

いつも、御指導ありがとうございます。

売掛金元帳のセル（J3）に数式１で、[得意先登録.xls]得意先登録から社名を表示させています。

●数式１　　'=IF($C$2="","",IF(ISERROR(VLOOKUP($C$2,'C:\Documents and Settings\aaa\My Documents\販売管理
\登録　（台帳）\[得意先登録.xls]得意先登録'!$D$7:$E$65536,2,FALSE)),"未登録です",
VLOOKUP($C$2,'C:\Documents and Settings\aaa\My Documents\販売管理
\登録　（台帳）\[得意先登録.xls]得意先登録'!$D$7:$E$65536,2,FALSE)))

売掛金元帳のセル（E2）に数式２で、[自社情報登録.xls]自社情報'!$C$3から自社名を表示させています。

●数式２　　'='C:\Documents and Settings\aaa\My Documents\販売管理\登録　（台帳）\[自社情報登録.xls]自社情報'!$C$3

●数式１及び数式２をVBAで記述したいのですがど、うしても記述出来ません。

●まるなげになってしまいますが、数式１及び数式２と、社名変更があった場合は売掛金元帳へ反映させるVBAを教えてください。

宜しく御願いいたします。

回答No.3

Wendy02

こんにちは。（あけましておめでとうございます。）

>●＞Sub Test1()　数式１の分
>結果　→　登録済コード番号でも全て"未登録です。"と表示されてしまいます。
>If IsError(ret) Thenの(ret)にカーソルを合わせますと”ret=エラー2042 ”と表示されています。

「ret=エラー2042」の意味は、#N/A ですから、たぶん、数式のどこかに間違いがあるのだと思います。全体的に、微妙な数式なのです。ブックを開いていれば簡単なのですが、スペースひとつ違うことができないので、分かち書きしています。

残念ながら、こちらからでは、この数式が成立している限りは、環境が違いますから、その数式を、インターネット経由で直すのは不可能というか、試行錯誤しなければなりません。細かい点を、以下の方法で、もう一度、チェックしていただくしかありません。

簡単な方法は、

Const sPATH As String = "C:\Documents and Settings\aaa\My Documents\販売管理\登録　（台帳）\"
FName = "得意先登録.xls" 'ブック名
ShName = "得意先登録"
sFind = Range("C2").Value
ret = ExecuteExcel4Macro("VLOOKUP(""" & sFind & """,'" & sPATH & "[" & FName & "]" & ShName & "'!R7C4:R65536C5,2,FALSE)")

----------------------------------------------------------
エラーの出る前後で、
Debug.Print "VLOOKUP(""" & sFind & """,'" & sPATH & "[" & FName & "]" & ShName & "'!R7C4:R65536C5,2,FALSE)"

で、イミディエイト・ウィンドウから、確認してください。

これでは読みにくいなら、R1C1 からA1 に変換する方法です。

Const sPATH As String = "C:\Documents and Settings\aaa\My Documents\販売管理\登録　（台帳）\"
FName = "得意先登録.xls" 'ブック名
ShName = "得意先登録"
sFind = Range("C2").Value

fml = "VLOOKUP(""" & sFind & """,'" & sPATH & "[" & FName & "]" & ShName & "'!R7C4:R65536C5,2,FALSE)"
ret = Application.ConvertFormula( _
　　 Formula:=fml, fromReferenceStyle:=xlR1C1, _
　　 ToReferenceStyle:=xlA1, _
　　 toAbsolute:=xlRelative)

Debug.Print ret

------------------------------------------
結果：
VLOOKUP("a",'C:\Documents and Settings\aaa\My Documents\販売管理\登録　（台帳）\[得意先登録.xls]得意先登録'!D7:E65536,2,FALSE)

（これを「=」を付けて、ワークシートのセルに貼り付けて様子を見ればよいです）
-------------------------------------------
●「どのパソコンでも使用できるような記述方法があるのでしょうか。」

これは不要です。
Const sPATH As String =
--------------------------------------------
Dim myFolder As String
Dim UserName As String
Dim sPath As String

FName = "得意先登録.xls" 'ブック名
ShName = "得意先登録"
UserName = Environ("USERNAME")
myFolder = "C:\Documents and Settings\" & UserName & "\My Documents\￥販売管理\2009年11月決算\売掛金元帳\"

これで当てはめればよいと思います。
完全なVBA方式にしても、この場合、環境に左右されるものは、どの道、微妙な部分は逃げられないと思います。

お礼

Wendy02様

あけましておめでとうございます。

ご挨拶申し上げたくて、締め切らずに保管しておりましたご回答を開けましたら、

内容が随分変化しているので驚きました。

今年第一号の質問をさせていただきました。

ご覧いただきご教示いただければ幸甚でございます。

質問番号　6428673

今年も、宜しくお願い致します。

oguno

補足

Wendy02様
●デバッグの方法まで御指導いただきありがとうございます。

●試行錯誤、色々試してみました結果をご報告します。

＞VLOOKUP("a",'C:\Documents and Settings\aaa\My Documents\販売管理\登録　（台帳）\[得意先登録.xls]得意先登録'!D7:E65536,2,FALSE)
（これを「=」を付けて、ワークシートのセルに貼り付けて様子を見ればよいです）

("a",・・・　の　a は得意先コード番号です。　→　100 織田信長工業(株)でテストしました。

１・("a",・・・　　→　("100",・・・では、「ret=エラー2042」#N/A でした。

２．("a",・・・を　→　 (a,・・・　即ち　(100,・・・と修正しましたら、社名、織田信長工業(株)が表示されました。

●＞残念ながら、こちらからでは、この数式が成立している限りは、環境が違いますから、その数式を、インターネット経由で直すのは不可能というか、試行錯誤しなければなりません。

もし、これ以上御指導いただく事が不可能であれば、その旨だけ御連絡いただけないでしょうか、御願申し上げます。

勝手な御願いばかりで、申しわけございません。

oguno

回答No.2

Wendy02

こんばんは。

なかなか、手間が取れないで返事が遅くなりました。
これは、古い手法ですが、一種のデータベース方式です。ブックを開ける必要はありません。ファイルを開いてという方法もありますが、もともと、Excelでは、ブックを開かずとも値を取れる機能があります。

しかし、古い手法ならでは、ExeCuteExcel4Macro関数の中では、R1C1 方式で書かなくてはなりません。なお、パス名に空白があるようですが、気をつけて入れてください。

(1)

Sub Test1()
Dim FName As String
Dim ShName As String
Dim sFind As String
Dim ret As Variant
Const sPATH As String = "C:\Documents and Settings\aaa\My Documents\販売管理\登録　（台帳）\"
FName = "得意先登録.xls" 'ブック名
ShName = "得意先登録"
sFind = Range("C2").Value
ret = ExecuteExcel4Macro("VLOOKUP(""" & sFind & """,'" & sPATH & "[" & FName & "]" & ShName & "'!R7C4:R65536C5,2,FALSE)")
If IsError(ret) Then
　MsgBox "未登録です。", vbInformation
Else
　MsgBox ret
End If
End Sub

(2)
Sub getValue()
Dim FName As String
Dim ShName As String
Dim sFind As String
Dim ret As Variant
Const sPATH As String = "C:\Documents and Settings\aaa\My Documents\販売管理\登録　（台帳）\"
FName = "自社情報登録.xls"
ShName = "自社情報"
ret = ExecuteExcel4Macro("'" & sPATH & "[" & FName & "]" & ShName & "'!R3C3")
If Not IsError(ret) Then
　MsgBox ret
End If
End Sub

お礼

Wendy02様

明けましておめでとうございます。

旧年中は色々と御指導いただきありがとうございました。

ご多忙のところ恐縮くではございますが、本年も御指導の程宜しく御願

い申し上げます。

oguno

補足

Wendy02様

身勝手なお願いにも係わらず、ご多忙のところ御指導賜り感謝申し上げます。

●＞Sub Test1()　数式１の分
結果　→　登録済コード番号でも全て"未登録です。"と表示されてしまいます。

If IsError(ret) Thenの(ret)にカーソルを合わせますと”ret=エラー2042 ”と表示されています。

●＞Sub getValue() 数式2の分
思い通りの処理が出来ました。

●先般、質問番号：4518725で「どのパソコンでも使用できるような記述方法があるのでしょうか。」に対し下記のように御指導いただきましたが、数式１の分も数式2の分も同様な処理が可能でしょうか。

＞それは、こんな方法です。
Dim myFolder As String
Dim UserName As String
UserName = Environ("USERNAME")
myFolder = "C:\Documents and Settings\" & UserName & "\My Documents\￥販売管理\2009年11月決算\売掛金元帳\"

●Const sPATH As String = "C:\Documents and Settings\aaa\My Documents\販売管理\登録　（台帳）\"は、定数の為か上手く出来ません。

回答No.1

imogasi

実例が作りにくいので、文章での要点ですが
（１）Application.WorksheetFunction.をＶＬＯＯＫＵＰの前につける
（２）ＩＦ関数部分はＶＢＡのIf Then Elseで置き換える。
（３）セル範囲はRange("A1:B10")のようなＶＢＡのセル範囲を指定するやり方に置き換えてください。
ーー
はっきり言って、Ｖｌｏｏｋｕｐは列の１つの該当しか検索しないのは覚悟の上だろうから、
range(・・).Find(What:="値")の方がすっきりすると思う。
ーー
他ブックのセルの値は
Workbooks(フルパスブック名.xls).Worksheets(シート名).range("C3")で書き換える必要が有ると思う。
拡張子について参考http://officetanaka.net/excel/vba/file/file03.htm

お礼

imogasi様

明けましておめでとうございます。

御指導いただきましたが、今もって完成できませんが、頑張ってみます。

本年も御指導の程宜しく御願い申し上げます。

oguno