2015年8月12日水曜日

EXCEL2013でSQL、EXCELシート同士をクエリする(最終回)まとめ+VBAコード


EXCEL+VBA+SQL(ADO) 外部DBなど使わず、全てをエクセル内で完結させる

2回に渡り記事をUPしたが、まだ完全に纏められるほど頭が固まっていないが、最終回。
現時点での纏めです。※コードは後半




 (免責)
・下記はコード作成中に発見したモノで、コードを作った後で改めて検証は行っていないため、不正確な解説が行われている可能性あるが、不正確な点あればスルーの事。
・MSにより随時更新されているEXCELのパッチにて問題が解決する場合もあるが、新たな別の問題が発生する場合もある(現在日:2015/8)。
※MSのOffice系パッチはとてもいい加減で、ある日突然マクロ(VBA)がエラーで動かなくなる(仕様が真逆に変更になっていた)等、過去にMSにしてやられたこと数回=私的には業務PGMでexcelなど決して使いたくない




「 EXCEL エクセル+VBAのコツ 」

「エクセルの要素(cells、sheetなど)は、本処理では、絶対に!決して!直参照しない」
 ※処理件数が少なければシート直接参照OK!! 問題ない。

理由:処理が数百~数万倍遅くなる
    2015年8月現在はBOOKバグも発生するのでSQL使用時は必須と考えた方が良い

・最初に、シートにあるデータは、VBAで配列(若しくは変数)を作りシートを丸ごと入れ、
 その配列や変数のみを使って処理を行う
 (シートを参照しない=処理速度が異様に高速になる)
・最後に配列や変数の値を、結果表示するEXCELシートへ貼り付ける

・・処理流れ・・
「前処理」 VBAからI/Oするシートやセルのデータは配列や変数を作って入れる
「本処理」 絶対に!!!sheet、cell、等エクセルシート内の要素を直参照・直設定しない。
        配列や変数のみを使い処理ロジックを書く。
「後処理」 処理結果をEXCELシートへ一括貼付する(range)

これらは一般的に、VBA高速化テクニック、として公開されているが、私的には必須と思っている。
出来ないならコード書くな!と言いたい。

参考ページ → とても分かりやすい解説




「 EXCEL エクセル+VBA+ADO+SQL (シート同士をjoinしたクエリ) のコツ 」

「シート上で数字に見える列は、事前に全て ' を付けEXCELに文字列と宣言する」



   「 教訓 」
   VBAでは問題ないが、SQL(ADO)では問題になる事がある(永遠にマッチしない事態に陥る可能性ある)

   多分今回発生した一連の問題のキモはこれでした。

   ADOに渡ったデータの値が変わってしまうなど思いもしなかったですから。


------------
 まず解説)
------------
 EXCELセルの中身 A1:1234 A2:0123 A3:'0123
 EXCELでの見た目 A1:1234 A2:0123 A3:0123

 A2はセルの書式設定で「文字列」と指定してある

------
 VBAにて
------
Sub main()
Dim wsMain As Worksheet
Set wsMain = Worksheets("Sheet1")

MsgBox (wsMain.Range("A1").Value)
MsgBox (wsMain.Range("A2").Value)
MsgBox (wsMain.Range("A3").Value)
End Sub

 実行すると・・・次のよう表示される
  1234
  0123
  0123
 VBA上ではEXCELの数値の仕様とセルの書式設定が効いている。
 だから2番目と3番目が「0123」と表示される

------
 しかしADOでは!!
------
 EXCELの数値仕様 : 適用
 セルの書式設定 : 無視

 データは其々次のよう読み込まれていた

  1234
  123
  '0123

 VBAで表現されるデータがそのままADOに渡ると思っていたため、永遠にjoinしない状況が発生!
 ※実際にADOに渡されたデータを見ることは不可能。(今回使ったドライバーではこうなった)

------
 回避策は・・
------
 EXCELの数値列の全セルの頭に、予め ' を付加し、ADOでは「'0123」と言うデータでSQLした。


 今回のSQLに使ったドライバ
  Microsoft ActiveX Data Objects 6.1 Library
  Provider:Microsoft.ACE.OLEDB.12.0
 他のドライバ、またバージョンによって動きが異なること、大いにあり得る









< コツ(まとめ) >

・ADO(VBAのADO)では、エクセルで設定したセルの書式設定は無視される
  上記より

・数字に関してエクセルには数々の仕様が存在し、事前に仕様が適用されたのち、クエリ(ADO)へ渡される
  仕様回避は不可能 上記と記事(2)を参照

・ADOを使用するなら数字列はEXCEL上で事前に ' を付加しておく
  処理結果をシートへ結果を貼り付ける時も、EXCELの数値仕様が適用され問題となることがあるので ' を付けると全て上手く行く

