Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


2642 / 13645 ツリー ←次へ | 前へ→

【66737】Excelファイルを開く→シートコピー→閉じる ANTON 10/10/3(日) 0:36 質問[未読]
【66740】Re:Excelファイルを開く→シートコピー→閉... よろずや 10/10/3(日) 6:31 回答[未読]
【66752】Re:Excelファイルを開く→シートコピー→閉... ANTON 10/10/3(日) 18:24 発言[未読]
【66753】Re:Excelファイルを開く→シートコピー→閉... よろずや 10/10/3(日) 19:05 回答[未読]
【66756】Re:Excelファイルを開く→シートコピー→閉... ANTON 10/10/4(月) 1:24 お礼[未読]

【66737】Excelファイルを開く→シートコピー→閉...
質問  ANTON  - 10/10/3(日) 0:36 -

引用なし
パスワード
   こんばんわ。
タイトルの事をやろうとしていますが、行き詰っております。

少し量が多いのですが・・・・
もし、ご助力いただけましたら宜しくお願いいたします。

もう少し詳しく、やりたいことを書かせていただきますと

1.ユーザーフォームを使用し、マクロを使用しているExcelファイルとは別のExcelファイルを開く

2.新しく開いたExcelファイルからコピー元となるシートを選択する。

3.シートをコピーし、名前を変更

4.もし、同名シートがあった場合は元々あったシートを削除し、コピーしたシートにその名前を使う。

5.1で開いたExcelファイルのみを閉じる。

6.次のユーザーフォームを開く。

7.次のユーザーフォームでこの作業をやり直すコマンドを作成し、ファイルを開く作業からやり直す。


困ってる点は
*2でシートを選択しようとクリックするとエラーが出る。(Excel2003のみ)

*4の作業が行われない。(Excel2003のみ)

*5の作業が行われない、もしくはエラーがでる(2003.2007両方)

*7の作業を行った際、ファイルを開く作業を終了させたと同時に次で開いたユーザーフォームまで一緒に消えてしまう(Excel2007のみ)

2003と2007両方で行おうとしているため、より難しくなってしまっています・・・。
二つでVBAの内容が若干変わっているのは知っていたのですが。。。まさかここまで違うとは思いませんでした・・・。

下記に私の作ったプログラムを記述します。

Private Sub CommandButton1_Click()
On Error Resume Next

Dim Worksheet As String
Dim MyFileName, MyFileName2 As String
Dim MyWSName, MyWSName2 As String
Dim MyWS As Worksheet

MyFileName = Application.GetOpenFilename _
  (Filefilter:="2003Excelファイル(*.xls), *.xls,2007Excelファイル(*.xlsx), *.xlsx", _
    Title:="Excelファイルの読み込み")

If MyFileName = "False" Then Exit Sub

Workbooks.Open MyFileName

MsgBox "今開いたExcelファイルのシートの中からコピーするシートを選び、アクティブ状態にしてください。" & vbNewLine & _
      "もしよろしければOKを押してください。", vbExclamation
      
MyWSName = ActiveSheet.Name

Worksheets(MyWSName).Copy after:=ThisWorkbook.ActiveSheet

MyWSName2 = "AAA"

For Each MyWS In Worksheets
If MyWSName = MyWSName2 Then
  Application.DisplayAlerts = False
  Worksheets("AAA").Delete
  Applicarion.DisplayAlerts = True
End If
Next

ActiveSheet.Name = MyWSName2

If vbYes = MsgBox("今開いたファイルを閉じますか?", vbQuestion + vbYesNo) Then
Workbooks(MyFileName).Close
End If

Unload Me

'次のユーザーフォーム.Show

UserForm1.Show

End Sub

宜しくお願いいたします。

【66740】Re:Excelファイルを開く→シートコピー→...
回答  よろずや  - 10/10/3(日) 6:31 -

引用なし
パスワード
   ▼ANTON さん:
>Private Sub CommandButton1_Click()
>On Error Resume Next
こんなこと書いてはいけません。
これは、エラーが発生することが分かっていて、
エラー原因によって処理を分岐させたい場合に使うものです。
これが書いてあるせいでデバッグができなくなっています。

