Excel VBA 質問スレッド No.741 (解決済)

VBAにExcel関数を挿入しましたが構文エラーとなります。

投稿者 : はなこ     投稿日時 : 2022/05/23(Mon) 18:36:41     OS : 未指定     EXCEL : 未指定
ファイル名称をVBAで取得し、ファイル名の中にある氏名を取得したいです。

■実現したいこと
 例:日報サンプル_松本.xlsx→松本
  氏名は1文字や3文字など可変するので対応したい。
  氏名の前の「日報サンプル_」「.xlsx」は変化しない

■挿入したいExcel関数
=MID(A1,RIGHT(14-6),FIND(".xlsx",A1)-1-FIND("_",A1,1))

■エラー
赤文字になる
構文エラーとでる

解決方法お分かりになりますでしょうか。
宜しくお願いします。

[返信 1] Re : VBAにExcel関数を挿入しましたが構文エラーとなります。
投稿者 : tok     投稿日時 : 2022/05/23(Mon) 19:09:05
とりあえず、255文字以内の数式ならEvaluateしちゃえばなんとかなります。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.application.evaluate
Sub test()
氏名 = [MID(A1,RIGHT(14-6),FIND(".xlsx",A1)-1-FIND("_",A1,1))]
End Sub

[返信 2] Re : VBAにExcel関数を挿入しましたが構文エラーとなります。
投稿者 : はなこ     投稿日時 : 2022/05/23(Mon) 19:32:06
tokさん
ご返信ありがとうございます。
Evaluateの方法は知りませんでした。勉強になります。
ありがとうございます。

追加で質問なのですが対象が複数の場合、

日報サンプル_松本.xlsx
日報サンプル_田中.xlsx
日報サンプル_遠藤.xlsx
日報サンプル_金.xlsx

複数のファイルの場合、for ループで回そうと考えています。
どのように書けばよいのでしょうか。





■[返信 1] tokさん(2022-05-23 19:09:05)の記事
> とりあえず、255文字以内の数式ならEvaluateしちゃえばなんとかなります。
> https://docs.microsoft.com/ja-jp/office/vba/api/excel.application.evaluate
> Sub test()
> 氏名 = [MID(A1,RIGHT(14-6),FIND(".xlsx",A1)-1-FIND("_",A1,1))]
> End Sub

[返信 3] Re : VBAにExcel関数を挿入しましたが構文エラーとなります。
投稿者 : tok     投稿日時 : 2022/05/23(Mon) 23:35:53
ループは基本不要です。
Sub test()
Dim 氏名s()
'範囲固定
氏名s = [MID(A1:A4,RIGHT(14-6),FIND(".xlsx",A1:A4)-1-FIND("_",A1:A4,1))]

'範囲可変
Dim sad As String
Dim s As String
s = "MID(address,RIGHT(14-6),FIND("".xlsx"",address)-1-FIND(""_"",address,1))"
sad = Range("a1", Cells(Rows.Count, "a").End(xlUp)).Address
氏名s = Application.Evaluate(Replace(s, "address", sad))

'ループ
Dim r As Range
Dim 氏名 As String
For Each r In Range("a1", Cells(Rows.Count, "a").End(xlUp))
sad = r.Address
氏名 = Application.Evaluate(Replace(s, "address", sad))
Next
End Sub

[返信 4] Re : VBAにExcel関数を挿入しましたが構文エラーとなります。
投稿者 : はなこ     投稿日時 : 2022/05/26(Thu) 16:06:04
tok様
ご返信ありがとうごじざいます。

変数氏名の中にファイルの中の氏名があることを確認できました。
ありがとうございます。

質問なのですが、、、
Replaceの"address"はどこのことを指しているのでしょうか。
Replaceを調べてみたのですが、なぜこのような処理になっているのか
理解が追い付いておらず可能であれば教えていただきたいです。
Replace(文字列, 検索文字, 置換文字)


もし可能であればB列にその処理結果を書き込むにはどうすればいいでしょうか。
考えてみたのですがわかりませんでした。

[返信 5] Re : VBAにExcel関数を挿入しましたが構文エラーとなります。
投稿者 : tok     投稿日時 : 2022/05/26(Thu) 18:30:58
元の数式
>=MID(A1,RIGHT(14-6),FIND(".xlsx",A1)-1-FIND("_",A1,1))
(ところで、RIGHT(14-6)って無駄じゃ無い?)

"A1"を可変に出来るよう文字列修正し、分り易くしたつもり("A1" ⇒ "address")
> s = "MID(address,RIGHT(14-6),FIND("".xlsx"",address)-1-FIND(""_"",address,1))"

これなら解りますか?
s = "MID(アドレス,RIGHT(14-6),FIND("".xlsx"",アドレス)-1-FIND(""_"",アドレス,1))"
> sad = Range("a1", Cells(Rows.Count, "a").End(xlUp)).Address
氏名s = Application.Evaluate(Replace(s, "アドレス", sad))
文字列 : s
検索文字: "アドレス"
置換文字: sad

>なぜこのような処理になっているのか
Evaluateの引数は文字列である必要があるため、アドレス部を3回生成して書くより、
1回置換の方が分り易いかな?と思ったため(全て裏目のようでした)

>もし可能であればB列にその処理結果を書き込むにはどうすればいいでしょうか。
範囲固定バージョン
Sub test()
Range("b1:b4").Value = [MID(A1:A4,RIGHT(14-6),FIND(".xlsx",A1:A4)-1-FIND("_",A1:A4,1))]
End Sub


範囲可変バージョンはトライ下さい。

[返信 6] Re : VBAにExcel関数を挿入しましたが構文エラーとなります。
投稿者 : はなこ     投稿日時 : 2022/05/27(Fri) 14:06:27
tok様
ご丁寧な回答ありがとうございました。
助かりました。

当掲示板について
返信入力フォーム
お 名 前  :
内  容   :
ステータス  : この質問を解決済みにする

認証コード  :
        キャプチャ画像


( 処理日時 : 2022-07-07 14:17:14 )

Page
Top