・SQLのjoinキーは、エクセルでの全ての数字の制約(仕様)を考慮しないと永遠にマッチしない事態に陥る
 今回大問題になった事件(記事(2)を参照

・対策としてEXCEL数値列に ’ を付加するには
 1.別シートを用意し当該シートのセルを其々参照させ、その別シートをVBAに渡しクエリで使用する(簡単)
 2.CVSなどをラッパーとし利用I/Oし、書きor読込時に ' を付加し、新シートへ書き込むVBAを作る(面倒)


 上記数字列全てに ' を付けてしまう手法は、joinキーに数字を用いない場合もエクセルの各種数字に対する制約がキツイので、なら数字列は予め全て ' 付けてしまえ!!的な手法。 但し美しくないので、机上のプロやIT記者(私はマスタべーションのプロと呼んでいる)は嫌がると思うが、なら現場入ってみろ!と私ならそう言います。

 批判されること、あるかも知れない現場的な手法ですが、開発もメンテも楽になりますよ!








今回作ったコードの一部を記します。ご参考まで。

※これがウォータープルーフ開発で用いられていた上から下へ流れるコード一例、でもある
 (オブジェクト指向でない = 今どきこんなコードを書いたら張り倒されるかクビになるかも?)
※VBAは、VBA<VB6 要するにVBAは20年前の代物=学習教材としては最悪です
 java(JavaScriptではない)、PHP、C#を勉強しましょう
※解説はしませんので質問もしないでください、少々雑でいい加減な個所もありますが動きます
※不明なパラメータなど、ググれば腐るほど解説ページが出てきますので、そちらを参照のこと
※エラー処理は外しているので、On Error GoTo、して下さい

※下記コードの著作権は私にあるので好き使っていただいて結構です (但し、使えるものなら!)



'-----------------------------------------
' サンプルコード START

' 
' Windows10(64bit)、Excel2013(64bit)、拡張子~.xlsm、にて稼働中
'-----------------------------------------
Sub CollectAccountDue()


    '出力する未収金反映済の請求シート名
    Dim OutSheetName As String
    OutSheetName = "当月請求"


    'SQL文
    Dim SQLSheet As String
    Dim SQLCell As String
    SQLSheet = "tempSQL"
    SQLCell = "A2"


    'ワークシート
    Dim wsMain As Worksheet
    Dim wsSql As Worksheet
    Dim wsOut As Worksheet
    Set wsMain = Worksheets("メイン")       'メイン画面
    Set wsSql = Worksheets(SQLSheet)        '当該セルよりSQL読込
    Set wsOut = Worksheets(OutSheetName)    '出力先シート名


    'ツール → 参照設定 → Microsoft ActiveX Data Objects 6.1 Library
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql As String


    '出力STARTセル情報
    Dim oColumn As Long     '出力カラム
    Dim oRow  As Long       '出力行
    oColumn = 1
    oRow = 1


    Application.ScreenUpdating = False

    '出力先シートをクリア
    wsOut.Cells.Clear


    'ADO
    Set cn = New ADODB.Connection
    cn.Provider = "MSDASQL"
    cn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
        & ThisWorkbook.FullName _
        & ";Extended Properties=""Excel 12.0 Xml;IMEX=1;HDR=YES;"""


    sql = wsSql.Range(SQLCell).Value     '当該セルよりSQL文を読込

    cn.Open

    Set rs = New ADODB.Recordset
    rs.Open sql, cn, adOpenStatic


    If rs.Fields.Count < 1 Then
        MsgBox ("エラー発生!!該当者がマスター上に存在しません。処理を終了します")
        Exit Sub
    End If


    '(未使用)新シート作成(現在のシートの最後に追加)
    '(未使用)Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = Range(NewSheetNameCell).Value


    '---------------------------------------
    '--- Query Data -> New Sheet ---
    '---------------------------------------

    Dim rsFieldsCount As Long
    rsFieldsCount = rs.Fields.Count


    '出力
    Dim rowArray(1 To 3000, 1 To 100)

    Dim colCount As Long
    Dim rowCount As Long
    colCount = 0
    rowCount = 1


    '未収金、請求額算出
    Dim Claim As Long
    Dim Claim1 As Long
    Dim Claim2 As Long
    Dim Claim3 As Long


    '========================
    Dim dblStart As Double
    Dim dblEnd As Double
    Dim dbltime As Double
    dblStart = Timer
    Debug.Print dblStart
    '========================


    'クエリ結果を配列へ挿入
    ' EXCELバグが発生しなければ配列へ挿入する倍の時間で直接シートへ挿入可能
    ' だがEXCELのBook内バグが発生しだすと300倍の時間が掛かり回避不可能
    ' 従って回避策として配列へ挿入する


    colCount = rsFieldsCount + 3    '未払金、小計、請求額を付加
    Do Until rs.EOF


        'ヘッダー行セット(1行目)
        If rowCount = 1 Then
            For i = 1 To rsFieldsCount
                rowArray(1, i) = rs(i - 1).Name
            Next
            rowArray(1, 45) = "未払額"
            rowArray(1, 46) = "小計"
            rowArray(1, 47) = "請求額"
            rowCount = rowCount + 1
        End If


        'データ行セット(2行目以降)
        If rs(2).Value <> "'" Then
            'col
            For i = 1 To rsFieldsCount
                If IsNull(rs(i - 1).Value) = False Then
                    rowArray(rowCount, i) = rs(i - 1).Value
                Else
                    rowArray(rowCount, i) = ""
                End If
            Next


            '========================================
            '  未払い金、小計、請求額を付加
            '========================================
            '請求金額(32)
            If (rowArray(rowCount, 32) = "") Then
                Claim1 = 0
            Else
                Claim1 = rowArray(rowCount, 32)
            End If
            '当月合計(20)
            If (rowArray(rowCount, 20) = "") Then
                Claim2 = 0
            Else
                Claim2 = rowArray(rowCount, 20)
            End If
            '現金入金(21)
            If (rowArray(rowCount, 21) = "") Then
                Claim3 = 0
            Else
                Claim3 = rowArray(rowCount, 21)
            End If


            '** 未払額 **  ** 小計 **
            If (rowArray(rowCount, 37) = 0) Then
                rowArray(rowCount, 45) = Claim1
                Claim = Claim1 + Claim2 + Claim3
            Else
                rowArray(rowCount, 45) = 0
                Claim = Claim2 + Claim3
            End If
            rowArray(rowCount, 46) = Claim


            '** 請求額算出 **
            If (Claim <= 0) Then
                rowArray(rowCount, 47) = 0
            Else
                rowArray(rowCount, 47) = Claim
            End If


            '行カウント
            rowCount = rowCount + 1
        Else
            Exit Do
        End If

        rs.MoveNext
    Loop
      
    Dim rowCnt
    rowCnt = UBound(rowArray, 1) '1368
              
    '======================
    '出力先シート ← 配列
    '======================

    '1Startを0Startにする(正常に戻す)
    rowCount = rowCount - 1
    '-1を行った後のrowCountは、1行目のヘッダーを含めた件数なので注意!!!


    wsOut.Select    'アクティブにしておかないと「rangeオブジェクトは失敗しました 1004 」が発生する
    wsOut.Range(Cells(1, 1), Cells(rowCount, colCount)) = rowArray  ’配列→シート:オーバーフローしているが問題ない(切り捨てられる)


    '========================
    dblEnd = Timer
    Debug.Print dblEnd
    dbltime = dblEnd - dblStart
    Debug.Print "処理時間は" & Format$(Int(dbltime)) & "秒でした"
    Debug.Print "処理時間は" & Format$(Int(dbltime * 10 ^ 4 + 0.5) / 10 ^ 4) & "秒でした"
    '========================


    rs.Close
    cn.Close


    '============================
    '口振シート作成(CSV化の元データ)
    '============================
    Dim rowBankArray()
    ReDim rowBankArray(1 To rowCount - 1, 1 To colCount)    '1行目はヘッダー行なので不要、-1する)
    Call CreateBankTransferData(rowArray(), rowBankArray())


    '================================
    'コンビニシート作成(CSV化の元データ)
    '================================
    Dim rowCvsArray()
    ReDim rowCvsArray(1 To rowCount - 1, 1 To 80)    '1行目はヘッダー行なので不要、-1する)
    Call CreateCVSTransferData(rowArray(), rowCvsArray())


    Application.ScreenUpdating = True

    'メインシートをアクティブにする
    wsMain.Select


    Dim ret
    ret = MsgBox("処理は正常に終了しました", vbOKOnly, "当月請求データ作成")


End Sub
'-----------------------------------------

' サンプルコード END
'-----------------------------------------


※↑ブログUP時、特殊文字が自動変換されてしまうので大文字にしている箇所があります
※仕様としては、顧客マスタと未払金CSVをSQLでマッチングさせ、通常請求額+未払い額を算出し、当月請求額としてFIXさせている。





一応クエリも貼っておきます

select * from [請求データ$] a left outer join [CSV1$] b ON mid(a.顧客ID,2,5) = mid(b.請求番号,12,5)


・SQLはこんな感じにセルに記入
・VBAでシート「SQL」の「A2」を読み込み、SQL文として使用する



















※データは機密のため見せられませんが、EXCELで管理している「顧客情報」と「請求情報」なので大したことありませんから特に記述しません。




参考までに 第1回記事 で解説した、手作業で行っていたEXCELを使った請求業務。
上記EXCEL+SQLを使うことで、パートさんが行っていた毎月7人日必要としていた請求処理は、わずか8秒で完了できるようになった。
開発費用はたった半年で回収でき、あとはボロ儲け?めでたしめでたし。





( まとめ 余談 オチ )

3回に渡り、EXCELのシート同士を其々テーブルと見立て、とあるシートのセルに書いたSQL文をVBAで読み取り、ADOを使いそのSQLを実行、結果を新しいシートへ貼り付ける。と言うVBAコードと解説?意見?日記?を長々書いてきましたが、最後に余談とオチ。

私はEXCELには詳しくない普通の職業プログラマーだったため、基本EXCEL関数は使いません。と言うより信用していない、私には必要ない、と言った方が正しい。
やはり速度が非常に遅い点、応用が利かない点、MSが予告なく仕様を勝手に変更する点、EXCELやVBA特有の関数を覚えたところでEXCEL以外では使えない点=それでは金にならない=食っていけない、からですね。(VB6やっていたので対した苦も無くVBAでコード書けますが・・)

職業プログラマーはプログラミングや設計で生計を立てるので、いかに将来性があり(市場シェア)効率よく金になるモノに注力し取り組めるか(学習勉強)、これが注力対象を選択する一番のポイント。しかも腐るほど金を持っている巨大企業が好む言語等を選択するしかない。中小企業(金がない=単金も安く金払いも悪い)が好む事が多いEXCEL VBAの市場シェアは低く(無に等しい)覚えたところで永年に渡り食っていけません(寝る間を惜しんで死ぬほど学習しているなら別!)。それならjava、.net、c#、各種web系言語、開発、運用ノウハウ、コンサル等に注力した方がよっぽど良い。と言うよりそれ以外の選択肢はない。数十年に渡り家族食わしていかないとでしょ?これがEXCEL・VBAが私に必要ない理由であり覚えない理由。ノウハウは今回の記事だけで十分、もうイラナイ。


そして最後にオチ

今回の、シートAとBをマッチングし、結合結果をシートCへ書く、と言う処理は、EXCEL VBA関数である「Find」を使い応用すれば出来ます。
「VBA シート同士の結合」とググれば例は山ほど出てきます。コード量も大変少なく、、さて、あなたならどちらの手段を選択しますか?
SQLを使い処理件数が増えても速度も速く汎用性を持たせるが面倒なロジック、FINDを使い短いコードで処理をこなす。
FINDを使った処理群、速度に関してはどうなのでしょう?試してないのでわかりませんのでご自身でお試しください。

私はFINDもですがエクセル提供の関数など信用しないので基本EXCELは使いませんが(EXCEL関数群があそこまで処理が強烈に遅くハングする等のバグも多いとはイマドキ夢にも思いませんでした)、数百件程度の少量データならFINDを使う事を大いに検討するかな?と考えます。




 おわり




パート1はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする(第1回)
パート2はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする(第2回)

2015年8月5日水曜日

EXCEL2013でSQL、EXCELシート同士をクエリする(第2回)


先回のパート1、EXCEL2013でSQL、EXCELシート同士をクエリする(第1回)、に続き、
今回はEXCELの仕様(制約)、自動処理、EXCELのBOOKバグ、などEXCEL+SQL時の問題点を具体的に記す。

環境としてはWindows10(64bit)とWindows7(64bit)、EXCEL2013(64bit)。
Windows10だから発生した、と言う問題は皆無。win7、win10、共に事象は同じ。

※実際のVBAコードは 最終回 に記しています




「 教訓 」
VBAでは問題ないが、SQL(ADO)では問題になる事がある




「 処理 」
 1.シートAの列A left outer join シートBの列A
 2.結果をシートCに貼り付ける

「 クエリするシート 」
 ・シートA 行2000、列13
 ・シートB 行230、列6

「 JOINキー 」
 ・シートAの顧客No(数字5桁)
 ・シートBの請求番号(数字17桁)の中にある顧客No(数字5桁)




上記非常に単純なleft joinクエリ

一般的なDB(sqlserver、オラクル)を使いt-sqlを使えば(オラクルはPL/SQL)上記sql作成に15秒・実行に1秒、どんな書き方をしようとこんな単純なクエリはsql作成~実行完了まで16秒で終わるが、EXCEL+VBA+ADO(SQL)では多くの問題が発生したためsql作成~実行完了までに、なんと約1万倍以上の時間を要した(約5日)



< ポイント >

・最初に断っておきますが、joinキーに数字が入った列を使ったため問題山積みになりました。
・excelに文字列と認識される文字列が入った列を使えば問題は発生しません。
・よって、excelのSQLではjoinキーに数値列を使うと問題が起こる!と記憶しておくと良いかと思います。

対処法は下記以降を参照下さい。



================
  参考までに (初心者編) START
================
EXCEL VBAでのクエリ(SQL)の書き方は普通のクエリと少し異なり、こんな感じ(ADO)

select * from [請求データ$] a left outer join [CSV1$] b ON mid(a.顧客ID,2,5) = mid(b.請求番号,12,5)

構文は普通のSQLとほぼ同じ。ただ特徴的な事項がある

1.シートを指定する場合 [シート名$ ] と書く
  上記、請求データ、と、CSV1、はシート名

2.関数は結構違う
  一例は、VBAにはsubstringがないのでmidを使ったりと、関数の違いはある

3.シート = テーブル と考える
  シートAとシートBをjoinする = テーブルAとテーブルBをjoinする、そう読み替える

4.シートの1行目は項目名として認識されSQLが実行される
  これは大変便利でとても面白い機能

 今回、同一BOOK内の、シートAとシートBをSQLでJOINするが、シート1行目は見出し行でデータ行は2行目から。 通常DBでは1レコード目からJOINが開始されるが、ADOで使用するEXCELドライバーは1行目をJOINせず見出し行として無条件に出力してくれ、実際のJOINは2行目以降のデータ同士で行ってくれる。 これは便利でとても面白く結構感動ですね。

ざっと目だった点を言えば上記な感じ。

あっと、、もっと基本的な話が1点

・EXCELマクロはマルウェアやウィルスの温床として世間からは悪として扱われている。MSも悪として扱っている。
 そのため「マクロを有効にする!」そうわざわざEXCELの設定画面で指定しないとマクロ(VBA)は動かない。

 しかし便利な拡張子xlsmと言う「マクロが書かれたエクセルですよ!」と予め宣言している拡張子がある。
 もちろん、今回のようマクロ(VBA)を使う場合は拡張子をxlsmとして保管は必須。
 Windows10ではエクセルを開いたときに上部にマクロを有効にするか?ボタンをクリックするだけでマクロが動く。
================
  参考までに (初心者編) END
================





< 本題 >

結論を先に言えば・・

シートのデータをVBAに渡して処理だのクエリ(SQL)だのやらせ、結果をシートへ書き込む、と言うEXCEL+VBAでの処理。このとき全てのセルの頭に強制的に ' を付加し、文字列としてEXCELに認識+処理させれば、シートから読み込むときも、シートへ書き込むときも、問題は何も発生しない。

ただ使う側の人間に、例えば新データ1行追加する度、全てのセルの頭に ' を付けて!とは言えない(ミスも増える=人間のオペレーションを信用してはいけない)従ってその辺は別シートから参照を使ったり、VBAで対処させることが必要。



(免責)
以下、検証漏れ、解説ミスが発生している可能性もあるので、最終的にはご自身で検証下さい

また今回の問題は、joinキーとなる列が全桁数字表記されているため、excelの数字に対する仕様、が働いてしまい問題になった。要するにexcelが最初からこの列は文字列だ!と、そう判断してくれる列同士のjoinであれば、何ら問題ない。

例えば0100はexcelに数字と認識されてしまうが、A000は文字列なのでjoinキーに使っても何ら問題は発生しない。要するに変わったことをすると問題が多発する、と言う典型的例でもある。そのための長い解説と次回のコードがある。
ただEXCELとADOで使うEXCELドライバーの、数字に対する様々な仕様が見れるので、それはそれで面白いかも?





========================
 EXCEL+VBA+ADO(SQL) 4つの問題点 START
========================

1.データをADOへ渡すとき、EXCEL書式設定の文字列指定は無視され、結果数字として扱われる
  → そのためADOがSQLを実行する時点では頭の0が消去されており、結果JOINキーがマッチしなくなる
  → 対策)顧客No(数字5桁)の頭に ' を予め付加しEXCELに文字列と認識させる
    ※ユーザーに入力の度に ' を付加しろとは言えない
    ※そのため開発側で別シートを用意し ' を付加し、このシートをクエリ対象とする必要ある
    ※若しくはSQLを実行するVBAでSQLを実行する前に全行のjoin対象セルに ' を付加(この対処は不可能)


