プロが教えるわが家の防犯対策術!

コードを直していただきたいです。

以下のコードはネットで拾ったものをほんの少しいじった物なのですが、フォルダに格納してあるxlsファイルの指定範囲をファイル毎にコピーして別ファイルに貼り付けるというものです。

このコードで問題なく動くのですが、このままだとEXCELを開いた時のシートをコピーするだけなのでシートを指定したいと思っています。
自分ではどうも上手くいかなかったのでどこに何を記載すれば良いか教えて下さい。
指定するシート名は全て同じです。


Sub OpenCSVfile()
Dim buf As String
Dim Path As String
Dim LastRow As Long

' このファイルがあるフォルダのパスを取得
Path = ThisWorkbook.Path

'xlsファイルだけを取ってくる
buf = Dir(Path & "\" & "*.xls")
'該当するファイルが無くなるまでループ
Do While buf <> ""

'見つけたファイルを開く
Workbooks.Open Path & "\" & buf

'開いたxlsファイルのコピーしたい範囲を選択
Range("A1:F5").Copy

LastRow = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

'貼り付け
If LastRow = 1 And ThisWorkbook.Worksheets(1).Cells(1, 1) = "" Then '最終行が1の時かつセルA1が空欄の時(→シートに何も書かれていない時)は1行目に転記
ThisWorkbook.Worksheets(1).Cells(1, 1).PasteSpecial
Else 'A1セルに既に文字が入っている時は最終行+1行目に転記
ThisWorkbook.Worksheets(1).Cells(LastRow + 1, 1).PasteSpecial
End If

'xlsファイルを閉じる
Workbooks(buf).Close

'次のファイルを取得
buf = Dir()
Loop
End Sub

質問者からの補足コメント

  • それだとxlsmブックだけが対象になりますが、当然ながらマクロを設定してあるブックも対象になります。
    そのブックには指定名のシートが存在しないというだけの話ではないのでしょうか?


    失礼しました。先程のお礼頭がおかしくなっていました。
    マクロを設定してあるブックが対象になっていて、指定のシートが存在しなかったからなんですね
    スッキリしました。

      補足日時:2024/01/11 22:02

A 回答 (7件)

こんばんは



>シートを指定したいと思っています。
>指定するシート名は全て同じです。
固定のシート名でよいものと解釈しました。

 Set wb = Workbooks.Open Path & "\" & buf
 wb.Worksheets("指定シート名").Range("A1:F5").Copy

とすることで、指定シートのA1:F15がコピーされるようになります。

ただし、ブック内に指定名のシートが存在しないと、エラーが発生しますのでご注意ください。
    • good
    • 1
この回答へのお礼

ありがとうございます。
以下置き換える認識であっていますでしょうか?

置き換え前)
'見つけたファイルを開く
      Workbooks.Open Path & "\" & buf
'開いたxlsファイルのコピーしたい範囲を選択
      Range("A1:F5").Copy


置き換え後)
'見つけたファイルを開く
      Set wb = Workbooks.Open Path & "\" & buf
     
'開いたxlsファイルのコピーしたい範囲を選択
      wb.Worksheets("指定シート名").Range("A1:F5").Copy

こちらに変更してみたのですが構文エラーになってしまいました。

お礼日時:2024/01/11 10:42

No6です。



>コード内のxlsの箇所をxlsmに変更したところエラーが出ました。
それだとxlsmブックだけが対象になりますが、当然ながらマクロを設定してあるブックも対象になります。
そのブックには指定名のシートが存在しないというだけの話ではないのでしょうか?
    • good
    • 0
この回答へのお礼

詳細はよくわからなかったのですが、コピーして書き込む用のファイル(マクロを入れているファイル)を開いてしまいその後止まってしまっているようでした。
マクロを仕組んだファイルを別フォルダに格納し、コピーしたいファイルのあるフォルダを指定してコピーするようコードを書き換えたら解決しました。
お手数おかけしました。
都度回答いただき大変助かりました。
ありがとうございます。

