[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『マクロを使用して名前が固定でないエクセルからVLOOCKしたい』（ぺんぎん）
いつもお世話になっております。
業務からデータをダウンロードすると
受発注履歴画面202203041722.xlsxの様な名称になっています。
データをダウンロードすると日付と時間などが自動で付きます。
Sub 行数貼付()の方でファイルを開く事はできるのですが
開いたファイルからVLOOCKしようとするとエラーになります。
どのような数式にすればVLOOCK出来る様になるのでしょうか？
下記が作成した(教えて頂いた）マクロになります
Sub APGM行数()
行数貼付
行数マクロ
End Sub
Sub 行数貼付()
Application.ScreenUpdating = False
Dim desktop As String
Dim file As String
Dim wb As Workbook
Dim ws As Worksheet
Dim lastRow As Long
desktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" 'デスクトップのフォルダ+"\"
file = Dir(desktop & "受発注履歴画面*.xlsx")
Set wb = Workbooks.Open(desktop & file) '開く
Set ws = wb.Sheets("Sheet2") 'コピー元シート
Windows("2021年度受注入力集計表.xlsm").Activate
End Sub
Sub 行数マクロ()
Dim c As Long
c = ActiveCell.Column
With Cells(5, c).Resize(58, 1)
.Formula = "=IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B5,$B4),[受発注履歴画面.xlsx]Sheet2!$A:$D,IF(MOD(ROW(),2)=1,3,4),FALSE),0)"
.Value = .Value
End With
With Cells(65, c).Resize(2, 1)
.Formula = "=IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B65,$B64),[受発注履歴画面.xlsx]Sheet2!$A:$D,IF(MOD(ROW(),2)=1,3,4),FALSE),0)"
.Value = .Value
End With
End Sub
以上よろしくお願いします。
＜ 使用 Excel：unknown、使用 OS：unknown ＞
「発注履歴画面.xlsx」のところに、正しいファイル名（「受発注履歴画面202203041722.xlsx」のように）を
指定する必要があります。
（わからん） 2022/03/04(金) 17:07
これは、名称は固定以外はできないのでしょうか？
desktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" 'デスクトップのフォルダ+"\"
file = Dir(desktop & "受発注履歴画面*.xlsx")
の様にすることはできない もの でしょうか？
（ぺんぎん） 2022/03/04(金) 17:24
できません。
とはいえ、「名称は固定以外はできない」のとは違います。
file = "受発注履歴画面202203041722.xlsx" 〜 中略 〜 .Formula = "=IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B5,$B4),[" & file & "]Sheet2!$A:$D,IF(MOD(ROW(),2)=1,3,4),FALSE),0)"
↑のようにすることができます。
（わからん） 2022/03/04(金) 17:47
.Formula = "=IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B5,$B4),[受発注履歴画面*.xlsx]Sheet2!$A:$D,IF(MOD(ROW(),2)=1,3,4),FALSE),0)"
↑みたいには使えないのであって、
file = Dir(desktop & "受発注履歴画面*.xlsx") .Formula = "=IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B5,$B4),[" & file & "]Sheet2!$A:$D,IF(MOD(ROW(),2)=1,3,4),FALSE),0)"
↑のようにできます。
（わからん） 2022/03/04(金) 21:54
Sub 行数マクロ()
Dim c As Long
c = ActiveCell.Column
With Cells(5, c).Resize(58, 1)
file = Dir(desktop & "受発注履歴画面*.xlsx")
.Formula = "=IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B5,$B4),[" & file & "]Sheet2!$A:$D,IF(MOD(ROW(),2)=1,3,4),FALSE),0)"(),2)=1,3,4),FALSE),0)"
.Value = .Value
End With
With Cells(65, c).Resize(2, 1)
.Formula = "=IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B65,$B64),[" & file & "]Sheet2!$A:$D,IF(MOD(ROW(),2)=1,3,4),FALSE),0)"
.Value = .Value
End With
End Sub
でもこれだとエラーになってしまいます。
どのように使用するするのでしょうか？
（ぺんぎん） 2022/03/07(月) 15:32
いくつか疑問に思ったことをメモしておきます。
(1) file = Dir(desktop & "受発注履歴画面*.xlsx") は、ご自分の想定しているExcelファイルになるんですか？ 一定の順序でDir関数が取り出してくれる「受発注履歴画面」で始まる Excelブックを、そのままハイハイと使っていていいんですか？
自分がこうと決めたファイルを指定するのであれば、 GetOpenFilenameなどを使って指定するのが普通です。
(2) 無造作に ActiveCell.Columnに依存して処理していますが、大丈夫ですか？
ユーザーが自分だけであればよいかもしれませんが。 ActiveCellなんてえものは、極めてヤクザなものじゃないですか？ 保存したときにたまたまカーソルがあったところ、ということですから。
・処理日から割り出した日付のようなものを使うとか、 ・ユーザーに明示的に指定されるとか、 そういった手法が確実だと思います。
(3) 最初の式（二つを一つにしたという）では、 B列に依存して結果が変わるのですが、 B列はどのようにコントロールされているのか気になりました。
式か何かで変化するようになっているんですか？ 入力したものであれば、どこかに値を残さなくていいんですか？ トレーサビリティ（あとから見て処理の確かさが確保されていること。 再現性があること）などの観点で、問題はないのですか？
以上
（γ） 2022/03/07(月) 17:24
「*」は、Dir関数では使えるけど、Formulaの設定値には
使えないといいたかったのです。
こんな感じで使います。
Sub APGM行数()
Application.ScreenUpdating = False
Dim desktop As String
Dim file As String
Dim wb As Workbook
Dim ws As Worksheet
Dim lastRow As Long
desktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" 'デスクトップのフォルダ+"\"
file = Dir(desktop & "受発注履歴画面*.xlsx")
Set wb = Workbooks.Open(desktop & file) '開く
Set ws = wb.Sheets("Sheet2") 'コピー元シート
Windows("2021年度受注入力集計表.xlsm").Activate
Dim c As Long
c = ActiveCell.Column
With Cells(5, c).Resize(58, 1)
.Formula = "=IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B5,$B4),[" & file & "]Sheet2!$A:$D,IF(MOD(ROW(),2)=1,3,4),FALSE),0)"
.Value = .Value
End With
With Cells(65, c).Resize(2, 1)
.Formula = "=IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B65,$B64),[" & file & "]Sheet2!$A:$D,IF(MOD(ROW(),2)=1,3,4),FALSE),0)"
.Value = .Value
End With
End Sub
（わからん） 2022/03/07(月) 18:36
適用するセル範囲が変わっているので何とも言えませんが、前トピックでいうとE〜AI列の31列だったので、1か月分だとおもったのですが今回は違うのでしょうか？
もしも、同じように１か月分の日付が並んでいることが前提でシート上に年月日を特定するデータがあるということであれば↓のようにすればファイル名を特定することができるとおもいます。
「集計表」シート
__E__ __F__ __G__ __H__
4 3/1 3/2 3/3 3/4
5
Sub ファイル特定()
Dim フォルダ As String
Dim ファイル名 As String
Dim MyDate As Date
Dim 年月日部分 As String
Dim 時刻 As Date
フォルダ = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" 'デスクトップのフォルダ+"\"
MyDate = ThisWorkbook.Worksheets("集計表").Range("H4").Value
年月日部分 = Format(MyDate, "yyyymmdd")
If Dir(フォルダ & "受発注履歴画面" & 年月日部分 & "*.xlsx") = "" Then
MsgBox MyDate & vbLf & "上記のデータは存在しません"
Else
ファイル名 = Dir(フォルダ & "受発注履歴画面" & 年月日部分 & "*.xlsx")
Do Until ファイル名 = ""
時刻 = WorksheetFunction.Max(時刻, CDate(Format(Mid(ファイル名, 16, 4), "@@:@@")))
ファイル名 = Dir()
Loop
MsgBox MyDate & vbLf & "上記の最新ファイルは以下のとおりです" & vbLf & vbLf & _
"受発注履歴画面" & 年月日部分 & Format(時刻, "hhmm") & ".xls" & vbLf & vbLf & _
"フルパスは以下のとおりです" & vbLf & フォルダ & "受発注履歴画面" & 年月日部分 & Format(時刻, "hhmm") & "xlsx"
End If
End Sub
■２
VBAの世界では基本的に、ブックやシート（オブジェクトといいます）を明示すれば、いちいちアクティブにしたり選択したりする必要はありません。
また、標準モジュールでシートの指定を省略した場合にはActiveSheetを指定したものとして扱われます。
よって、複数のブックやシートを相手にするなら、きちんとオブジェクトを指定したほうがよいとおもいます。
■３
ということを踏まえると以下のようなアプローチもあるとおもいます。
興味があれば【ステップ実行】して研究してみてください。
Sub 研究用()
Dim フォルダ As String, ファイル名 As String, 年月日部分 As String, フルパス As String
Dim 時刻 As Date
Dim 列番号 As Long
Dim MyRNG As Range
Stop 'ブレークポイントの代わり
フォルダ = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" 'デスクトップのフォルダ+"\"
列番号 = ActiveCell.Column
With ThisWorkbook.Worksheets("集計表")
年月日部分 = Format(.Cells(4, 列番号).Value, "yyyymmdd")
ファイル名 = Dir(フォルダ & "受発注履歴画面" & 年月日部分 & "*.xlsx")
If ファイル名 = "" Then
Exit Sub
Else
Do Until ファイル名 = ""
時刻 = WorksheetFunction.Max(時刻, CDate(Format(Mid(ファイル名, 16, 4), "@@:@@")))
ファイル名 = Dir()
Loop
フルパス = フォルダ & "受発注履歴画面" & 年月日部分 & Format(時刻, "hhmm") & ".xlsx"
End If
Set MyRNG = Workbooks.Open(フルパス).Worksheets("Sheet2").Range("A:D")
.Cells(5, 列番号).Resize(58).Formula = "=IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B5,$B4)," & MyRNG.Address(External:=True) & ",IF(MOD(ROW(),2)=1,3,4),FALSE),0)"
.Cells(65, 列番号).Resize(2).Formula = "=IFERROR(VLOOKUP(IF(MOD(ROW(),2)=1,$B65,$B64)," & MyRNG.Address(External:=True) & ",IF(MOD(ROW(),2)=1,3,4),FALSE),0)"
.Parent.Activate
End With
End Sub
（もこな２） 2022/03/08(火) 07:09
これを使うユーザーは私でない１人だけです。
しかし、作業が面倒なので簡単にできればと思い依頼しました。
Ｂ列にキーになる項目がある為、Ｂ列を指定してますが、指定しないでもVLOOCKなどが出来るのであれば教えて下さい。
今後の参考にさせて頂きます。
（ぺんぎん） 2022/03/09(水) 15:24
特に質問が無かったのでご承知のこととは思いますが一応提示しておきます。
【ステップ実行】 https://www.239-programing.com/excel-vba/basic/basic023.html http://plus1excel.web.fc2.com/learning/l301/t405.html
【ブレークポイント】 https://www.239-programing.com/excel-vba/basic/basic022.html https://www.tipsfound.com/vba/01010
【イミディエイトウィンドウ】 https://www.239-programing.com/excel-vba/basic/basic024.html https://excel-ubara.com/excelvba1/EXCELVBA486.html
【ローカルウィンドウ】 https://excel-ubara.com/excelvba4/EXCEL266.html http://excelvba.pc-users.net/fol8/8_2.html
（もこな２） 2022/03/09(水) 16:11
B列の扱いについては誤解されています。
別にB列を使うことに異存はないのですが、
後から検証可能かどうかが気になっただけです。
それもこちらの取り越し苦労であれば、無視してもらって結構です。
Activecellに依存してしまうのはやはり気になりますね。
しかし、これ以上はおせっかいになるでしょうから、これで終了とします。
（γ） 2022/03/10(木) 17:18
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.