2.EXCELの制約(仕様)のため、デフォルトでは数字17桁を表示できない
  ex. 99999001070000103
  エクセルでの表示 : 2.3716E+16
  → これは表示上の問題でVBAで扱う場合問題ではないが、クエリ結果をシートへ書き込む場合に問題となる

 「 対策 」
  ・頭に ' を付加し文字列とすれば問題ない
  ・VBAからシートへ書き込む場合に注意する程度


3.EXCELでは15桁以上の数値は扱えない(EXCELの15桁問題)
  99999001070000103
  上記のよう入力すると、以下のよう15桁以降は0に置換されてしまう
  99999001070000100

  これは知る人ぞ知る、IEEE754、問題。
  これがよく言われるEXCELで計算結果が正しくない=誤差が生じる問題として話題に上がる。
  またクレジットカード番号(16桁)を扱う場合も問題になる。

  詳しくはwikiを参照いただきたいが、EXCELはIEEE浮動小数点数演算標準(IEEE754)を仕様として取り込んでいる。
  よって15桁以上の数値は扱えない(仕様のため回避は不可能

 「 対策 」
  ・今回の場合は文字列とするため ' 付加すれば問題なし
  ・よってVBAからシートへ書き込む場合に注意する程度


4.CSVをEXCELシートへ取り込む際も、数値と文字列問題が発生する
  EXCELの機能にあるインポートをマクロ記録すると以下のコードが生成される

    Sheets(xxx).Select   '出力先シートxxxをアクティブにする
    ActiveSheet.Cells.Clear          '出力先シート内をクリア

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & CsvLocation, Destination:=Range("$A$1"))
        .Name = "bank"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 932
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

下から4行目、CSVの3番目の要素を文字列としてインポートしている。これはセルの書式設定で文字列と指定した事と同じ。
これは前述したシートBの請求番号(数字17桁)であり、シートには問題なく17桁全桁表示される
※エクセル上では表示書式=文字列が有効になるため

しかしこのシートのデータをSQLで使うため、かつ、この3番目の要素をjoinキーとしてすると、前述の2,3、の問題が発生する。

「対策」
 ・CSVをEXCELシートへインポートする際、自作コードを作り必要な列へ ' を付加する
 ・別シートを作り各セルを参照させ、当該列の参照に "'"& をつけEXCELに文字列であると宣言する





EXCELとVBA上のADOを考えるとき、渡されるデータは通常のDBなどにはないEXCEL独自の仕様を踏まえ、どうデータが渡されるか、若しくはデータがシート上でどう表現されるか、EXCELの仕様(制約)を考慮配慮する必要ある。
※ADOが受け取った生データを見ることはできないので、理詰め+勘で対処する

だが上記問題は、見た目数値だが文字列として扱えば問題は発生しない。
しかしそのためには物理的に頭に ' を付加し、EXCELに文字列として認識させねばならない。
VBA+ADOではセルの書式設定は無視される、と考えておけば良い。

但し別の考慮点も

今回使用したドライバではそのような動きをしたが、他のドライバでは別の動きをする可能性もある(2015年現在では{driver}は使えないので問題ないと思うが?)。するとドライバに応じた対策が必要になるケースも考慮する必要あり。

========================
 EXCEL+VBA+ADO(SQL) 4つの問題点 END
========================




制約(EXCELの仕様)はまだあるので引き続き記す。



5.jetエンジンは64bit環境サポートしていない
  Windows10が発売されている2015年現在においても、VBAは20年前のVB6と何ら変わりない
  VBA=VB6のようなもの(VBAはVB6のサブセット)=古すぎ終わっている言語。

  結論 : jetなど化石なので無視すればよいだけ。
  余談だが、最新ライブラリのコネクションをウォッチするとjet表記を見て取れる。不気味だが詳細は不明のためスルーする。

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\xxx\xxx.xlsm;Mode=Share Deny None;Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engi"

 Windows10上でEXCEL2013を使用するような2015年の現在、コネクションのプロバイダーは、Microsoft.ACE.OLEDB.12.0、を使う。 参照設定は以下。

 
※Driver={Microsoft Excel Driver (*.xls)};は使えない=忘れてしまえば良い
 32bitWindowsでは検証していないが、64bitWindowsでは使用できないため、もう使えない、そう考えておけばよい。


まだあるEXCELの仕様(制約)の例
以下は今回問題にならなかったが、EXCELにはこういった仕様もある


6.EXCELがデータ型を勝手に決定してしまうためnullが返される
  これはEXCELドライバ(なんのドライバだ?)の仕様。
  https://support.microsoft.com/ja-jp/kb/194124
  http://www.f3.dion.ne.jp/~element/msaccess/AcTipsExcelLinkDataType.html
  リンク先の情報はかなり古いが、20年前のVB6のサブセットであるVBAの場合、決して古い情報とは言えない。
  リンク先のデータアクセスはDAOだが、もしや後発のADOも仕様を引き継いでいるのでは?!と一応情報を載せた。





