Excel VBA質問箱 IV

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

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


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

【38910】SELLの指定法について わいわい 06/6/14(水) 10:28 質問[未読]
【38911】Re:SELLの指定法について ハチ 06/6/14(水) 10:55 発言[未読]
【38916】Re:SELLの指定法について ハト 06/6/14(水) 12:00 発言[未読]
【38920】Re:SELLの指定法について わいわい 06/6/14(水) 13:06 お礼[未読]

【38910】SELLの指定法について
質問  わいわい  - 06/6/14(水) 10:28 -

引用なし
パスワード
   いつもお世話になります。
ファイルの指定法について教えてください。
UserForm3の規則(textbox内の文字で読取列を指定)に従い、Private Sub CommandButton3_Click()で開いた外部ファイル(シートはComboBox1で選択)から、CommandButton2_Click()にてデータを取込もうとしています。
以下のマクロで動いてはいるのですが、2つのファイルを操作しているので元ファイル(マクロのある)を操作するとき
>Workbooks("Mk4.XLS").Worksheets("条件表").Range("A3:A65536"))
のように指定しています。これですと将来ファイル名を変更したとき問題となるので良い方法を教えてください。Me.??で出来るのでしょうか?

___________________________________________________________
Private Sub CommandButton2_Click()
Dim DATA(2, 40)

'読みとり列番号の取得
For i = 2 To 35
  DATA(0, i) = UserForm3.Controls("textbox" & i).Value
  '読みとり列番号の数値化
If DATA(0, i) = "" Then
   DATA(1, i) = ""
  ElseIf Asc(Right(DATA(0,i), 1))>48 And Asc(Right(DATA(0,i),1))<58Then
   DATA(1, i) = 0  '固定セルのフラグON
  ElseIf Len(DATA(0, i)) = 2 Then
   DATA(1,i)=(Asc(Left(DATA(0,i),1))-64)*26+Asc(Right(DATA(0,i),1))-64
  Else
   DATA(1, i) = Asc(DATA(0, i)) - 64
  End If
Next i
  
 l = Application.Max(Workbooks("Mk4.XLS").Worksheets("条件表").Range("A3:A65536"))    ~~~~~~~~~~~~~~~~~~~~~~~~こことか
  
'読みとり行数の取得
 With Workbooks(FILE_Name).Worksheets(Sheet_Name)
  For i = 0 To 65534
   If .Cells(65536 - i, DATA(1, 2)).Value <> "" Then
    DATA_Row = .Cells(65536 - i, DATA(1, 2)).Row
    Exit For
   End If
Next i
 End With
  
 '上書きか追加か
 If CheckBox1.Value = True Or l = 0 Then
  k = Val(l) + 2
 Else
  With Workbooks("Mk4.XLS").Worksheets("条件表") '<==こことか
   Range(.Cells(3, 1), .Cells(2 + l, 49)).ClearContents
   k = 2
  End With
 End If
    
'ここから読込み
With Workbooks(FILE_Name).Worksheets(Sheet_Name)
 For i = 1 To DATA_Row
  If .Cells(i, DATA(1, 35)).Value <> "" Then
   k = k + 1
   Workbooks("Mk4.XLS").Worksheets("条件表").Cells(k, 1) = k - 2
   For j = 2 To 34
    If DATA(1, j) <> "" Then
     If DATA(1, j) = 0 Then '固定セルの条件
      Workbooks("Mk4.XLS").Worksheets("条件表").Cells(k, j) = _
                     .Range(DATA(0, j)).Value
     Else
      Select Case j
       Case 4, 19
        Workbooks("Mk4.XLS").Worksheets("条件表").Cells(k,j)="○"
       Case Else
        Workbooks("Mk4.XLS").Worksheets("条件表").Cells(k,j) = _
              .Cells(i, DATA(1, j)).Value
      End Select
     End If
    Else
     Workbooks("Mk4.XLS").Worksheets("条件表").Cells(k, j) = ""
    End If
   Next j
  End If
 Next i
End With
 Workbooks(FILE_Name).Close SaveChanges:=False
 MsgBox " 読込みが終了しました!! "
 Unload UserForm3