お礼日時:2024/01/11 22:00

No3です。



>ちなみに拡張子をxlsmに変更したところ
>インデックスが有効範囲にありません となってしまったのですが
複数のブックが関係している処理で、単に「拡張子をxlsmに変更した」と言われても何をどうしたのかわかりません。
また、「エラーが出た」だけの情報では、どこで発生したのかも不明です。


もしも、「読まれるはずのブック」をxlsmにしたのであれば、ご提示のコードのコメントにもあるように、
 >'xlsファイルだけを取ってくる
という処理になっていますので、対象とは見做されず、そのブックは無視されるはずですから、それが原因でエラーが発生するとは思えません。
(無視されるので、そのブックのデータは反映されませんけれど)

どこでエラーが発生しているのかも不明ですけれど、メッセージ内容から推測すると、雰囲気的にはNo1に示しておいたエラーの可能性のような気がしますけれど・・・
(実態が不明なので、実際のところはわかりかねますが・・)


どうやら、質問者様はマクロをほとんど理解できないようですので、闇雲にいじるのはやめておいた方が宜しそうに思います。
(そのたびにエラーがでても、聞いて回るしかないことになりかねません。)
まずは、拾ったものでも良いので、処理内容をきちんと理解できるようにしましょう。
併せて、デバッグの方法も覚えるとなおよいです。
(仮に、質問するにしても、質問の仕方が上手くなるはずです)
    • good
    • 1
この回答へのお礼

説明不足、また不躾な質問ですみません
fujilinさんに教えて頂いた通りにシート指定できるコードに直した後、xlsmファイルもコピーしたかったためコード内のxlsの箇所をxlsmに変更したところエラーが出ました。
こちらもシートを指定する前のコードではxlsmファイルをコピーできたため、質問すれば解決できると思ってしまいました。

業務で大量にコピペする作業を短期間でやるよう指示され、とりあえず使えれば理解は後で良いとも思ってしまっていました。
もう少し自分で考えてみます。

お礼日時:2024/01/11 19:31

失礼、それは申し訳ないです。



でもさ、それだったら試せるじゃない?

名前の指定も、順番の指定も出来るんだから。

ステップ実行はしてみたの?
Workbooks.Openしたときに
? worksheets(1).name
すれば開いたブックでシートの構成で確認できるんよ。
必要な指定、つまり
Worksheets(なんとか).Activate
でそのシートが全面に出ます。
ステップ実行しながらイミディエイトウィンドウを
使ってほしいんです。
    • good
    • 1

No2です。



まだ多分伝わってないのでもう少し詳しく言います。
私は回答を読んでいただいて分かるとは思っていません。
試してほしいんです。
では何を試すか、そこをちゃんと言ってませんでした。
イミディエイトウィンドウは表示できていますか?

? worksheets(1).name
は試しましたか?
では順にカッコ内の数字を増やしていってください。
そうするとどこかでエラーになりますよね?
これでカッコ内の数字が持つ意味が認識しやすくなりませんか?
コードで読むだけじゃわかりにくいと思うんですけど
値を変化させて、つまり貴方が与えてその結果を見る。

読むだけだと方向が一つだけですけど、双方向になると
限界点がわかるとか、与えたものと結果の関連性とか
理解のしやすさが激変すると思ってるんです。

つまり左からn番目のシート名を表示してるんだってことが
貴方自身理解しやすくなりませんか?

次を行きましょう。
? Worksheets("Sheet1").Index
これはカッコ内で指定したシートが左から何番目かを
見ています。
カッコ内の文字列を実在するシート名に
貴方が変えてみてください。
その名前のシートが左から何番目かに一致しませんか?
実在しなければエラーになることもわかりますよね?

こうやってコードは動いた結果を見るんじゃなくて
貴方がコードを変えてみたときに分かりやすくなるんですよ。