=======
 その他の問題 複雑なプログラムであるエクセルに重いバグが入っていました
=======

エクセルBOOKのバグに遭遇。一旦バグスイッチが入るとそのブックは破棄するしかない。

下記例はごく単純なコード。
俗に言うEXCEL VBA初心者のコードで、直接セルを参照・設定し処理している=極悪コード=業務で使ってはいけないコード。

だが、2千件(13列)と300件(6列)のouter joinクエリ+画面貼り付けを20秒で完了出来る。

    ・
    ・
 'ADO
 Set cn = New ADODB.Connection
 cn.Provider = "MSDASQL"
 cn.ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
    & ThisWorkbook.FullName _
    & ";Extended Properties=""Excel 12.0 Xml;IMEX=1;HDR=YES;"""
 sql = wsSql.Range(SQLCell).Value     '当該セルよりSQL文を読込

 cn.Open
 Set rs = New ADODB.Recordset
 rs.Open sql, cn, adOpenStatic    'open&sqlが実行される

 '新シート作成(現在のシートの最後に追加)
 Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = Range(NewSheetNameCell).Value
   
 '見出し行
 For i = 0 To rs.Fields.Count - 1
     Cells(oRow, i + 1).Value = rs(i).Name    '・・本処理中にexcelのセルを直参照している(極悪コード)
 Next

 データ行
 j = oRow + 1
 Do Until rs.EOF
     For i = 0 To rs.Fields.Count - 1
         Cells(j, i + 1).Value = rs(i).Value    '・・本処理中にexcelのセルを直参照している(極悪コード)
     Next
     j = j + 1
     rs.MoveNext
 Loop
    ・
    ・

結果行2000、結果列19列の、left outer join 。このクエリ実行とシート貼り付け時間は約20秒弱。
しかしEXCELにてシート編集やセル編集VBA編集→実行を行っていると、ある時点でバグスイッチが入り、20秒で完了した当処理が300倍程の時間がかかるようになった。(要するに終わらなない)

こうなるとBOOKは壊れた。そう考え新しい真っ新のEXCELを作成し、シート、VBAを貼り付け、作り直すとまた20秒で処理が終わる。しかしまたバグスイッチが入るともう駄目。そのBOOKは破棄するしかない。
このBOOKバク回避に、とにかく考えられるあらゆる事を行ったが(シートを最小限まで減らすに始まり・・・)このバグは回避できなかった。


そこからが至難の連続が始まる。


VBAでは使用するEXCELシート全要素を、全て配列か変数に代入し、それらのみで本処理を書き、最後に配列toシート(range)、と言うプログラミングを行わないと駄目、そう考えそう実施する必要がある。
新しいofficeパッチで問題が出る可能性もあるので、売り物にする場合はそのよう作るしかない。

これは 次回記事 で コツ として記します。






( まとめ )

こうやってEXCELの各種仕様(制約)を書き出すと大したことではなく、回避も容易。しかし全く知らない状態から数々の問題を解決しながら理詰めと勘を駆使し進むことは、これは非常に大きな問題であり発見に時間がかかり、参った・・という感想しかない。




次回パート3は最終回、なるべく纏めた情報を記したい。実際のVBAコードもUPする予定。


パート1はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする(第1回)
パート3はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする(最終回)






2015年8月4日火曜日

EXCEL2013でSQL、EXCELシート同士をクエリする(第1回)

EXCEL2013での話


< お題 >

口振の引落結果/コンビニ振替の支払結果/口振依頼データ作成/CVS振込票データ作成/全顧客の情報管理

これ等を全てをエクセルのブックだけで管理しているが、毎月2度発生する請求処理が大変。これは顧客一覧と未払者(銀行WebよりDLしたCSV)を突合し、未払額を上乗せした当月請求額を算出しなければならない。
処理としては該当する顧客を探し足し算で終わりなので、全てEXCELと手作業だけで行っている。
しかし・・、顧客は数千人、しかも毎月増え続けているので手作業での突合がとても大変。随時顧客のメンテナンスもある。よって最重要以外の処理を手順書を作ってパートさんに説明するが覚えさせるのに一苦労。そしてミス多発。




よくある話です。

私たちからすると、そんな情報や処理をエクセル+手で管理しようとするから大変になる=当たり前の結果、な訳でDBとC#があれば一瞬で解決でしょ!!




と考えるのが普通。
が、色々と制約があり社内の規定によりPCへのプログラムインストールは一切禁止。ACCESSさえ導入不可。
要するに何もインストールせずエクセルとSQL(VBA)のみを使い問題を一瞬で解決させる、そんな夢?のような話。

が、sqliteやMSのcompact DB(VSのサポートがVS2013以降なくなったが)ならインストール不要。
C#もXCOPY配置でOK??必須.NETフレームワークはMS製なのでWindowsUpdateで取込まれるから問題なし。

結論から言えば、インストール不要DB+インストール不要C# EXEを使えば問題なく対処できる可能性は高い。





しかし・・・そんな人気のない仕組みを使う=不具合で嵌ると抜け出せなくなる可能性大。ただEXCEL(VBA+ADO)も情報は非常に少ないが、どうせ嵌るならexcelの方が面白そうなので、excelで解決する手段を選択。その方がお客にも説明し易いからね。

という事で!エクセルだけを使い、他のDBなど一切使わず、エクセルシート同士をSQLでクエリし、業務を行う、ですね。




Googleで「EXCEL SQL 」とググると簡単に出来るらしい。
方法はエクセルの裏にあるVBAでADOを使うとエクセルのシートをテーブルとして扱え、すると普通にSQLが書けるので実行させ、結果をとあるシートへ貼りつけるだけ。なんだかとても簡単そう。

と思ってやってみたら、これがとんでもない!!!
joinキーがちょうどEXCELの数字制約に引っかかるケースだったため、他にもエクセルの制約に引っかかる箇所多発!最終形に落ち着くまでが非常に大変!!
結局調べ+試行錯誤で丸々1週間。SQLServer使ってSQL+C#を書けば一瞬で終わるのに、何故こんな苦労を・・・?。結果もうヘトヘト。

それとnet上に多々あるエクセル+SQL解説はとても素晴らしいHPもあるが、どうも教科書的な解説で美し過ぎる!必要なのは裏のキタナイ部分であり、発生する問題解決事例なのだか、、それ等は皆無。従って残念ながらほぼ参考にならなかった。もっとも今回EXCEL+VBA+SQL(ADO)で苦労したが、C#+DBを使えば一瞬で解決するので、やはり一般的でない事はするものではない、ようです。実はEXCEL VBA関数を応用しても出来ますが・・第3回に記す。





普段の開発?とは異なる点

・とにかくエクセルには数々の制約があり過ぎる
 内部で勝手に行われる数字の自動処理が非常にやっかい
・エクセルシートは自動処理され過ぎるため普通にjoinさせるだけのSQLでも苦労する
 ドライバからADOに渡される生データを見ることはできないので、ホボ勘で勝負するしかない
・ADOが古すぎる
 OSはWindows10だが20年前VB6時代に使っていたado、古過ぎ

・VBAは、、、久々のVB6>VBAだが素晴らしい!!
 VB6がなければ現代のプログラマ人口は1/1000?
 感動的な適当さでプログラムが組め、物凄く素晴らしい事を再確認!!
 VB6はソフトウェア業界最大の発明では!?




前置?が長くなったので続きは次回。 具体的な問題点などUPします。




パート2はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする(第2回)
パート3はこちら → EXCEL2013でSQL、EXCELシート同士をクエリする(最終回)








============
  VBAについて
============

上記ではVBAを褒めましたが、それはVB6を褒めただけ。そのVBAはVB6のサブセットのようなモノ。VB6は20年前に流行った言語(VBAは未だ生き続けているが)。よって2015年現在VBAは学習教材としては 「 最悪! で 極悪! 」

素人が遊びでやるならOK?ですが、VBAを使ってソフトの学習をしようなどと決して!考えない方が良いです。こんなに楽でいい加減に組んでも動く言語は他になく、もしこれで覚えてしまうと他の言語で超苦労、、というよりプロと言われる人々の中にも無知人間が多数存在し、その無知が作った穴だらけのシステムのせいで(特にSQL!)情報漏えいしたと騒がれている時代。楽なVBA組んで喜んでいては一生Web系アプリなど、危険過ぎ組めません。やるならC#のコンソールアプリかWindowsフォームアプリでも組んで学習しましょう。

また初心者がいきなりWeb系アプリを作ることもお勧めしません(スマホ用アプリは別)。無知なときに作ると脆弱性多々でサーバー乗っ取られるか情報漏えいして会社クビになりますよ!まず普通に言語使えるようになり、十二分にセキュリティーの勉強を行ってからWeb系アプリ、組みましょう。Web系アプリは最新技術満載でどんどん新しく進化しているので面白いです。JavaScriptなども古いですが面白く怖く、ですね。スマホアプリ開発も新時代に入ってきましたし、スマホアプリなら脆弱性などさほど気にせず組めるので良いかも?知れません。

