none
Excel 2010(X64) VBA 他のBOOKを開かずにDataを読み込む時、最終のDataの認識の仕方がわからない。 RRS feed

  • Question

  • 下記のプログラムでDataの最後を認識するには?

    Option Explicit
       Dim Cell
       Dim WS1 As Worksheet
       Dim myPath As String
       Dim Bookname As String
       Dim SheetName As String
       Dim myAddress As String
       Dim myFormula As String
       Dim j As Long
       Dim i As Long
       Dim R_Row As Long
      
    Sub ブックを開かずに値を取得()
             Set WS1 = Sheets("Sheet1")
             myPath = "c:¥Users" '----ファイルのパス
             Bookname = "Book1.xlsm" '----ブック名
             SheetName = "S_Name" '----シート名
             'R_Row = WS1.Cells(1048576, 31).End(xlUp).Offset(1).Row 
             R_Row = 142981+18679    '←---現在使用しているDataの量(日毎に増加する)あまり多くなるとメモリーエラーになかも?// 'R_Row=Data +18679は2日分//。
             Range(Cells(1, 1), Cells(R_Row, 80)).ClearContents
             'Application.ScreenUpdating = False
             Cell = WS1.Range(WS1.Cells(1, 1), WS1.Cells(R_Row, 32))   ’←(R_Row, 32)の量を増やして(R_Row, 80)位にしたが、多きくするとメモリーエラーになる。
             
              For i = 10 To R_Row
                   For j = 27 To 32 '----列
                         myAddress = "R" & i & "C" & j  '----セルの行
                       Cell(i, j) = "=('" & myPath & "¥[" & Bookname & "]" & SheetName & "'!" & myAddress & ")"
                     Next
                  ’ここにData処理プログラムを入れたいが、Cell(i, j)ですとDataの扱い方が解らないので、読み込み終了後に処理する。[Cells(i, j)にすると問題ない]
           ①ここが問題⇒ If Cell(i,32) = Empty then Exit For
                 Next
                
                   WS1.Range(WS1.Cells(1, 1), WS1.Cells(i - 1, 32)) = Cell  '注意Cells(i, j)にした時はこの行は削除する
                   Erase Cell
                   'Application.ScreenUpdating = True
    End Sub
    ---------------------------------------------------------------------------------------------------------------------------------------------

               ①問題無⇒ Cells(i, j) = ('C:¥Users¥[Book2_17.xlsm]S_Name'!$AF$11)
               ②問題無⇒ If Cells(i,32) = Empty then Exit For  'Cells(i,32)=('C:¥Users¥David¥Documents¥[Book2_17.xlsm]S_Name'!$AF$1000)
              
               1、上記は、問題無く動作しますけれど処理速度がものすご~く遅い。// R_Row=1,000で約6分かかります。
              
               ①問題有⇒ Cell(i, j) = ('C:¥Users¥[Book2_17.xlsm]S_Name'!$AF$11)"
               ②問題有⇒ If Cell(i,32) then Empty Exit For   'Cell(i,32)=('C:¥Users¥[Book2_17.xlsm]S_Name'!$AF$161660)
              
               2、上記は、問題有るがが処理速速度が早い。// R_Row=162,660で約3分30秒です。    

           
               
               Dataの取扱量(R_Row)をふやすと下記のエラーが出ました。//R_Row=1,048,576より大きな数字を使用したかも?           
                1、いくつかの計算中にリソース不足になりましたため、これらの数式を評価できません。
                2、実行エラー'1004'
                   アプリケーションの定義または、オブジェクト定義エラー
                3、メモリー不足です、完全に表示できません。   
              
         ◎解決したい問題
          1、WorkSheetのDataの最後を検知する方法。
          2、Cell(i,32)は('C:¥Users¥[Book2_17.xlsm]S_Name'!$AF$161660)で記録される、
            ('C:¥Users¥[Book2_17.xlsm]S_Name'!$AF$161660)のDataの取り扱い方法。
          3、Bookを開かずWorkSheetをCopy / Pasteする方法。(たぶんこれが解れば、1、2、は使わなくてよくなる。∴これが一番知りたい。)
            以上の3点です。



    • Edited by DavidMicro Thursday, December 27, 2012 1:05 PM
    Thursday, December 27, 2012 9:09 AM

