VBA　シートがあるはずなのに実行エラー９インデックスが有効範囲にありませんのエラーが出る

if文による条件一致で読みにいくシートを分けるために
下記のコードを作成したのですが、「実行エラー９インデックスが有効範囲にありません」とのエラーが出ます。

VBA
1Dim ws As Worksheet
2         Set ws = Worksheets("レポート")
3           If ws = "レポート" Then
4          
5              Worksheets("レポート").ExportAsFixedFormat _
6                Type:=xlTypePDF, _
7                fileName:=ExcelFilePath & ExFileName, _
8                OpenAfterPublish:=True
9          
10          Else
11              'Worksheets(Array("表紙", "検索", "表示箇所", "行動", "順位")).Select
12              'ActiveSheet.ExportAsFixedFormat _
13               'Type:=xlTypePDF, _
14              'fileName:=ExcelFilePath & ExFileName, _
15               'OpenAfterPublish:=True
16          
17          End if

とりあえずelse文以外は正常に動作するのではと思ったのですが
エクセルにレポートのシートはあるのに何故かエラーが出てしまい困惑しております。
こういった際のデバッグ方法等が不明瞭なので何かアドバイスかヒント頂けると幸いです。
宜しくお願い致します。

参考にした記事：　https://www.tipsfound.com/vba/19009

OS：macOS Catalina バージョン10.15.3
Excelバージョン： Microsoft Excel for Mac バージョン16.39

行動規範の内容に同意します

回答2件

ベストアンサー

お望みのことは、
"レポート"という名前のレポートが存在したら、そのレポートをPDF出力、
存在しなければ、
"表紙", "検索", "表示箇所", "行動", "順位"シートをまとめてPDF出力ということですね。

vba
1    Dim ws As Worksheet
2    
3    On Error Resume Next
4    Set ws = Worksheets("レポート")
5    Select Case Err.Number
6    Case 0
7         On Error GoTo 0
8         Worksheets("レポート").ExportAsFixedFormat _
9             Type:=xlTypePDF, _
10             Filename:=ExcelFilePath & ExFileName, _
11             OpenAfterPublish:=True
12
13    Case 9
14        On Error GoTo 0
15        Worksheets(Array("表紙", "検索", "表示箇所", "行動", "順位")).Select
16        ActiveSheet.ExportAsFixedFormat _
17            Type:=xlTypePDF, _
18            Filename:=ExcelFilePath & ExFileName, _
19            OpenAfterPublish:=True
20    Case Else
21        MsgBox "エラー:" & Err.Number & " " & Err.Description
22        On Error GoTo 0
23    End Select

よく見たら、上記のコード、なんか遠回りのことをしてますね。

vba
1    Dim ws As Worksheet
2    
3    On Error Resume Next 'エラー無視
4    Set ws = Worksheets("レポート")
5    On Error GoTo 0 'エラー無視解除
6    If ws Is Nothing Then '"レポート"シートが存在しなければ、
7        Worksheets(Array("表紙", "検索", "表示箇所", "行動", "順位")).Select
8        ActiveSheet.ExportAsFixedFormat _
9            Type:=xlTypePDF, _
10            Filename:=ExcelFilePath & ExFileName, _
11            OpenAfterPublish:=True
12    Else
13        ws.ExportAsFixedFormat _
14             Type:=xlTypePDF, _
15             Filename:=ExcelFilePath & ExFileName, _
16             OpenAfterPublish:=True
17    End If

投稿2020/07/30 10:52

編集2020/07/30 11:28

hatena19

総合スコア34075

taka-hoop

2020/07/30 12:12 編集