でもそろそろ・・ロボットの時代?かも知れませんね。労働人口減少?怖いですね・・。
============





2015年6月25日木曜日

OneDriveとOneDrive for Business どちらが高セキュリティー?使い分けと違い


以前から使っていた OneDrive
最近office365を購入したので新たに OneDrive for Business が使えるようになった

私はどちらのサービスも同一メルアドを使っているため、
ログイン画面のログイン先が不明確だったのと、この2つのクラウドスペースをどう使い分ければよいか不明確だったが、使い分けができるようになったので、UP




同一のメルアドを使っていると下記の画面が出現する
しかしログインに関しての疑問は下記のページに全てが書かれている

OneDrive : 「マイクロソフト アカウント:Microsoft Account」でログイン
OneDrive for Business : 「職場または学校のアカウントにサインイン(MSの言葉だとこういう)



上記ページではなく、いきなり 「Microsoftアカウントか職場or学校のアカウントでログイン」 という選択を求められるページが出てくることが多く、最初戸惑ったが・・・

このページをスルーしていたので理解するまでに時間がかかったが、要するにそういうこと
ログインに関しては、、という事です。








使い分けに関しては、現時点で2段階認証をサポートしているのは、なんと! OneDrive の方
よって高セキュリティーなクラウドドライブは、OneDrive、と認識

逆に OneDrive for Business は、パスワード認証しかないので危ないからクラウドへファイルUPしてません

何か逆では?ですが、OneDrive for Businessはazureと密につながっているので、社内ADのログイン情報を共有できる(詳しくは知らないけどね)
だからそう言うこと???

やってみていないのでよくわからないがそう言うことなのでしょう。

(使い分け、まとめ)

一般人がクラウドストレージとして使うなら、現時点では2段階認証をサポートしているOneDrive の方が断然安全

ただし、二段階認証にはスマホ+アプリ必須



このアプリ出来がよく、許可をタップするだけで認証完了する
あまりに出来が良いので顧客のシステムに取り込みたい、真面目に思う



わざわざアプリ開かなくてもスマホのお知らせ画面?に出てくるから、認証できる
凄楽
やっぱ真面目にこのアプリというか認証システム、ウチのシステムに取り入れたい・・・
MSさん、無料で使わせて!!
 



















おわり

Visual Studio 2010, 2013, 2015 RC と SAP Crystal Reports


2017.8の最新記事あり → https://oyaji-pgm.blogspot.jp/2017/08/20178-visual-studio-2017-2015-2013-2012_13.html


SAP Crystal Reports = クリスタルレポート = クリレポについて、
Windows 10 Build 10074 を開発用ノートへクリーンインストールしたので、再度クリレポ環境を作成すべく、下記。


これまでのSAP Crystal Reports記事は、ごたく?が多かったので今回はストレートに。



1.Visual Studio 2008, 2010, 2013, 2015 RC で SAP Crystal Reports クリレポ を使いたいなら・・・

 A.下記メインページへ
 http://scn.sap.com/docs/DOC-7824

 B.現時点の最新版は、Support Pack 14 (v.13.0.14.1720)
 wiki(Fixed Issues)を見ると、Visual Studio 2010, 2013, 2015 RC、で使えるらしい。
 ※各VSの最新SP(パッチ)は予め当てておくこと


 C.インストール
 開発環境(Visual Studio 2010, 2013, 2015 RC)で最新のクリスタルレポートを使うなら Install Executable の Support Pack 14 をDLしてインストール。※ランタイム必須=別途DL → インストール

 必要ならマージモジュール、ランタイムをDLすればよい。(客先PCへインストールする等)

 D.ランタイムインストール
 インストールが完了すると、下記画面より64bit(私は64bitWindowsのため)ランタイムをインストールしろ!と出る。
 が!!!このまま先へ進むと、私の環境ではラインタイムインストールの最後でエラー発生。




この後、SAPから当該ランタイム単独でDLしインストールする方法を・・・
試そうと思ったら、別の理由でWindows 10  自体がクラッシュというより壊滅的に壊れたため試せなかった。

インストールまとめ)

1.SAP Crystal Reports, developer version for Microsoft Visual Studio の最新版(対象VSか確認)をDLインストール※VSは最新化しておく
2.インストールの最後の「続けてランタイムをインストールするか?」のチェックは外す
3.ランタイムは別途DLしインストールする(必須)


 



D.Windows10復旧後、実際にVisualStudio 2010 で、クリレポを作成&実行
 問題なく作成実行できた(VS2010.SP1 .net framework4.0 C#)
 ※他のバージョンVSでは試していない
 ※VS2015RCを試しにインストールしたが、あまりに不安定すぎアンインストールした
  お楽しみは後ほど・・、という事だね






※私のソリューションにはVSインストーラーのセットアッププロジェクトを作ってあるが、そこの必須コンポーネントとしてクリレポランタイムにチェックを入れている。
実際にセットアップを行うと・・・エラーが出た。

面倒+必要ないので調べません。
一応内容。VSのセットアップがDLしたランタイムは、前述の最新ライタイムと同一モノだったが・・・


LOG)
(2015/06/25 19:44:39) Downloading 'Crystal Reports for .NET Framework 4.0\CRRuntime_32bit_13_0_14.msi' from 'http://downloads.businessobjects.com/akdlm/crnetruntime/clickonce/CRRuntime_32bit_13_0_14.msi' to 'C:\Users\xxxxxx\AppData\Local\Temp\VSD3AC7.tmp\'
Download completed at 2015/06/25 19:44:39
Verifying file integrity of C:\Users\xxxxxx\AppData\Local\Temp\VSD3AC7.tmp\Crystal Reports for .NET Framework 4.0\CRRuntime_32bit_13_0_14.msi
WinVerifyTrust returned 0
File trusted
Error: ファイル 'C:\Users\xxxxxx\AppData\Local\Temp\VSD3AC7.tmp\Crystal Reports for .NET Framework 4.0\CRRuntime_32bit_13_0_14.msi' は最初に発行されて以来変更されています。

・・・DL元のフォルダ名にclickonceとあるのが気になるが・・・ただの気のせいだろね??









余談)

 過去のサービスパックはリンクが切られwikiしか参照できない。が、実はDLできる。
下記など、最後の / 以降の ~.exe を変更すれば過去の各種モジュールがDLできるよう。
※HPの英語コメントにそれらしいことは書いてある

ex.13.0.4のサービスパックなら CRforVS_13_0_4.exe などと入れてみる
http://downloads.businessobjects.com/akdlm/cr4vs2010/CRforVS_13_0_4.exe




2.SAP Crystal Reports, version for Visual Studio

SAP Crystal Reports, version for Visual Studio フォーラムというかコミニティー




以上







おわり


2015年6月13日土曜日

同じ機能ばかりの、office365 Exchange、office365 SharePoint 、ローカルのoutlook、これをどう使い分けるか?


この件はずっと不明だった。何故なら小企業はオンプレミスにMSのアプリサーバー(Exchange、SharePoint )など使ってないから。だからoffice365を使い始めたけど、同じ機能ばかりある。これら一体どう使い分ければ良い?


全く不明でしたが、なんと!MS社員が書いた凄まじく素晴らしい資料を発見!
MSでこんな良い資料、初めて見た気がする。良く纏まっていて素晴らしいです!


こういう人材がいるって、やはり大企業は何もかも違いすぎますね・・・最初から勝負にならんし差が開くばかりな理由が見えますね・・・。




  よく似た Office 365 の機能の使い分けの運用 - マイクロソフトの場合




機能
全社
拠点
チーム
個人
Exchange  メール
 
 
Exchange  パブリックフォルダー
 
Exchange  アドレス帳
 
 
 
Exchange  予定表/施設予約
Exchange 連絡先
 
 
 
SharePoint 個人用サイト
 
 
 
OneDrive for Business
 
 
 
SharePoint 予定表/施設予約
SharePoint 連絡先
SharePoint ポータル
SharePoint ファイル共有
Lync 連絡先   
資料よりデータをお借りしました。よってこの表は copylight microsoft corporation.





同様、資料をクラウドか、オンプレミスか、どちらに置くか。参考資料。

  パブリック クラウド サービスを利用する際のセキュリティ対策として考えるべきこと



同じく

  ビジネス情報の分類と保護 - マイクロソフト IT 事例






以下は、こちらも常によく分からんかった件(小企業の私には)

MS資料
  連絡先とアドレス帳の基本を理解する

連絡先およびアドレス帳とは

Outlook では、連絡先を使用して、同僚や友人に関する情報を管理できます。連絡先には、名前、勤務先、電子メール アドレス、電話番号などのさまざまな情報が保存されます。

アドレス帳は、連絡先の電子メール情報用に主に使用されるツールです。アドレス帳を使用することで、連絡相手の電子メール アドレスを簡単に検索し、電子メールの受信者として選択できます。












おわり



Office 365で会社法人のホームページHPを公開できるのか?(パブリックWEBサイト)



 コロコロ変わっている?様だが、結論を言うと「出来ない」と言うか「出来なくなった」
(2015/6/13現在)