でもそれって実際のコードでやると流れの中の
複雑ないろんなこととごちゃごちゃになってわかりにくいでしょ?
だからイミディエイトウィンドウで知りたいことだけ
ピンポイントで貴方が改変して試すことを繰り返してほしいんです。

特にステップ実行で実行途中の知りたいことを
色々確認してほしいんです。
あなたがイミディエイトウィンドウを使って自分で確認するのが
大事なんです。

あなたは熟読するとおっしゃって頂きましたが
私は熟読じゃなくてイミディエイトウィンドウを使って
貴方自身で色々と試してくれることを期待してるんです。

読んだだけじゃどうにもならないので。
    • good
    • 1
この回答へのお礼

そもそもなのですが、
コード内のWorksheets(1)は書き込むファイルのシートを指しているので、こちらのシート数を弄ってもコピー元のシートは指定できないと思います。

お礼日時:2024/01/11 13:26

No1です。



>こちらに変更してみたのですが構文エラーになってしまいました。
あぁ、ごめんなさい。 うっかりしました。

 Set wb = Workbooks.Open(Path & "\" & buf)
でしたね。
    • good
    • 0
この回答へのお礼

ありがとうございます!
こちらでできました。

ちなみに拡張子をxlsmに変更したところ
インデックスが有効範囲にありません となってしまったのですが。これは書き込むブック自体が同じフォルダにあってxlsmなのでエラーになってしまってるんでしょうか

すみませんがこの場合もどこを修正したら良いか教えていただけますと幸いです。

お礼日時:2024/01/11 13:24

>このままだとEXCELを開いた時のシートをコピーするだけなのでシートを指定したいと思っています。



言葉がとても混乱しています。
EXCELはアプリなので普通開くとは言いません。
起動するとか立ち上げるじゃないかな?
一方開くのはブックっていいます。
VBA使ってるんだから勿論ご存知ですよね。
多分単純にそれを使い間違えただけかなと思うけど・・。

>コードを直していただきたいです。
コードさえ得たら動くという考えを捨ててください。
理由さえちゃんとつかめば正しいコードは
貴方が書けるんです間違いなく。
問題をちゃんと把握する。
その方法の方に注力しないと貴方の学習効率が低下します。
そのことのほうが重大な問題です。
この一件なんて些細なことなんです。

>シートを指定したい
というのがまたまた違うんですよ。
指定はちゃんとしています。(注 ダジャレではありません)

Worksheets(1)
これをちゃんと理解していないだけです。

実行時エラー9 インデックスが有効範囲にありません。
という表示がされるエラーを見たこと無いですか?
見たことがあっても理解は出来ていないんだと思います。

このエラーはコレクションの指定が誤っていることを
示します。
ではコレクションとは何か?
WorkbooksやWorksheetsなどカッコ内に
番号もしくは名前を指定することで
複数の同種オブジェクトから一つを確定する仕組みです。

このケースで言うと(1)と指定してるんです。
1は左から1番目のシートを指します。

開いた時のシートと感じたようですけど
2番めのシートがアクティブになって開いたとしても
必ず左端のシートです。だって指定してるんだから。

シート名を1の代わりに使えば名前で指定が可能です。

最後にどうしても言いたいのはこういった事をちゃんと知る上で
恐らく貴方はイミディエイトウィンドウを使ってないと思うんです。

これを使い倒すことこそが上達の道です。
? Worksheets(1).Name
? Worksheets("Sheet1").Index
? ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
など殆どのコードは実行前に値を確認できることを知っておいてほしいんです。

そしてステップ実行しながら、実行中のコードが持っている
値をチェックしながら進めることで理解が進むんですよ。
一気に走らせて判る方がいたら異常です。
一行ずつの動作をちゃんと見るから判るんだと
認識して学習を進めてください。
    • good
    • 1
この回答へのお礼

ありがとうございます。
お察しの通りだいぶ初心者です。
時間がある時に熟読して勉強させていただきます。

お礼日時:2024/01/11 10:44

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています


このQ&Aを見た人がよく見るQ&A