VBAでCSVをエクセル形式で開いたものに、さらにマクロを実行すると上手くいきません

Question

前提・実現したいこと

VBA初心者です。
VBAでCSVをエクセル形式で開いたものに、さらにマクロを実行させたいと思っています。
何卒宜しくお願い申し上げます。

発生している問題・エラーメッセージ

VBAでCSVをエクセル形式で開いたものに、さらにマクロを実行すると上手くいきません。
具体的にはVBAでCSVをエクセル形式で開き、INDEX MATCH関数を用いて、
ほかのエクセルファイルを参照させて、値が自動で入力されるマクロを作ろうとしています。
CSVファイルの読み込みはできるのですが、そのファイルに別のモジュールで
INDEX MATCH関数を実行させると全く反応しません。
※手作業でCSVをエクセルで開いたものに、同様のマクロを実行すると上手くいきます。

該当のソースコード

●CSVファイルをエクセルで開くVBAのコードは以下です。

Public Const targetsheet = "result"
'結果を出力するシート名を指定

Sub CSV取り込みUTF8()

Dim SettingFileName As Variant

Sheets(targetsheet).Cells.Clear
'ファイルクリア

SettingFileName = Application.GetOpenFilename(FileFilter:="CSVファイル(*.csv),*.csv", _
Title:="CSVファイルの選択")
If SettingFileName = False Then
Exit Sub
End If

With Sheets(targetsheet).QueryTables.Add(Connection:="text;" & SettingFileName, Destination:=Sheets(targetsheet).Range("A1"))
.TextFilePlatform = 65001 'UTF-8
.AdjustColumnWidth = False '列の幅を自動計算しない
.TextFileCommaDelimiter = True 'コンマ区切り
.TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat, xlTextFormat) 
.Refresh BackgroundQuery:=False 'シートに出力

.Delete
End With

Sheets(targetsheet).Activate

End Sub

●INDEX MATCH関数を用いて、ほかのエクセルファイルを参照させ、
値が自動で入力されるVBAのコードは以下です。
（ほかにも、値がある場合は1を、ない場合は3を返し、日付形式に変換する関数を
組み込んでいます）

Option Explicit

Sub 検索()
Dim i As Integer
Dim z As Integer
Dim j As Integer
Dim y As Integer
    Dim x As String
    Dim ex As New Excel.Application
    Dim sPath As String
    Dim wb As Workbook