Office 365 でのパブリック Web サイトの使用

Office 365 SharePoint Online パブリック Web サイトの変更点について


新規で作成することがNGになったようで、既に作っていた人はあと2年くらい使えるらしい(ズルい)
作ろうとすると、外部レンタルサーバー屋たった2社(アメリカの業者)を紹介される。
日本語対応してるのはwix。

結局メールでoffice365の料金を払い、会社HP公開でレンタルサーバー屋に料金を払う。2重払いになってしまうよう。



ただ既に会社HPは公開してるだろからそのままにしておけば良い。
どうせ独自ドメインをoffice 365で使うために、レジストラのDSNをいじっていると思うので問題なく運用できてると思います。

ただ!wixはウィザードでHPが作成できるよう(有料版)。これからHP作りたい人には良いかも。年払いで半額キャンペーンもやっているし。ただ2年後以降幾らになるかは不明ですが、どうせ外資だし奴らのやることは信用できんです。



以下はそのままな話。

組織外の人とサイトまたはドキュメントを共有する









おわり


outlookの連絡先とアドレス帳の違いとは?


MSのアプリ(ソフト)は多機能すぎて似た機能が沢山ある。

掲題もその一つ。何が違うの?理解できません。

そしたらMSの情報ページを発見。

備忘と情報公開。



連絡先とアドレス帳の基本を理解する



大したことは書いてないけど、違いは理解できる。

日本マイクロソフトの社内でも、連絡先とアドレス帳を使い分けている様。

簡単に言うと下記の様にやっているらしい。



機能全社拠点チーム個人
Exchange  メール  
Exchange  パブリックフォルダー 
Exchange  アドレス帳   
Exchange  予定表/施設予約
Exchange 連絡先   
SharePoint 個人用サイト   
OneDrive for Business   
SharePoint 予定表/施設予約
SharePoint 連絡先
SharePoint ポータル
SharePoint ファイル共有
Lync 連絡先   
↑ この表はMSさんのもの。copylight microsoft corporation.



MSのサーバーアプリを知らない人は上記名前を見て混乱すると思うけど、

ようは連絡先は個人用。

アドレス帳は会社共通のもの(※特殊なサーバー入れてない人には当て嵌まらない)。



上記※にあるよう、中小零細企業は、普通連絡先など全社共通化などされていないと思うので、

結局はどっちも似たようなものでしかない。

但し、連絡先には住所等、詳細情報をいれておけますね。

アドレス帳はただのメール羅列してある一覧。



結局特殊なサーバー入れていない会社や人は、同じものでしかない、ですね。






2015年6月11日木曜日

gmailをmboxを使わずoutlook2013へインポートする方法(超簡単・・)


前回、outlook2013からoffice365へ過去メールを含め全てを移行する方法、に続き、
今回もメール関連、gmailをエクセルexcel2013に取り込む、要件があったので、備忘録。


すると掲題「gmailをmboxを使わずoutlook2013へインポートする方法」を行う必要があり調査。

調べるとgmail(メール)をアーカイブ → mboxをDL → Thunderbirdに取り込む → 色々やる・・(面倒)
など行っているようですが、インストールやら設定やら、またインポート先にoutlook2013を指定できない、など面倒山積み。
と言うより結論を言うとできないらしく、、gmailのmbox → outlook2013=無理。そんな感じ。なら時間の無駄ですね。
と言うか、何故わざわざそんな面倒な事をしているのか?意味不明?




とりあえずgmailの受信トレイにあるメールを取り込めれば良いのでは?
それで良いなら、無茶無茶簡単。
mboxなど忘れ、popで取り込めばよい。imapでも良いと思います。
imapなら送信メールも取り込めますね。ただimap+outlook2013は試した事がないので他の方にお任せします。



掲題

 「gmailをmboxを使わずoutlook2013へインポートする方法」

解決策)

1.gmailのpopアクセスを有効にする(若しくはimap)
2.outlook2013にgmailアカウントを追加
3.送受信ボタンをクリック
4.終わり

ごく簡単に終わりです。。。

※ちなみに既存のoutlook2013にあるメールアカウントの受信トレイへ、上記popで取得したgmailを入れたいなら・・
 windowsなのだからドラッグドロップすれば完了。アホみたいに簡単です。
※Windowsのエクスプローラーのよう、outlook2013の左側に見えるフォルダはエクスプローラーと同じ。
 だからドラックドロップが使える。




問題あるとすると、送受信ボタンをクリックしても1回に250件しかメールが読み込まれない点。
仮にgmailが1万件あるとなると・・・
10000/250=40回送受信ボタンをクリックする必要がある=面倒。


が、その対策も簡単。
パソコンのスリープを無効にし、outlook2013の、

  ファイル→ オプション→ 詳細設定→ 送受信

を開き、自動送受信タイミングを、1分おき、に変更。
あとはPCつけっぱなしでほっとけば勝手に受信してくれます。



上記1万件の40回クリックが1回1分だから40分。
∴最低40分あれば1万件のgmailがoutlook2013の受信トレイに取り込まれる。


 以上で問題解決。





更に今回私が必要だった、「gmailをエクセルexcel2013に取り込む」
これはgmailを取り込んだoutlook2013からCSVにエクスポート→ これをエクセルで開いて完了。






参考画像)


gmailのpopアクセスを有効にする (この画面は、右上の*ボタン→設定から行く)








outlook2013側でgmailアカウントを登録(pop3)

(推奨)下記設定は、事前にgoogleの最新helpを参照 → IMAP と POP3 の開始方法

下記画面は、
ファイル→アカウントの設定→電子メール→新規→自分で・・・設定する(手動設定)→popまたはSMTP→
で表示される







!!! 以下 要注意 !!!
 
1.サーバーにメッセージのコピーを置く、がチェックされている事を確認
2.サーバーから削除する、チェックを外す

outlook2013で受信すると、そのgmailメールがgmaiサーバーから削除される。
嫌な方は確認要注意!

・削除済アイテム」から削除されたら・・・
 これも嫌なので私はチェックなど外す。今回、gmailをoutlook2013へ取り込むのが作業なので削除不要。





この状態で次へをクリックすると(3つ上の画面)、アカウントの設定テストを行うが(チェックが入っているので)、エラーが発生する(2015年6月現在)

パスワードが正しくない旨を返される








これと同時に当該gmailに「ログイン試行をブロックした」とのセキュリティー警告メールが送信される

現在これはデフォルトの動作の様で、文章の指示通り下記赤四角内リンクをクリック




次の画面が表示されるので、オンにする。これでoutlok2013でメール送受信エラーは消える。

もしエラーが消えない場合は、上記行ったoutlook設定が悪い。設定箇所を正しく修正するまでエラーは消えない(慣れてないと結構設定ミスする事が多く時間がかかる。しっかり確認)

ちなみにoutlook2013はリスクの大きいアプリとgoogleは言っている。だから下記オンにしないとパスワードエラーとしてメールサーバーへアクセスさせないようだ。




上記でオンにしたので、確認のメールが当該gmailへ送信される。
再度言うが、gmailの取り込みが完了したら上記が画面でオフにしておいた方がよい。
但し今後もoutlook2013でgmailを送受信したいならそのままにしておけばよい。





最後に。
pop(pop3プロトコル)でメールを受信しても、pop3の仕様上受信メールに入っているメールしか受信しない。全部受信したいならIMAPで実験すること。
私はIMAP実験が面倒だったので取り込みたいメールを全てgmailの受信トレイに入れてからローカル(PC)のoutlook2013へ取り込んだ。







 おわり・・・の前に、後処理。

outlook2013で送受信したくないメールアカウントがあれば(私は上記取り込み後のgmail)、送受信させなくする。
ファイル→オプション→詳細設定→送受信→編集→
送受信したくないアカウントをクリックし、「この送受信グループに選択されたアカウントを含める」
このチェックを外せば送受信してもそのアカウントはメールサーバーへ問合せに行かなくなる。
いわば、休眠している感じ?

もしoutlook2013に登録したgmailアカウントが不要になったら削除すれば、上記のよう面倒をしなくてよい。
削除は簡単、outlook2013のメール画面左にある追加したgmailのアカウントを右クリック→削除。
これでpcのローカルに保管されたデータも含め全て削除される。

今回の私はgmailをエクセルにインポートすれば不要なので、outlook2013に登録したgmailアカウントは削除した






と言う事で、 おわり・・・






2015年6月10日水曜日

outlook2013からoffice365へ過去メールを含め全てを移行する方法


outlook2013(他バージョンも同様と思われる)からoffice365へ過去メールを含め全てを移行する方法は、以下1メールアカウント単位に移行する方法。
社員一人=PC1台使っているとすると、PC1台つづ行う行う方法。


社員を段階的に移行する要件などには、社内ExchangeServerサーバーを使ったハイブリッド展開するしか方法がなく、ExchangeServerを社内で立ち上げ運用している会社は小企業では皆無?
まさかそのために社内にExchangeServerを導入するのは普通ではない。何故なら、AD環境+FW(office365やazureが必要なポートを開く)+ExchangeServer自体を導入(CALは社員分必要ない)+サーバー1~2台。
これだけの環境を揃えると、設計料、導入費用、ハード購入費用、テスト費用、ExchangeServer移行費用、全費用を合計すれば500万~1000万ほど必要。よってこれは普通でないと思われる。