>
>Dim Worksheet As String
特別な意味のある単語を勝手に使うと、損をするのは自分自身ですよ。

>Dim MyFileName, MyFileName2 As String
この書き方では、
Dim MyFileName AS Variant, MyFileName2 As String
と書いたのと同じです。

>Dim MyWSName, MyWSName2 As String
>Dim MyWS As Worksheet
>
>MyFileName = Application.GetOpenFilename _
>  (Filefilter:="2003Excelファイル(*.xls), *.xls,2007Excelファイル(*.xlsx), *.xlsx", _
>    Title:="Excelファイルの読み込み")
>
>If MyFileName = "False" Then Exit Sub
実際に Exit Sub が実行されるか試してみましたか?

>
>Workbooks.Open MyFileName
>
>MsgBox "今開いたExcelファイルのシートの中からコピーするシートを選び、アクティブ状態にしてください。" & vbNewLine & _
>      "もしよろしければOKを押してください。", vbExclamation
ユーザーフォームとメッセージボックスを開いた状態で
ワークシートを触らせるなんて無茶です。
メッセージはユーザーフォームのラベルにでも表示して
もう一つのコマンドボタンを押させるとかしましょう。

>      
>MyWSName = ActiveSheet.Name
>
>Worksheets(MyWSName).Copy after:=ThisWorkbook.ActiveSheet
>
>MyWSName2 = "AAA"
>
>For Each MyWS In Worksheets
>If MyWSName = MyWSName2 Then
このときそれぞれの変数に何が入っているか
ステップ実行をして確かめましょう。
ブレークポイントを設定しておくと、そこで一時停止します。
変数の上にマウスポインタを持っていくと
変数に格納された値がポップアップで表示されます。

>  Application.DisplayAlerts = False
>  Worksheets("AAA").Delete
>  Applicarion.DisplayAlerts = True
>End If
>Next
>
>ActiveSheet.Name = MyWSName2
>
>If vbYes = MsgBox("今開いたファイルを閉じますか?", vbQuestion + vbYesNo) Then
>Workbooks(MyFileName).Close
>End If
>
>Unload Me
同じ作業を繰り返すなら閉じたり開きなおしたりする必要は無いでしょう。

>
>'次のユーザーフォーム.Show
>
>UserForm1.Show
>
>End Sub

【66752】Re:Excelファイルを開く→シートコピー→...
発言  ANTON  - 10/10/3(日) 18:24 -

引用なし
パスワード
   丁寧にお答えいただき有り難うございます!

▼よろずや さん:
>▼ANTON さん:
>>Private Sub CommandButton1_Click()
>>On Error Resume Next
>こんなこと書いてはいけません。
>これは、エラーが発生することが分かっていて、
>エラー原因によって処理を分岐させたい場合に使うものです。
>これが書いてあるせいでデバッグができなくなっています。

なるほど、ただ動作に問題ないエラーを飛ばしてくれるだけだと思ってました。

>>Dim Worksheet As String
>特別な意味のある単語を勝手に使うと、損をするのは自分自身ですよ。

たしかにおっしゃるとおりです。。。

>>Dim MyFileName, MyFileName2 As String
>この書き方では、
>Dim MyFileName AS Variant, MyFileName2 As String
>と書いたのと同じです。

これは知りませんでした。
Dim MyFileName As String, MyFileName2 As String
こう記述しなければならないんですね。

>>Dim MyWSName, MyWSName2 As String
>>Dim MyWS As Worksheet
>>
>>MyFileName = Application.GetOpenFilename _
>>  (Filefilter:="2003Excelファイル(*.xls), *.xls,2007Excelファイル(*.xlsx), *.xlsx", _
>>    Title:="Excelファイルの読み込み")
>>
>>If MyFileName = "False" Then Exit Sub
>実際に Exit Sub が実行されるか試してみましたか?

試してはいません・・・・。試そうと思いましたが試し方がわかりませんでした。。。