End Sub
________________________________________________________
Private Sub CommandButton3_Click()
  varRet = Application.GetOpenFilename("EXCELファイル,*.xls")
  If VarType(varRet) = vbBoolean Then
    Exit Sub
  Else
    FILE_Pass.Value = varRet
    For j = 1 To 256
      If Left(Right(varRet, j), 1) = "\" Then
        FILE_Name = Right(varRet, j - 1)
        Exit For
      End If
    Next j
  End If
  Workbooks.Open Filename:=varRet
  
  ComboBox1.Clear
  For Each WS In Workbooks(FILE_Name).Worksheets
    ComboBox1.AddItem WS.Name
  Next
End Sub
______________________________________________________
あと自分の分かる範囲でマクロを書いていますので、御覧のように野暮ったいものとなっています。何かアドバイスを頂ければ幸いです。

【38911】Re:SELLの指定法について
発言  ハチ  - 06/6/14(水) 10:55 -

引用なし
パスワード
   マクロの記載されているbookなら

ThisWorkbook.

で良いのでは?

【38916】Re:SELLの指定法について
発言  ハト  - 06/6/14(水) 12:00 -

引用なし
パスワード
   こんにちは

▼わいわい さん:
>いつもお世話になります。
>ファイルの指定法について教えてください。
>UserForm3の規則(textbox内の文字で読取列を指定)に従い、Private Sub CommandButton3_Click()で開いた外部ファイル(シートはComboBox1で選択)から、CommandButton2_Click()にてデータを取込もうとしています。
>以下のマクロで動いてはいるのですが、2つのファイルを操作しているので元ファイル(マクロのある)を操作するとき
>>Workbooks("Mk4.XLS").Worksheets("条件表").Range("A3:A65536"))
>のように指定しています。これですと将来ファイル名を変更したとき問題となるので良い方法を教えてください。Me.??で出来るのでしょうか?
>
この件についてはハチさんが回答されている通りです

>______________________________________________________
>あと自分の分かる範囲でマクロを書いていますので、御覧のように野暮ったいものとなっています。何かアドバイスを頂ければ幸いです。

気が付いたところでは

Workbooks("Mk4.XLS").Worksheets("条件表")
が何度も出てきますので、メンテを考えると

Dim MWS As Worksheet

Set MWS = Workbooks("Mk4.XLS").Worksheets("条件表")
(または Set MWS = ThisWorkbook.Worksheets("条件表") )

として、
> l = Application.Max(Workbooks("Mk4.XLS").Worksheets("条件表").Range("A3:A65536"))
等を
l = Application.Max(MWS.Range("A3:A65536"))

のようにしておくと修正するのが1箇所ですみます


>'読みとり行数の取得
> With Workbooks(FILE_Name).Worksheets(Sheet_Name)
>  For i = 0 To 65534
>   If .Cells(65536 - i, DATA(1, 2)).Value <> "" Then
>    DATA_Row = .Cells(65536 - i, DATA(1, 2)).Row
>    Exit For
>   End If
>Next i
> End With
ここはループ処理をしなくても
 Dim mR As Range

 With Workbooks(FILE_Name).Worksheets(Sheet_Name)
    Set mR = .Cells(65535, DATA(1, 2)).End(xlUp)
    DATA_Row = mR.Row
 End With

で求められます

_______________________________________________________
>Private Sub CommandButton3_Click()

>    FILE_Pass.Value = varRet
>    For j = 1 To 256
>      If Left(Right(varRet, j), 1) = "\" Then
>        FILE_Name = Right(varRet, j - 1)
>        Exit For
>      End If
>    Next j
ここもループ処理ではなく

    j = InStrRev(varRet, "\")
    FILE_Name = Right(varRet, Len(varRet) - j)

で求められます

【38920】Re:SELLの指定法について
お礼  わいわい  - 06/6/14(水) 13:06 -

引用なし
パスワード
   ハチ様、ハト様 回答ありがとうございます。

ThisWorkSheetsで出来ますねぇ・・・一度試したときは、Errが出たんですけど周りとの兼ね合いなんでしょうかね。そのときの内容が無いので再確認は無理ですけど。とにかくこれで後顧の憂い無く邁進できます。 

また、ハト様には幾つもアドバイスを頂きありがとうございました。今後とも宜しくお願いいたします。

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