Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2016)
AODを使用したデータ抽出で一部文字が転記されない
投稿日時: 21/01/16 22:01:55
投稿者: torao

お世話になっております。
 
前回の質問で
 
フォルダ内にあるEXCELブックを「開いて→データ抽出し作業シートに転記→閉じる」処理について質問させて頂きましたが、今回試しに、上記の処理をAODを使用して試して処理スピードを計測しました。
 

引用:
(処理内容)
同じフォルダ内にある一つのEXCELブックにあるシートのデータを転記
 
・ブックを開いてデータ抽出処理
 0.7734375
 0.7578125
 
・AODにてデータ抽出
 0.140625
 0.1328125

 
結果AODが圧倒的に高速。
 
しかし、取り込んだデータをよく見ると、ところどころ空欄になっておりデータが正しく転記されていません。
 
・データは日付や記号が入っています
→取り込まれていない文字を調べると、転記されたりされていなかったりと不規則
 転記されていない値は文字列ですが特定文字ではないようでした。
 ※但し、数値(日付データ)は正しく転記されていました。
 
ネットでこの現象を調べると、カンマ区切りとなっていないデータや、数値・文字が混在していると正しくデータが読み込まれないことがあるようです。
 
(質問です)
・AODを使用する場面は限られ、どんなデータでの抜けなく取り込むことは難しいのでしょうか?
 
すみません、無理でしたら諦めます。
簡単な工夫で実現できそうなら、取り組もうかと考えております。
 
皆様アドバイスのほど宜しくお願い致します。
 
 

投稿日時: 21/01/16 22:04:05
投稿者: torao

すみません。AODの処理コードです。
 
Sub ADO_データ抽出処理()
    '参照設定:Microsoft ActiveX Data Objects 6.1 Library
    Dim StartTime As Double
    Dim StopTime As Double
    StartTime = Timer
     
    Dim cn As ADODB.Connection
    Dim Rs As ADODB.Recordset
    Dim EXProperties As String
    Dim constr As String
    Dim DBFile As String
    Dim strSQL As String
    'Dim num As Long
     
    DBFile = ActiveWorkbook.Path & "\AODテスト用.xlsm"
    EXProperties = """Excel 12.0;HDR=No;"""
    constr = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & DBFile & ";Extended Properties=" & EXProperties
     
    Set cn = New ADODB.Connection
    cn.ConnectionString = constr
    cn.Open
    strSQL = "Select * from [202011$B7:AI66];"
     
    Set Rs = New ADODB.Recordset
    Rs.Source = strSQL
    Rs.ActiveConnection = cn
    Rs.Open
    Dim num As Variant
    With Worksheets("tmp")
        .Cells.Clear
        num = .Range("A1").CopyFromRecordset(Data:=Rs)
        .Cells(.Rows.Count, "A").End(xlUp).Offset(0, 1).Value = num
    End With
     
    Rs.Close
    Set Rs = Nothing
    cn.Close
    Set cn = Nothing
     
    StopTime = Timer ' 処理後
    Debug.Print (StopTime - StartTime)
End Sub

回答
投稿日時: 21/01/17 15:30:11
投稿者: WinArrow
投稿者のウェブサイトに移動

>.Range("A1").CopyFromRecordset
 
で、シートに書き込んだデータを眺めて
おかしなところと、元データを照合してみればいかがでしょう。

回答
投稿日時: 21/01/17 15:31:44
投稿者: WinArrow
投稿者のウェブサイトに移動

追伸
 
>AOD
を連発していますが、
正しくは
ADO
だからね・・・

投稿日時: 21/01/17 16:25:14
投稿者: torao

WinArrow さま
 
アドバイスありがとうございます。
 
抜け落ちるパターンがわかりました。
 
数値と数値に囲まれた部分の文字が抜け落ちていました。
 

引用:
(下記は勤務表の一部抜粋)
 