>>Workbooks.Open MyFileName
>>
>>MsgBox "今開いたExcelファイルのシートの中からコピーするシートを選び、アクティブ状態にしてください。" & vbNewLine & _
>>      "もしよろしければOKを押してください。", vbExclamation
>ユーザーフォームとメッセージボックスを開いた状態で
>ワークシートを触らせるなんて無茶です。
>メッセージはユーザーフォームのラベルにでも表示して
>もう一つのコマンドボタンを押させるとかしましょう。

2007ではできたので、できるものだと思っていました。
別のユーザーフォームを起動するマクロを作りたいと思います。
      
>>MyWSName = ActiveSheet.Name
>>
>>Worksheets(MyWSName).Copy after:=ThisWorkbook.ActiveSheet
>>
>>MyWSName2 = "AAA"
>>
>>For Each MyWS In Worksheets
>>If MyWSName = MyWSName2 Then
>このときそれぞれの変数に何が入っているか
>ステップ実行をして確かめましょう。
>ブレークポイントを設定しておくと、そこで一時停止します。
>変数の上にマウスポインタを持っていくと
>変数に格納された値がポップアップで表示されます。

ブレークポイントの存在は知っていましたが活用法がわからずにいました。。。
ブレークした時に変数に入っている値が見れるという利点があったのですね!
今までメッセージボックスを使用して変数の確認を行っていました・・・。

>>  Application.DisplayAlerts = False
>>  Worksheets("AAA").Delete
>>  Applicarion.DisplayAlerts = True
>>End If
>>Next
>>
>>ActiveSheet.Name = MyWSName2
>>
>>If vbYes = MsgBox("今開いたファイルを閉じますか?", vbQuestion + vbYesNo) Then
>>Workbooks(MyFileName).Close
>>End If
>>
>>Unload Me
>同じ作業を繰り返すなら閉じたり開きなおしたりする必要は無いでしょう。

同じ作業を繰り返すといいますか、ユーザーが開くファイルを間違えたりシート選択を間違えた際にもう一度読み込めるようにしたいのです。
次のユーザーフォームでこのユーザーフォームを開くコマンドボオタンを押さない限りは繰り返し作業は行いません。

>>
>>'次のユーザーフォーム.Show
>>
>>UserForm1.Show
>>
>>End Sub


その後、よろずやさんのコメントを参考にデバック等を繰り返し、
*同名シートが存在した場合、シートを削除しコピーしたシートに削除した名前をつける。
*アクティブシートの選択時のエラー発生
は解決することができました!

今一番の問題となっているのは最初に開いたExcelを閉じる作業です。。。
未だにExcelを閉じることが出来ません。

マクロを実行しているファイルもろとも開いているExcelを全て閉じる事はできたのですが、指定したExcelファイルのみを閉じるということができません・・・。

エラーは「インデックスに有効範囲がありません」というのがでます。

ようはExcelを開く際に使用した変数を閉じるでも使用しているため、変数内に入っている値が「C\***\***\***\〜〜〜.xls」となっており、閉じる場合は「〜〜〜」のみを指定してやらなければならないからエラーがでているのではと思うのですが、どうすればいいのかがわかりません・・・。

もし、なにかいい方法がありましたらお教え願えると幸いです。
宜しくお願いいたします。

【66753】Re:Excelファイルを開く→シートコピー→...
回答  よろずや  - 10/10/3(日) 19:05 -

引用なし
パスワード
   >今一番の問題となっているのは最初に開いたExcelを閉じる作業です。。。
>未だにExcelを閉じることが出来ません。

Dim Wb As Workbook
  :
Set Wb = Workbooks.Open(MyFileName)
  :
Wb.Close

【66756】Re:Excelファイルを開く→シートコピー→...
お礼  ANTON  - 10/10/4(月) 1:24 -

引用なし
パスワード
   >よろずやさん
おぉ、、、ずっと悩んでた時間が嘘のように簡単にできてしまいました。。。
Setとという文字は知りませんでした。。。

大変助かりました!!ありがとうございます!

2642 / 13645 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free