ご回答ありがとうございます！まさにおっしゃる通りです。一応下記のように丸々コードコピーして使わせてもらったのですがやはり同じインデックスが有効範囲にないとのエラーが出てしまいます。下記作成したマクロの全貌です。 ######### Sub EXCELファイルPDF化03() 'フォルダのEXCELファイルの一括変換 Dim Button, T, I, L As Integer Dim OpenExcelFileName, ExcelFileName, ExcelFilePath, ExFileName As String Application.DisplayAlerts = False '確認メッセージを無効化します。 Button = MsgBox("EXCEファイルの一括PDF変を行いますか？", vbYesNo + vbQuestion, "確認") If Button = vbYes Then OpenExcelFileName = Application.GetOpenFilename 'ダイアログを表示取り込むフォルダーにあるファイルを選択します。 If OpenExcelFileName <> "False" Then ExcelFileName = Dir(OpenExcelFileName) '指定したファイルパスからファイル名を代入します。 ExcelFilePath = Replace(OpenExcelFileName, ExcelFileName, "") '指定したファイルパスを指定します。（ファイルパスからファイル名を取り除く） MsgBox ExcelFilePath & "この選択フォルダからPDFに変換します。" Else MsgBox "キャンセルされました" Exit Sub 'キャンセルでプログラムを終了します。 End If ExFileName = Dir(ExcelFilePath & "*.xls?") '指定したフォルダーから一件目のEXCELファイルを指定します。 Do While ExFileName <> "" '読み込むファイルがなくなるまで繰り返す。 'ActiveWorkbook の記述はいらなかった 'レポートの記述にいらないスペースがあったかも Dim ws As Worksheet On Error Resume Next 'エラー無視 Set ws = Worksheets("レポート") On Error GoTo 0 'エラー無視解除 If ws Is Nothing Then '"レポート"シートが存在しなければ、 Worksheets(Array("表紙", "検索", "表示箇所", "行動", "順位")).Select ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ fileName:=ExcelFilePath & ExFileName, _ OpenAfterPublish:=True Else ws.ExportAsFixedFormat _ Type:=xlTypePDF, _ fileName:=ExcelFilePath & ExFileName, _ OpenAfterPublish:=True End If ActiveWindow.Close '読み込んだファイルを閉じます。 ExFileName = Dir() '次のファイルを指定する。 Loop MsgBox "PDFファイルに一括変換しました。" Else MsgBox "処理を中断します" End If Application.DisplayAlerts = True '確認メッセージを有効化します。 End Sub

hatena19

2020/07/30 12:32

Dir関数で指定したフォルダー内のエクセルファイルのファイル名を取得はしてますが、そのファイルを開いてませんね。 Worksheets("レポート") だと、現在アクティブなブックのシートを参照することになりますが、それには"レポート"はないのでは。 Dir関数で取得したファイル名のブックに"レポート"シートがありそれをPDF出力するのなら、そのブックを開かないとだめでしょう。【エクセルVBA】ワークブックを開くWorkbooks.Openの使い方とエラーの対処法 https://www.excelspeedup.com/workbookopen/

taka-hoop

2020/07/30 13:13 編集

ご返答ありがとうございます！一応下記コードであれば分岐などはできないですがレポートのあるファイルは自動でPDF出力ができるのですが、Dir関数で取得したファイル名がおかしいという事でしょうか？非常に詳しく説明して頂いてるのに理解が及んでおらず大変申し訳ないのですが宜しくお願い致します。以下レポートだけPDF出力できるコードです ############################### Sub EXCELファイルPDF化03() 'フォルダのEXCELファイルの一括変換 Dim Button, T, I, L As Integer Dim OpenExcelFileName, ExcelFileName, ExcelFilePath, ExFileName As String Application.DisplayAlerts = False '確認メッセージを無効化します。 Button = MsgBox("EXCEファイルの一括PDF変を行いますか？", vbYesNo + vbQuestion, "確認") If Button = vbYes Then OpenExcelFileName = Application.GetOpenFilename 'ダイアログを表示取り込むフォルダーにあるファイルを選択します。 If OpenExcelFileName <> "False" Then ExcelFileName = Dir(OpenExcelFileName) '指定したファイルパスからファイル名を代入します。 ExcelFilePath = Replace(OpenExcelFileName, ExcelFileName, "") '指定したファイルパスを指定します。（ファイルパスからファイル名を取り除く） MsgBox ExcelFilePath & "この選択フォルダからPDFに変換します。" Else MsgBox "キャンセルされました" Exit Sub 'キャンセルでプログラムを終了します。 End If ExFileName = Dir(ExcelFilePath & "*.xls?") '指定したフォルダーから一件目のEXCELファイルを指定します。 Do While ExFileName <> "" '読み込むファイルがなくなるまで繰り返す。 'ActiveWorkbook の記述はいらなかった 'レポートの記述にいらないスペースがあったかも Workbooks.Open fileName:=ExcelFilePath & ExFileName, ReadOnly:=True, UpdateLinks:=0 'EXCELファイルを読み取り専用で読み込む ExFileName = Left(ExFileName, InStr(ExFileName, ".") - 1) ' ファイル名から拡張子を取り除く（.xls?） Worksheets("レポート").ExportAsFixedFormat _ Type:=xlTypePDF, _ fileName:=ExcelFilePath & ExFileName, _ OpenAfterPublish:=True ActiveWindow.Close '読み込んだファイルを閉じます。 ExFileName = Dir() '次のファイルを指定する。 Loop MsgBox "PDFファイルに一括変換しました。" Else MsgBox "処理を中断します" End If Application.DisplayAlerts = True '確認メッセージを有効化します。 End Sub