z = Worksheets("result").Cells(Rows.Count, "G").End(xlUp).Row
For i = 2 To z
    '発注番号を取得
    x = Cells(i, 3).Value
    '開くブックを指定
    sPath = "C:\Users\XXXXXXX\Desktop\XXXXXXX.xlsx"
    '読み取り専用で開く
    Set wb = ex.Workbooks.Open(Filename:=sPath, UpdateLinks:=0, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    With ActiveSheet
        '納品予定日の値を表示
On Error Resume Next
        Cells(i, 7).Value = _
            Application.WorksheetFunction.Index(wb.Worksheets("sheet1").Range("I1:AM" & Cells(Rows.Count, "AM").End(xlUp).Row), Application.WorksheetFunction.Match(x, wb.Worksheets("sheet1").Range("AM1:AM" & Cells(Rows.Count, "AM").End(xlUp).Row).Value, 0), 1)
    End With
On Error GoTo 0
Next

y = Worksheets("result").Cells(Rows.Count, "F").End(xlUp).Row
For j = 2 To y
If Cells(j, 7).Value = "" Then
Cells(j, 6).Value = "3"
Else: Cells(j, 6).Value = "1"
End If
Next

    Dim myDate As Date
    Dim v As Integer
    With Worksheets("result")
        v = .Cells(.Rows.Count, "G").End(xlUp).Row
        For j = 2 To v
            Dim strDate As String
            strDate = Format(.Cells(j, 7).Value, " ####/##/##")
            If IsDate(strDate) Then
                .Cells(j, 7).NumberFormat = "yyyy/mm/dd"
                .Cells(j, 7).Value = CDate(strDate)
            End If
        Next
    End With

End Sub

試したこと

こちらで質問をさせていただき、それぞれのVBA自体は動作するものができたようなのですが、
ひとつのエクセルファイル上で2つのモジュールを動作させようとすると上手くいかない現象が
起こっています。

補足情報（FW/ツールのバージョンなど）

Excel for Microsoft 365 を使用しています。

Comments

[jinoji]

とりあえず質問の中にコードを書くときは「コードの挿入」を活用しましょう。

[opsy]

ご教示ありがとうございます。「コードの挿入」を活用させていただきました。
こちらにてご確認くださりましたら幸いです。宜しくお願い申し上げます。

[bebebe_]

関係ない箇所だったので編集で削除しました。

[opsy]

ご教示ありがとうございます。
そもそもVBAを実行してもエラーも何も出ず、何も起こらない状態なのです。
色々試してみたところ、Worksheetを指定すると動かないような気がします。
（Worksheetを指定しない簡単なコードだと動くようです）
調べているのですが、原因がわからず…。
恐れ入りますが、宜しくお願い申し上げます。

[bebebe_]

On Error Resume Next を抜いて実行してもエラーはでませんか？
シート指定に原因がありそうなら Worksheets("sheet1")だとSheet1という名前のシートを指定しますが
シートの名前を変更されていないでしょうか
Worksheets(1)と書けば1番目のシートを指定することもできますが

[opsy]

>On Error Resume Next を抜いて実行してもエラーはでませんか？
エラーは特に出ませんでした。反応なしという感じです。
シート名の変更等はしていません。Worksheets(1)に指定する方法も試してみましたが、
反応なしという感じです。ちなみにシート名が間違っているとエラーは出ます。
何かお心当たりはありませんでしょうか…？すみませんが、宜しくお願い申し上げます。

Answer 1

＜修正＞　こんな感じでどうでしょうか。

Sub 検索()

    Dim sPath As String, fn As String
    Dim wb As Workbook, ws As Worksheet
    Dim rng1 As Range, rng2 As Range
    
    sPath = "C:\Users\XXXXXXX\Desktop\XXXXXXX.xlsx"
    fn = "XXXXXXX.xlsx"
    For Each wb In Workbooks
       If wb.Name = fn Then Exit For
    Next wb
    If wb Is Nothing Then
      Set wb = Workbooks.Open(Filename:=sPath, UpdateLinks:=0, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    End If
    Set ws = wb.Worksheets("sheet1")
    Set rng1 = ws.Range("I1:AM" & ws.Cells(ws.Rows.Count, "AM").End(xlUp).Row)
    Set rng2 = ws.Range("AM1:AM" & ws.Cells(ws.Rows.Count, "AM").End(xlUp).Row)
    
    Debug.Print "納品予定日の参照先:", wb.Name, ws.Name, rng2.Address
    
    Dim res As Worksheet
    Dim z As Long
    
    Set res = Worksheets("result")
    z = res.UsedRange.Rows.Count
    Debug.Print "結果シート:"; res.Name, "最終行:", z
    
    Dim i As Long
    Dim x As String
    Dim wsf As WorksheetFunction
    Set wsf = WorksheetFunction
    
    For i = 2 To z
        '発注番号を取得
        x = res.Cells(i, 3).Value
        '納品予定日の値を表示
        res.Cells(i, 7).Value = wsf.Index(rng1, wsf.Match(x, rng2.Value, 0), 1)
        res.Cells(i, 7).NumberFormat = "yyyy/mm/dd"

        If res.Cells(i, 7).Value = "" Then
            res.Cells(i, 6).Value = "3"
        Else
            res.Cells(i, 6).Value = "1"
        End If
        
        Debug.Print "行番号", i, "発注番号", x, "F列", res.Cells(i, 6).Value, "G列(納品予定日)", res.Cells(i, 7).Value
    Next

    wb.Close False
End Sub

＜修正前＞
起きている事象がよくつかめませんが、エラーが起きるわけではないんですね？
以下のようにデバッグ文を入れて、イミディエイトウィンドウにどう出るか確認してみては？

Option Explicit

Sub 検索()

    Dim res As Worksheet
    Dim z As Long
    Set res = Worksheets("result")
    z = res.Cells(res.Rows.Count, "G").End(xlUp).Row

Debug.Print res.Name, z

    Dim sPath As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng1 As Range, rng2 As Range
    sPath = "C:\Users\XXXXXXX\Desktop\XXXXXXX.xlsx"
    Set wb = Workbooks.Open(Filename:=sPath, UpdateLinks:=0, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    Set ws = wb.Worksheets("sheet1")
    Set rng1 = ws.Range("I1:AM" & ws.Cells(ws.Rows.Count, "AM").End(xlUp).Row)
    Set rng2 = ws.Range("AM1:AM" & ws.Cells(ws.Rows.Count, "AM").End(xlUp).Row)

Debug.Print wb.Name, ws.Name, rng1.Address, rng2.Address
    
    Dim i As Long
    Dim x As String
    For i = 2 To z
        '発注番号を取得
        x = res.Cells(i, 3).Value
            '納品予定日の値を表示
        With Application.WorksheetFunction
            res.Cells(i, 7).Value = .Index(rng1, .Match(x, rng2.Value, 0), 1)
        End With
    
Debug.Print i, x, ws.Name, res.Cells(i, 7).Value
    
    Next

    Dim y As Long
    y = res.Cells(res.Rows.Count, "F").End(xlUp).Row

Debug.Print y
    
    Dim j As Long
    For j = 2 To y
        If res.Cells(j, 7).Value = "" Then
            res.Cells(j, 6).Value = "3"
        Else
            res.Cells(j, 6).Value = "1"
        End If

Debug.Print j, res.Cells(j, 6).Value
        
    Next

    Dim myDate As Date
    Dim v As Long
    With res
        v = .Cells(.Rows.Count, "G").End(xlUp).Row
        
Debug.Print v
        
        For j = 2 To v
            Dim strDate As String
            strDate = Format(.Cells(j, 7).Value, " ####/##/##")
            If IsDate(strDate) Then
                .Cells(j, 7).NumberFormat = "yyyy/mm/dd"
                .Cells(j, 7).Value = CDate(strDate)
            End If
        
Debug.Print j, strDate
        
        Next
    End With


End Sub

Comments

[opsy]

早々のご回答ありがとうございます。
いただいたコードを挿入してみましたが、やはりエクセルに変化は起こりません…。
手作業でCSVをエクセルで開いたものに、同様のマクロを実行すると上手くいくようなのですが…。
CSVをVBAで開いたものに適用すると上手くいかないのはなぜなのでしょうか…。
宜しくお願い申し上げます。

[jinoji]

Application.WorksheetFunction を ex.Application.WorksheetFunction に変えたらどうでしょうか。

というか、Dim ex As New Excel.Application とする理由は何でしょう。
Excelをもう一つ立ち上げる必要はないように思えます。

[opsy]

早々のご回答ありがとうございます。
変えてみましたが、エクセルに変化は起こりません。
今回も手作業でCSVをエクセルで開いたものに、同様のマクロを実行すると上手くいきました。

>Dim ex As New Excel.Application とする理由は何でしょう。
こちらで定義しておかないと「変数が定義されていません」とのエラーが出るので、定義しているのですが…。

すみませんが、宜しくお願い申し上げます。

[jinoji]

「手作業でCSVをExcelで開く」作業を、マクロの記録でコードにしてみて、
CSVファイルをエクセルで開くVBAのコードと見比べてみてはいかがでしょうか。

[opsy]

ご教示ありがとうございます。
見比べてみましたが、全く違うコードでよく分からず…、すみません。
また、毎日異なるCSVをエクセルで開きたいので、特定のCSVに依らないVBAを作成したいです。
色々試してみたところ、Worksheetを指定すると動かないような気がします。
（Worksheetを指定しない簡単なコードだと動くようです）
何かお心当たりはありませんでしょうか。
恐れ入りますが、宜しくお願い申し上げます。

[opsy]

追記ありがとうございます。
エラーが起きているわけではありません。
イミディエイトウィンドウには対応する値が入力されましたので、動いてはいるようです。
ただ実際エクセルへの入力等は動作しないのです、なぜなんでしょう…。
もし何か他にも改善できそうな点等ありましたら、ご教示くださりましたら幸いです。
恐れ入りますが、宜しくお願い申し上げます。

[jinoji]

＞対応する値が入力されましたので、動いてはいるようです。

たとえば
Debug.Print i, x, ws.Name, res.Cells(i, 7).Value
の行ではどのように出力されていますか？

[opsy]

コメントありがとうございます。お返事遅くなりまして申し訳ございません。
すみませんイミディエイトウィンドウの見方について理解不足なこともあり、
的を得ていない回答かもしれませんが、「1」が出力されているようです。
恐れ入りますが、宜しくお願い申し上げます。

[bebebe_]

Debug.Print i, x, ws.Name, res.Cells(i, 7).Valueの出力が1ですか？
1はiの値だと思うのでx ws.name res.Cells(i, 7).Valueの値が入っていないように思えます。
Debug.Printに「wb.name」あたりを追加したら表示されますか？

[opsy]

早々のご回答ありがとうございます。
結果は変わらないようです。出力されるのは、以下です。
私が知識不足なこともあり、とりあえず、動いてはいるのかな…と思ったのですが、
勘違いでしょうか。宜しくお願い申し上げます。

result 1
XXXXXXX.xlsx Sheet1 $I$1:$AM$85 $AM$1:$AM$85
1
1

[jinoji]

最初のデバッグ文の Debug.Print res.Name, z の結果が result 1
z = res.Cells(res.Rows.Count, "G").End(xlUp).Row
つまり、resultシートの一番下の行が1 と判定されています。
resultシートのG列に値が入っていないのでは。

Debug.Print res.UsedRange.Address と入れてみて、
どう出力されるか確かめてみてください。

[jinoji]

zが1 だと、For i = 2 To 1 ということになり、一度も処理されないことになります。
（y や v が 1 でも同じことになります。）

[opsy]

ご回答ありがとうございます。やりとり続きましてすみません。

>resultシートのG列に値が入っていないのでは。

確かに初めからG列に値は入っていません。
"C:\Users\XXXXXXX\Desktop\XXXXXXX.xlsx"のAM列を参照し、resultシートC列の一致する発注番号を取得し、
"C:\Users\XXXXXXX\Desktop\XXXXXXX.xlsx"のI列の納品予定日の値をresultシートG列に
表示させたいと思っています。

>Debug.Print res.UsedRange.Address と入れてみて、
どう出力されるか確かめてみてください。

結果は以下のようになりました。
result 1
$A$1:$AP$49
XXXXXXX.xlsx Sheet1 $I$1:$AM$85 $AM$1:$AM$85
1
1

すみません、ここから何か分かりますでしょうか。
本当に知識不足で申し訳ないのですが、宜しくお願い申し上げます。

[jinoji]

実際は49行目までデータが入っていて、１行目は見出し行になっていて、F,G列は空白で、
処理の中で２行目から49行目までのF,G列の値を設定する感じでしょうか。

z = res.Cells(res.Rows.Count, "G").End(xlUp).Row を
z = res.UsedRange.Rows.Count と変えたら、z の値が49になって、
その後の各処理が動くのではないかと思います。

A列は必ず全行に値があると保証されているのなら、
z = res.Cells(res.Rows.Count, "A").End(xlUp).Row でもいいです。

[opsy]

早々のご回答ありがとうございます。

>実際は49行目までデータが入っていて、１行目は見出し行になっていて、F,G列は空白で、
>処理の中で２行目から49行目までのF,G列の値を設定する感じでしょうか。

仰る通りです。

一回上手くいったと思ったのですが、再度実行したところ

実行時エラー'9':
インデックスが有効範囲にありません

と出てきてしまいました。
あと少しで上手くいきそうなのですが…
申し訳ございません。何かお心当たりはありますでしょうか。
宜しくお願い申し上げます。

[jinoji]

どの行で起きたエラーかわかりますか？ デバッグ文の出力も手掛かりになるかと思います。

[opsy]

すぐにお返事くださり本当にありがとうございます。

Set ws = wb.Worksheets("sheet1")

が黄色でマークされています。
すみませんが、宜しくお願い申し上げます。

[jinoji]

であれば、sheet1という名前のシートがないんだと思われます。

[opsy]

sheet1はあるのですが…

何回か試してみたところ、
"C:\Users\XXXXXXX\Desktop\XXXXXXX.xlsx"
のエクセルを開いた状態で動作させるとエラーが起きるようです。
（このエクセルを閉じた状態であれば理想通り動きます）
このエクセルを開いた状態でも動作させる方法はないものでしょうか。
何度もすみませんが、宜しくお願い申し上げます。

[jinoji]

開いているブックの中に"XXXXXXX.xlsx"が既にあった場合はそれを使い、
なかった場合にのみOpenするように変えてみました。

[opsy]

修正VBAありがとうございます。
実行してみたところ、

実行時エラー'9':
インデックスが有効範囲にありません

とのエラーが出てしまい、

Set res = Worksheets("result")

が黄色くマークされています。
本当に何度もすみませんが、解決法はないでしょうか。
宜しくお願い申し上げます。

[jinoji]

Set res = ThisWorkbook.Worksheets("result")
でどうでしょう。

[opsy]

すみません、修正VBAをもう一度見直させていただき、
コードを色々といじったところ、上手くいきました！
長々とお付き合いいただき、本当にありがとうございました！