と思うので、1アカウント(PC1台づつ)、かつ、当該ドメインを使っている社員全員分の作業を一括(土日など?)で行うしかない。以下はそのための手順。




本題

「outlook2013からoffice365へ過去メールを含め全てを移行する方法」

結構悩み2日ほど要したが、非常に簡単だった。

答えは以下MSのページにある。

Outlook を使ってメール アカウント間で情報を移動する





補足)

上記を行うには、まず初めにoffice365で登録したアカウントのメールアドレスをPCのoutlook2013で送受信出来るようにする必要ある。


でも、それはとても簡単。

1.outlook2013の下記画面の電子メールアドレスにoffice365で取得したメールアドレスを入力
2.次へをクリック
3.すると自動で情報を探してくれる
4.見つかるとパスワード入力する画面が出るので、そこにoffice365メールのパスワードを入力
5.するとPCのoutlook2013へoffice365のメールが登録される
6.office365のメールがPC上のoutlook2013で送受信可能になる。

outlook2013からoffice365へ過去メールを含め全てを移行する方法

ファイル→アカウント設定→アカウント設定→電子メールタブの新規→上記画面表示される





補足)

office365アカウントをoutlook2013に登録出来れば、outlookメール画面左側の階層にoffice365のアカウントが表示される。これが出来ればあとは簡単。一言でいえば、エクスポート → インポートを行えば完了。

手順は上記MSのリンク先を参照し、その通りに行えばよい。



注意点)

エクスポートするのは、今まで使っていたPCのメール・連絡先等。

インポート先を間違えて選択すると、そこへエクスポートしたデータが取り込まれるので、インポート先を間違えないのが必須と言うかコツ。




以下参考画面。ちょいと見づらいが・・・下記は私の場合。

※office365で2アカウント取得したので、その両方を既にoutlook2013に登録してある
※また元々PCで2つのメールアカウントを使っていたので、今まで使っていたメールが2つある

紛らわしいですね。
普通は既存のメールアカウントが一つ、登録したoffice365のメールアカウントが一つ、ですね。

outlook2013からoffice365へ過去メールを含め全てを移行する方法





この画像はoffice365のメールアカウント2つをoutlook2013に登録した状態。
この状態になればoffice365のメールがPC上で送受信できる。

outlook2013からoffice365へ過去メールを含め全てを移行する方法








以上で、過去メールが全てクラウド化=office365化の完了です。

※余計なことまで言えば、ドラックドロップでフォルダを持っていくこともできるようになる。




と言う事で時間がかかった敗因は、MSの当該office365移行手順URLを見つけられなかった、でした。

MSは情報ありすぎるからね。中々探せません。

・・・と言うより

outlook2013にoffice365のメールアカウントが取り込めた状態で、エクスポート→インポートすればOKだろ!くらい思いつけよ!と言う感想ですね。





別の補足)

office365で独自ドメイン(会社のドメインなど)を使うには、上記より先に行う、かつ、上記とは別の要件。
でもそれらはoffice365の管理画面からドメインを選択し、ドメインを管理している(購入した)レジストラの管理画面でDNSを書き換えたら(office365へ向ける)OK!

※DNS情報書き換えNGのレジストラだとoffice365でその独自ドメインは使えない=他のレジストラへドメイン移管するか、そのドメインを捨て、新しいドメインを買いましょう。

と、とても簡単。
分からなければ業者に頼みましょう。

私なら綺麗なおねえちゃんのいる店でゴチしてくれたらやってあげますが。
但し23区内か横浜か浦和近辺かな?ん?やっぱ銀座がいいかな~

って、完全にオヤジ化してしまったようです、私、笑








2015年6月8日月曜日

office365の問題点(アホ話)


ツマラン話で恐縮ですが・・・アホ話を一つ



数度チャレンジして挫折したoffice365(昔はBPOS?)

そろそろMS側の環境も揃い良い時期かも?と思い、色々調査しオンプレミスから365へ過去メールを含め全面移行出来る所まで調査が進み、あとはEnterpriseではなくBusinessを購入!と言う所まで辿り着たのだが。



私は一人でメルアドを30個ほど持っている。
そして移行対象の主ドメインもVPSのため好きにメルアドを増やし10個のメルアドを作ってある。

そして考えた。
「ん?office365って、1アカウント=1メールだよな?んー、そりゃそうだよな・・・」

とここまで考えて初めて気が付いた。
「ぐぉー、、メルアド10個移行したら、月額540円*10=5400円になるではないか!!」




アホですよねー、調べつくしあと5%で完了と言うことろで、最重要と言うか、当たり前の事に初めて気が付くなんて!!!




結局メルアドを削減するか、主要な2-3メールのみoffice365でクラウド化し、あとは適当なドメイン買ってそこで運用するしかないですね。って、同一ドメインを使い、メールサーバーを全く別の業者2つに置くって不可能。

結局一度office365を使い始めたら二度と元には戻れないですね。それって凄まじく凄いビジネスモデルです。真似しないと・・・ですね。


で、まだ悩んでます・・・







2015年5月28日木曜日

b-PAC+PT-9700PC(PT-9800PCN)で、COM相互運用型エラー、エラーは出ないが印刷も出ない、COMエラー発生し印刷できない


久々のプログラム制作

今さら感あるバーコードですが、JAN(EAN)とCODE39を印字しようと調べるとブラザーのラベルプリンターが他社の1/10程の低価格。またB-PACと言うSDKがあり自プログラムから印刷可能。早々PT-9700PCを購入し、そのB-PACを使ってみると・・・

嵌る・・・。プリンターが全くうんともすんとも言わない。

環境はWindows7の64bitとWindows8.1の64bit+VS2010、VS2012、VS2013、の6通り。
全て試したがエラー発生せず。エラーが発生しない=こう言うのが一番困ります。




ちなみにブラザーのラベルプリンターの価格は・・
「今どきUSB接続しか出来ないPT-9700PCが約2万、LAN接続できるPT-9800PCNは何と倍の4万強」高過ぎですね。
一応プリンターやSDKが外れの可能性もあるので安い方、PT-9700PCを購入。







エラーその1

「相互運用型 ‘bpac.DocumentClass’ を埋め込むことができません。代わりに適用可能なインターフェイスを使用してください」。

<対処方法>
これはググると答えが沢山出てくる。「b-pac エラー」などで。

このサイトから情報登録するとSDKをDL出来るが、ここでDLしたSDKをインストール。
(私はVer3.1.003の64bitバージョンDL ← これも間違いの原因=その2で悩んだエラーの原因)
するとVisualStudioのプロジェクトにCOMの参照を追加できるのだが・・。

現実には参照で、Brother b-PAC 3.1 Type Library(ver1.3、なんで1.3なの?)を追加するとbpacと言う参照が出来、これをクリックすると「相互運用機能型の埋め込み」と言うプロパティーがあり、これがTrueになってるからFalseにする。

この問題は5分で解決。






エラーその2

上記対処してもプリンターは全くうんともすんとも言わない。プリンターを購入するとラベルをデザインできるP-touch Editor 5.1と言うのが付いており、このソフトからは問題なく印刷できる。


<エラー内容>

なし。そう、エラーにならないのです、自プロから印刷ボタンをクリックしても何の応答もなし。テンプレートのオープン、StartPrint、PrintOut、EndPrint、クローズ、何のエラーも起こらずスルーしてしまいます。

結局半日ほど悩み・・こういう時はホボ凡ミスが原因が多い・・と、初心に戻り最初から抜けがないか手順を追ってみと発見!原因は・・・やはりクダラナイ・・


<原因>

PT-9700PC(PT-9800PCN)に適合しているSDKが何と!!32ビット版のみ!
※私は64bit windowsなので気にせず64bit版をインストールしていた。

流石元が古いプリンターとSDKです。答えはブラザーのサイトにありましたが、もっと分かり易い所に書け!と言いたくなります。



b-PACトップ → 動作環境の下の方
 


<対処方法>

1.b-PAC、64bitSDKをアンインストール(私の場合)
2.b-PAC、32bitSDKをDLしてインストール


上記行うと、32bit版SDKが、ちゃんとx86フォルダへインストールされます。





余談)
Brotherのサンプルプログラムが使うラベルのテンプレートフォルダは64bit版sdkをインストールしたと仮定しているようで、要注意!!
素直にサンプルに従い、プログラムで読み込むテンプレートフォルダ先を間違えるとまた変なエラーが発生し悩むでしょう





これで大丈夫だろうと思っていたら、今度はCOMエラーが発生。今更COMですか?めんどくさい・・・

エラーその3へ進む・・・






エラーその3

テンプレートのOPENで下記エラー発生。

<エラー内容>

------------------------------------
COMException
------------------------------------
CLSID {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx} を含むコンポーネントの COM クラス ファクトリを取得中に、次のエラーが発生しました: 80040154

クラスが登録されていません (HRESULT からの例外:0x80040154 (REGDB_E_CLASSNOTREG))。
------------------------------------