Answers

  • 諸々、了解しました。

    ADOに関しては、ネット上に比較的多く情報があると思います。ただし、C#やVBといった.NETの言語からも扱うことがありますので、それらの情報も多く引っ掛かると思います。「VBA ADO」などで検索されると良いと思います。

    (例)
    ADOでExcelワークシートに接続
    http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_130_090.html


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    • Marked as answer by DavidMicro Monday, December 31, 2012 12:08 PM
    Friday, December 28, 2012 7:39 AM
    Moderator

All replies

  • 処理が遅い件に関しては、以前以下のスレッドでご紹介したように自動再計算と画面更新を一時的に停止されると改善される可能性があります。

    Excel 2010(x64) VBAでセルの表示が途中から変わらなくなる、最後は正常に表示する。
    http://social.msdn.microsoft.com/Forums/ja-JP/vbajp/thread/444b2b66-7128-49ad-afac-b1d494e05c64

    また、Bookを開かずにWorkSheetを読む方法ですが、ADOが使う方法と、Excel 4.0のマクロを使う方法を思い浮かべます。

    (参考)
    ADOを使ってExcelシートを読み出す方法
    http://www7b.biglobe.ne.jp/~whitetiger/ex/ex2002088.html

    閉じたブックからデータを取得する
    http://www.moug.net/tech/exvba/0060037.html


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    Friday, December 28, 2012 2:01 AM
    Moderator
  • trapemiya(MVP)さん、いつももありがとうございます

      処理が遅い件に関して、前回紹介していただいたスレッド中で、下記の方法が表示が早いです。

       cntRec = cntRec + 1
       Application.StatusBar = "処理実行中....(現在 " & cntRec & "件)"

      試してみました、やはり途中で止まります。

     自動再計算と画面更新は使用しもしなくても同じです。(これはセル機能をコントロールしていると思もいます)
      皆様のアドバイスのおかげで、セルにDataを戻して処理させると、その分だけ処理スピードが遅く成る事がわかりました。

      前回 hihijiji さんから、アドバイスしていただいた中で、
     (他のアプリケーションやタスクによる影響で)OSから画面更新を促されてもExcelは無視します。
     よって、のっぺらになったりゴミが残ったりは異常ではありません。} により得た結果、処理速度が速いため画面表示が上手く出来ない?。
     目的はData処理の進行状況が知りたいので、時々画面に表示すれば進行状況は分るし処理速度も表示回数で変るのでそれなりのプログラムを考えてみます。
     
      前回 trapemiya(MVP) さんの紹介のURL中にプログレスバーの使い方がありました。これはExcelの外で画像処理をしてると思います。
     いつか試してみたいです。
     
      今回 trapemiya(MVP) さん(参考) ADOを使ってExcelシートを読み出す方法を見てみます。
     Excel 4.0のマクロは以前使用しました。処理速度が遅く感じました。
     ADOと関数は、ほとんど解りません、これからの課題です。(関数は処理速度が遅い[速い関数もあります]と思っているので、ほとんど使用してません)
     
      Dataの多いEccelを開く時は時間が掛かります、過去のDataを少しでも早く読み込む方法をさがしてます。何か良い方法があれば教えてください。
     今回の方法だと1sheetのDataを読み込むのに、約4分かかります、現在は、20sheet使用してますから、bookを開てCopy/Pasteしたほうが早いです。
     
     
       一番知りたいのはBookを開かずにsheet又は、Bookを丸ごと読み込む方法です、ADOは良くわからないので参考になるプログラムを具体的に教えて
      頂ければ幸いです。

    Friday, December 28, 2012 7:08 AM
  • 諸々、了解しました。

    ADOに関しては、ネット上に比較的多く情報があると思います。ただし、C#やVBといった.NETの言語からも扱うことがありますので、それらの情報も多く引っ掛かると思います。「VBA ADO」などで検索されると良いと思います。

    (例)
    ADOでExcelワークシートに接続
    http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_130_090.html


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    • Marked as answer by DavidMicro Monday, December 31, 2012 12:08 PM
    Friday, December 28, 2012 7:39 AM
    Moderator
  • trapemiya(MVP)さん、ありがとうございます

    microsoft activex data objects 2.x library

    microsoft activex data objects 6.x library

    が有りますが6.1は問題があるのですか。

    6.1入れましたが、よろしいでしょうか。



    • Edited by DavidMicro Friday, December 28, 2012 8:38 AM
    Friday, December 28, 2012 8:27 AM
  • 問題ないと思います。なぜ、問題があると思われたのでしょうか?どこかにそのような情報がありましたか?
    とりあえず試してみて下さい。


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/

    Friday, December 28, 2012 2:36 PM
    Moderator
  • trapemiya (MVP)さん お世話になります

     Microsoft ActiveX Data Objects 2.x Libraryへの参照設定をし保存して、Exceleを終わらせ次に立ちあげた時に設定画面の2.xが元の場所に戻って✔無くなっていました。
    今度は6.1を入れました。2.xと6.1の違いを調べてる中で、「ビルドマシンと Windows 7,そしてクラシックな ADO」の記事で最後の文章を勘違いして質問をしてしました。

     「ADOを使ってExcelシートを読み出す方法」のプログラムをCopyし必要な部分を手直しいて、どこまで動くかテストしたら手直しミスで途中でエラーになりました。
     Excelを修了して次に立ちあげた時は最初でエラーになりました、参照設定画面の6.1は元の場所に戻って✔無くなっていました。2~3回設定をしましたが同じ事でした。
     「Excelでお仕事」の参照設定はmicrosoft DAO 3.6 objects libraryが設定してありましたので、それを設定してもう一度6.1を設定しましたら設定できました。

     

     

    Saturday, December 29, 2012 12:29 AM
  • trapemiya(MVP)さん、ありがとうございます。

    ADOでExcelワークシートに接続
    ファイルの拡張子が「.Xls」なら下記のプログラムで動きます(とても速いです)

    しかし、問題があります。
     私のシステムでは閉じたBookを読み込み時、Sheetが7以上あるとメモリーエラーします。(Book開いているときは問題ないです)
     セルの使える行が65536までしかないのです。
     「.Xlsm」拡張子は、 (実行時エラー -2147467259 外部テーブルのフォーマットが正しくありません)と出ます

    他のBOOKを開かずにDataを読み込む事は解決しました。ありがとうございます。


    Option Explicit
        Dim cn         As ADODB.Connection
        Dim rs          As ADODB.Recordset
        Dim ①strFilePath As String
        Dim ②strFileName As String
       
    Sub loadADOJetOLEDB()
       'On Error GoTo Err_Handler
        ①strFilePath = "c:\Users\"   '--ファイルパスを指定する
        ②strFileName = "Book1.xls"     ' --ファイル名を指定する"
        ②strFileName = ①strFilePath & ②strFileName '  --面倒なのでファイルパス+ファイル名にする
       
        'Application.ScreenUpdating = False
          ActiveSheet.Protect UserInterfaceOnly:=True '--マクロによる操作も保護を解除
          ActiveSheet.Unprotect


             Set cn = New ADODB.Connection
             Set rs = New ADODB.Recordset
            
                cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ②strFileName & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=yes;"";"  'ワークシートにヘッダー行が無い時は、HDRにはNoを設定します。
               
                rs.Open "SELECT * FROM [Sheet$]", cn, adOpenStatic, adLockOptimistic, adCmdText '--読み込みシート名$
               
                ThisWorkbook.Sheets("Sheet1").Range("A1").CopyFromRecordset rs  '--'書き込みシート「Sheet1」のB2に貼り付けます。
               
                rs.Close: Set rs = Nothing  '--オブジェクトの破棄
                cn.Close: Set cn = Nothing  '--オブジェクトの破棄
        'Application.ScreenUpdating = True
        Exit Sub
       
    Err_Handler:
        Application.ScreenUpdating = True
        MsgBox CStr(Err.Number) & Err.Description
    End Sub


    • Edited by DavidMicro Monday, December 31, 2012 12:26 PM
    Monday, December 31, 2012 12:23 PM