hatena19

2020/07/30 13:26 編集

今度のコードは Workbooks.Open でブックを開いてますね。まず、ブックを開く、そのブックに"レポート"シートが存在するかチェック、それからPDF出力、という処理にしないとダメですよ。ブックを開かずにシートチェックすれば当然エラーになります。よく、考えて処理を組んでください。それでもうまくいかないなら、この質問の本筋からずれてきますので、もう一度新規に質問を立て直した方がいいと思います。このコメントスペースで続けるのはつらいです。

taka-hoop

2020/07/30 14:15

ご返答ありがとうございます！ご丁寧に教えて頂き本当にありがとうございます！ hatena19さんに頂いたコードと解説のおかげでかなり道が開けたように感じました！ブックを開いてシートチェックをちょっと意識して考えてみます記述して頂いたコードをもっとじっくり読み解き処理を考えてみます！それでもまた詰まるようならまた質問させて頂きたいと思います！本当にご丁寧にありがとうございました！

行動規範の内容に同意します

全部検証していませんが、この部分がすでにおかしいです・

If ws = "レポート" Then

→正
if ws.name = "レポート" then

VBA
1sub test()
2
3dim i as doubule
4dim str as string
5
6For i = 1 To Worksheets.Count
7
8  str = Sheets(i).Name
9　　　
10　　if str="レポート" then
11      処理
12    else
13　　　　処理
14    end　if
15  
16next
17
18end sub
19

投稿2020/07/30 10:24

編集2020/07/30 10:32

mako1972

総合スコア383

taka-hoop

2020/07/30 10:30

ご回答ありがとうございます！ご指摘頂いた通りに修正を試みたんですが、同じエラーが出てしまいます。せっかく教えて頂いてたのにタイポ申し訳ありません！

mako1972

2020/07/30 10:37

なるほど。この場合、VBAのサンプルで提示したように、setにこだわらずしたほうがいいですね。

mako1972

2020/07/30 11:00 編集

一旦取り消しますね。よく考えると他のレスの継続案件ですね。確か、PDFを１ページ目に作るということ。今回の質問は１ページとそれ以外で PDFを作りたいという質問ですか？？最強VBA１位のhatena19さんが回答されていますので、そちらにのっかるほうが良いと思います。

taka-hoop

2020/07/30 11:57

ご返答ありがとうございます！やりたい事としてはエクセルを開いてDBと言うシート以外をPDFで保存を繰り返すと言った感じでレポートとDBがセットのエクセルファイルとDBと"表紙", "検索", "表示箇所", "行動", "順位"がセットのエクセルファイルがあるフォルダを見に行って自動でPDF出力したい言う感じでございます！

mako1972

2020/07/30 11:59

ちょい。 VBAランク１位のすごい人が回答してんのよ＾＾。

taka-hoop

2020/07/30 12:02

ご返答ありがとうございます！ご指摘ありがとうございます！お返事させて頂きました！

行動規範の内容に同意します

あなたの回答