2020/11/1    出A    2020/11/3    出A
2020/11/1    出A    2020/11/3    出A
2020/11/1    出A    2020/11/3    出A
2020/11/1    出A    2020/11/3    出A
        出A    2020/11/3    出A
        出A    2020/11/3    出A
2020/11/1    出A            出A
2020/11/1    出A            出A
2020/11/1    夜A    2020/11/3    遅A
2020/11/1    夜A    2020/11/3    遅A
2020/11/1    遅A    2020/11/3    出A
2020/11/1    遅A    2020/11/3    出A
2020/11/1    遅A    2020/11/3    出A
2020/11/1    遅A    2020/11/3    出A
2020/11/1    遅A    2020/11/3    出B
2020/11/1    遅A    2020/11/3    出B
        夜A            ○
        夜A            年
        ○            夜a
        年            夜a
        ○            ○
        ○            ○
2020/11/1    出A    2020/11/3    出A
2020/11/1    出A    2020/11/3    出A
2020/11/1    出A    2020/11/3    出A
2020/11/1    出A    2020/11/3    出A
2020/11/1    出A    2020/11/3    遅A
2020/11/1    出A    2020/11/3    遅A
 
 

 
ネットでさがすけれど、なぜかわかりません。

投稿日時: 21/01/17 16:41:48
投稿者: torao

すみません色々試してみて
 
・「`」+数値というふうに数値を文字列にすると読み込まれました(感動)。
 
・勤務表自体、数値を文字列のままというふうには出来ないため
 読み込む際に、一括で文字列に変換できたらと考えております。
 
 出来たら「ADOコードに全て文字列として読み込む」のような設定とかありますでしょうか?
 
 すみません引き続きアドバイスお願いいたします。
 
 
`2020/11/1
`2020/11/1
`2020/11/1
`2020/11/1
 直
 直
`2020/11/1
`2020/11/1
`2020/11/1
`2020/11/1
`2020/11/1
`2020/11/1
`2020/11/1
`2020/11/1
`2020/11/1
`2020/11/1
 ○
 ○
 夜A
 夜A
 ○
 ○
`2020/11/1
`2020/11/1
`2020/11/1
`2020/11/1
`2020/11/1
`2020/11/1
 
 

投稿日時: 21/01/17 20:00:25
投稿者: torao

途中報告です。
 
データを文字列に変換
カンマ区切りデータとしてテキスト化
配列に取り込んで
シート転記
 
という形まで出来ました。
 
 
    'レコードの内容を文字列で取得(60レコード分)
    '※フィールド区切りは「,」レコード区切りは「vbCrLf」
    Dim myStr As String
    myStr = myRS.GetString(adClipString, 60, ",", vbCrLf)
    'myStrをsplit改行しtmpに格納
    Dim tmp As Variant: tmp = Split(myStr, vbLf)
    'tmpを形成し配列に格納しながらシート転記
    Dim i As Long, x As Long
    Dim GetData As Variant 'カンマでsplitして格納
    For i = 0 To UBound(tmp)
        GetData = Split(tmp(i), ",") 'tmp(i)をカンマで区切りGetDataに格納
        For x = 0 To UBound(GetData) '配列をループでシートに転記
            Worksheets("tmp").Cells(i + 1, x + 1).Value = GetData(x)
        Next x
    Next i
 

回答
投稿日時: 21/01/17 22:03:40
投稿者: たらのり

こんばんは
  
# スレッドはよく読んでいません
  
CSV形式(プレーンテキスト)にしたのであれば,schema.ini
を使用すると列ごとにデータ型を指定できると思います。
  
# schema.ini の綴りはうろ覚えです
 

投稿日時: 21/01/17 22:05:09
投稿者: torao

皆様 
 
とりあえず自己解決しました。
 
EXProperties = """Excel 12.0;HDR=No;IMEX=1"""
 
IMEX=1追加で読み込み出来ました。
 
あと実用できるか検証します。