こういう濃いエラーは、ググれば絶対誰かがUPしている筈!とググったら素晴らしい方が記事を残してくれていました!
こちらの素晴らしい方のおかげで即解決 → こちらのお方
自己解決ではありませんが、最近はnetデバッグありですから。


※余談だが、最近netを見ていると2/3程のブログは嘘を書いている=本人自身が嘘を書いている事に気付いていない。かなりレベルの低い自称SEがいるのでご注意!こういう方々を雇ってしまったら爆弾抱えている状態=かなり怖ろしい・・しかも60-70%がこう言う方々。今後は面接よりペーパーテストを強烈に重視したいと思います。




<原因>

32bit版COMなので(何だか知らんがSDKが32bitだからそうだろうと勝手に解釈)、
プロジェクトのターゲットプラットフォームも32bitにしないとCOMエラーが発生する。



<対処>

プロジェクトのプロパティーにあるターゲットプラットホームをx86にする。








これ等、エラー3点の対処を行うと、バーコードが印刷された!祝


余談、最初から滞りなくやれたら「なんだこれ?こんな簡単なんだ」となるのでしょうが、システム開発ってクダラナイ事に時間取られすぎます。それがノウハウになりますが、でもそもそも論から言えば、それって違うのでは?ですね。早く明るい業界になって欲しいものです。




以上で、C#、.NET Framework4(2~4、OK!)を使い、自プロからBrotherラベルプリンターへの出力印刷が完了です。

ちなみに制作しているモノはシリアルNo+製造番号ロットNo自動採番+バーコード出力プログラムです。









<その他の注意点:別のエラー>

これも1度引っかかったので解説。

・サンプルプログラム実行時の注意点!

上記で言いましたが私の場合、P-touch Editor 5.1で作成したテンプレート、ibx、のロケーションフォルダ。32bit版sdkなので¥Program Files (x86)¥、です。

ブラザーのサンプルは¥Program Files¥となっているので要注意。このまま実行すればまた別のエラーが発生して悩みます。

※くだらないエラーであればある程、原因特定するのに時間かかる事が多いのでご注意を。




以上で解決です。

ちなみに上記サンプルで普通に印刷出来ます。







< まとめ >

1.32bitか64bitか、SDKを間違えずにインストールする
  ※印刷したいラベルプリンターに対応しているSDKを間違えずにDLしインストールする
  確認はこちら → こちら

2.SDK提供のコンポーネントをプロジェクトの参照に追加する
  ※SDKをインストールすると、参照の追加で勝手にCOMの一覧に出てくる

3.interop.bpac(COM)の相互運用型をfalseに
  ※参照設定下にあります(C#の場合)VBだとどこにあるのか知りません。

4.プロジェクトのターゲットプラットホームをx86にする
  ※PT-9700PC、PT-9800PCN等の場合、SDKが32bitだからx86にした。
  確認はこちら → こちら
  ※64bitSDKならx64かAnyCPUでokでは?ご自身でテスト下さい。

5.ブラザー提供のサンプルプログラム
  ラベルのテンプレートフォルダのフォルダ位置が正しいか確認
  ※Program Files (x86)かProgram Files 、どちらなのか、インストールしたSDK位置に合わせる 

6.コンパイル → 実行

※上記はPT-9700PC(PT-9800PCN)が32bitSDKにしか対応していないから32bit版をDLしたり、ターゲットプラットホームをx86にしている

ブラザーのどのラベルプリンターでも同様!ではないので、先程紹介したリンクから確認する事
http://www.brother.co.jp/dev/bpac/env/index.htm ← ここ!







・エラーとは関係ないが、一応、どのフレームワークに対応しているのか試す

 〇:.NET framework 2.0
 〇:.NET framework 3.0
 〇:.NET framework 3.5
 〇:.NET framework 4
 ×:.NET framework 4.5
 ×:.NET framework 4.51


どうやら4.5以上はダメ、エラーが出る。だから素直に4を使いました。
よってVS2013でも開発出来る。

 
 



・それと使用フレームワークを.NET framework 2.0(や3.0?)に変更して遊んでいるとプロジェクトフォルダーにゴミがたんまり出来る(本当はゴミではないが・・)

system.なんちゃらとかMicrosoft.なんちゃらとか・・プロ内で使ってるからMSが親切にCOPYしてくれたのかな?

BrotherのSDKもサンプルも、元は古いので使用フレームワークが2.0や3.0になっている。
従って、遊んでるとゴミ?だらけになるので注意。

その場合は、フォルダ内の見た目が悪いので、プロジェクトを削除し、新しいモノに作り直しましょう
※ソースも消さないよう、どこかに保管しとかないと後で泣きますよ

そしてプロジェクトのプロパティーでフレームワークを4.0などになっているか、確認しましょう。









一応、プログラムからテンプレートのバーコードの数値をセットして印字するプログラムソース兼テストプログラム、載せときます。どうでも良い程度のコードですが、一応。C#。

ちゃんと環境が正しく、かつ、プリンターが正常に接続されていれば、
code39バーコードのコード:C987Z6001Q(末尾に自動でチェックデジットが追加されていた=助かった)が、印刷されます。テープ幅は12mmを使用。

        const int TAPE_WIDTH = 12;
        const string TEMPLATE_FOLDER = "C:\\Users\\xx\\xx管理システム\\";
        const string TEMPLATE_FILE = "CODE39_1.lbx";
        const string TEMPLATE_PRINTER = "";
        public bool BarPrint()
        {
            string templatePath = TEMPLATE_FOLDER + TEMPLATE_FILE + TEMPLATE_PRINTER;
            bool ret = false;

            bpac.DocumentClass doc = new DocumentClass();
            if (doc.Open(templatePath))
            {
                MessageBox.Show(doc.Width.ToString(), "tape幅");
                MessageBox.Show(doc.Length.ToString(), "tape長DPI");
                MessageBox.Show((doc.Length * 254 / 1440).ToString(), "tape長");
                MessageBox.Show(doc.GetMediaId().ToString(), "メディアID");
                MessageBox.Show(doc.GetMediaName(), "メディア名");
                MessageBox.Show(doc.GetPrinterName(), "プリンター名");
                MessageBox.Show(doc.GetBarcodeIndex("CODE39").ToString(), "バーコードインデックス");
                MessageBox.Show(doc.CurrentSheet, "カレントシート");
                MessageBox.Show(doc.CutLineCount.ToString(), "カットライン");
                object[] sheetNames = doc.SheetNames as object[];
                foreach (string sheetName in sheetNames)
                {
                    MessageBox.Show(sheetName, "シート名");
                }
                // プリンターリストを取得
                object[] printers = (object[])doc.Printer.GetInstalledPrinters();
                foreach (string printerName in printers)
                {
                    //オンラインかどうかをチェックする
                    if (doc.Printer.IsPrinterOnline(printerName))
                    {
                        MessageBox.Show(printerName, "Online");
                    }
                    else
                    {
                        MessageBox.Show(printerName, "Offline");
                    }
                }
                // バーコード生成&セット
                doc.SetBarcodeData(doc.GetBarcodeIndex("CODE39"), "C987Z6001Q");
                // 印刷
                doc.StartPrint("", bpac.PrintOptionConstants.bpoDefault);
                doc.PrintOut(1, bpac.PrintOptionConstants.bpoDefault);
                doc.EndPrint(); // ここを通り過ぎると印刷される模様 
                   
                doc.Close();
                ret = true;
            }
            else
            {
                System.Windows.Forms.MessageBox.Show("プリンターがOPENできませんでした。", "エラー");
            }
            doc = null;
            return ret;
        }
    }



 <結果>

doc.Width         680      テープ幅
doc.Length        2556      テープ帳DPI
doc.Length * 254 / 1440     450      テープ長
doc.GetMediaId()       259      メディアID
doc.GetMediaName()     12mm      メディア名
doc.GetPrinterName()     Brother PT-9700PC  プリンター名
doc.GetBarcodeIndex("CODE39")  0       バーコードインデックス
doc.CurrentSheet      シート1     カレントシート
doc.CutLineCount      0       カットライン
doc.SheetNames as object[n]  シート1     SheetNames
doc.Printer.IsPrinterOnline(printerName) Brother PT-9700PC  オンライン
doc.SetBarcodeData(doc.GetBarcodeIndex("CODE39"), "C987Z6001Q");

印刷されたバーコード ⇒  C987Z6001QI
※印字してみたところ、自動でチェックデジットが追加されていた(上記の場合は I です)

それをバーコードリーダーで読込  ⇒  c987z6001qi




・元になったテンプレートと印刷結果を載せておきます

このテンプレートのバーコード、下のテープ部からはみ出てますが、はみ出た部分は無視され普通に印刷してくれます。
※ファイル名が違いますが・・でも同一テンプレートです





こんな小さな印刷物、テープ幅12mmにCODE39だから物凄く小さいです。
でも2千円のリーダーでもちゃんと読み取れます。





この写真はブラザーのテストプログラムから印字したものですが、上記プログラムからも印刷出来ますので。




という事で久々にプログラム組みましたが、やはり楽しいですね。
何時もDBしか相手にしていませんでしたが、こうやって目で見えるモノが出てくると、尚楽しいです。



 おわり