none
DAO12.0 でエクセル2010(xlsx)形式のブックの更新ができなくなる。 RRS feed

  • 質問

  • DAO12.0でエクセル2010(xlsx形式)のブックの読み書きを行うプログラムを開発しています。

    下記現象が発生し、解決できません。

    手順

    ①ある行のフィールドに値をセット

    ②セットした値をクリア("")をセット

    ③別の行のフィールドに値をセットしてもブックに反映されない。

    ④①を再度実行すると、③が可能

    原因と解決方法があればお願いします。

    開発環境

    VB2008Express

    DAO.DBEngine.120使用

    ・接続

    DAO.DBEngine.OpenDatabase(xlFileName, False, False, "Excel 12.0 xml;HDR=NO;)


    2014年4月14日 21:45

回答

  • [手順]
    ①VBプログラム上で、Button1実行で「TEST.xlsx」1行目のVALUEをCに更新。
     →TEST.xlsxを開くと更新されている。
    ②VBプログラム上で、Button1実行で「TEST.xlsx」1行目のVALUEを""に更新。(空
    文字セット)
     →TEST.xlsxを開くと更新されている。
    ③VBプログラム上で、Button1実行で「TEST.xlsx」2行目のVALUEをDに更新。
     →TEST.xlsxを開くと更新されてない。

    遅くなりすみません。私の方で同じ環境で試してみたのですが、再現しませんでした。
    すなわち、③でTEST.xlsxを開くとDに更新されていました。もちろん、①から③の操作は一連の操作として続けて行いました。
    OS等、他の環境は何でしょうか? 私はWindows 7で確認しました。関連しそうなのがOfficeのバージョンですが、私は以下の通りです。

    Microsoft Excel 2010 (14.0.7109.5000) SP2 MSO(14.0.7116.5000) 64bit

    とりあえず最新のサービスパックが当たっているか確認してみて下さい。


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

    • 回答の候補に設定 星 睦美 2014年4月18日 0:30
    • 回答としてマーク tak2014 2014年4月20日 12:25
    2014年4月17日 9:03
    モデレータ
  • 当方でも再現できました。

    環境Excel2003 + 互換パック + Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント上のVBAです。

    XLSXにネイティブ対応していないEXCELのVBAからの更新でも現象が発生するのでデータベースエンジン側のExcelドライバに問題があると推定できます。

    Option Explicit
    
    Sub Test()
        Const xlExcel12 = 50 'XLSB
        Const xlOpenXMLWorkbook = 51 'XLSX
        Const xlOpenXMLWorkbookMacroEnabled = 52 'XLSM
        
         Dim path As String
         Dim ws As Workbook
         path = GetDeskTopPath() & "\DaoTest_XML.xlsx"
         Call FileTest(path, "Excel 12.0 Xml;", xlOpenXMLWorkbook)
         
         path = GetDeskTopPath() & "\DaoTest_Macro.xlsm"
         Call FileTest(path, "Excel 12.0 Macro;", xlOpenXMLWorkbookMacroEnabled)
         
         path = GetDeskTopPath() & "\DaoTest_Binary.xlsb"
         Call FileTest(path, "Excel 12.0;", xlExcel12)
    End Sub
    Sub FileTest(ByVal filePath, ByVal isam As String, ByVal format As Integer)
         Call CreateTestFile(filePath, format)
         Call Update(filePath, isam, "KEY1", "Test")
         Call Update(filePath, isam, "KEY1", "")
         Call Update(filePath, isam, "KEY2", "Success")
         Call Workbooks.Open(filePath)
    End Sub
    
    Function GetDeskTopPath() As String
        Dim shl
        Set shl = CreateObject("WScript.Shell")
        GetDeskTopPath = shl.SpecialFolders("Desktop")
    End Function
    
    Sub CreateTestFile(ByVal path As String, ByVal format As Integer)
        Dim ws As Workbook
        Dim sh As Worksheet
        Set ws = Workbooks.Add
        Set sh = ws.Worksheets(1)
        sh.Cells(1, 1) = "KEY"
        sh.Cells(1, 2) = "VALUE"
        sh.Cells(2, 1) = "KEY1"
        sh.Cells(2, 2) = "Dummy"
        sh.Cells(3, 1) = "KEY2"
        sh.Cells(3, 2) = "Fail"
        
        ws.SaveAs path, FileFormat:=format
        ws.Close
    End Sub
    
    Sub Update(ByVal filePath As String, ByVal isam As String, ByVal key As String, ByVal value As Variant)
        Dim de ' As New DAO.DBEngine 'Microsoft Office 14.0 Access Database engine object Libraly
        Dim db 'As DAO.Database
        Dim rs 'As DAO.Recordset
        Dim sql As String
        Set de = CreateObject("DAO.DBEngine.120")
        Set db = de.OpenDatabase(filePath, False, False, isam & "HDR=YES;")
        sql = "SELECT * FROM [Sheet1$] WHERE KEY = '" & key & "'"
        Set rs = db.OpenRecordset(sql, DAO.RecordsetTypeEnum.dbOpenDynaset)
        
        rs.Edit
        rs.Fields(1).value = value
        rs.Update
        
        rs.Close
        db.Close
        
        Set rs = Nothing
        Set db = Nothing
        Set de = Nothing
    End Sub

    魔界の仮面弁士さんが書かれているように、文字列がブランクのinlineStrが1行以上あるとSharedStringの行の更新に失敗するようです。(inlineStrの位置は前後どこにあっても)
    xlsx内のシートデータ(XML)を取り出してブランクのセルを削除してみると正常に更新できます。

    現状では空文字になる場合はダミーの文字列を入れておくぐらいしか回避方法はないみたいです。


    個別に明示されていない限りgekkaがフォーラムに投稿したコードにはフォーラム使用条件に基づき「MICROSOFT LIMITED PUBLIC LICENSE」が適用されます。(かなり自由に使ってOK!)

    • 回答としてマーク tak2014 2014年4月20日 12:22
    2014年4月18日 4:01
  • 空文字になる場合はダミーの文字列を入れておく

    ということで、空文字の代用としてタブ文字などは如何でしょう。

    rs.Fields(1).Value = If(String.IsNullOrWhiteSpace(TextBox2.Text), vbTab, TextBox2.Text)

    上記にて B2 セルにタブをセットした場合、当方の Excel 環境では、式「=B2=""」に対して TRUE を返し、「=LEN(B2)」に対して 0 を返すようになっていました。「ISBLANK(B2)」 は FALSE を返してしまいますが。

    …あとは、直接 Excel.Application 経由で処理するとか、EPPlus等を利用してみるとか。

    • 回答としてマーク tak2014 2014年4月20日 12:24
    2014年4月18日 7:02

すべての返信

  • 追加情報

    ①②③はそれぞれ、フィールド変更後にupdateで更新しcloseでブックを閉じています。

    2014年4月14日 21:46
  • 回答しずらい状況だと思いますので、可能であれば再現できるできるだけ簡単なコードを掲示していただけませんか?
    普通に考えれば毎回ブックを閉じているのであれば、前回行った作業とは関係ないはずです。よって、DAOによる処理とはあまり関係ないように思えます。関係があるとすれば、Excel上に書き込まれたデータによって状況が変り、後の作業に影響を与えているのではないでしょうか?

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

    2014年4月15日 0:59
    モデレータ
  • ご回答ありがとうございます。

    以下に簡単なコードを記述します。

    [準備]
    ・TEST.xlsx(エクセル2010で作成)
     ヘッダ:KEY,VALUE
     1行目:KEY1,A
     2行目:KEY2,B
     VBプログラムと同フォルダに保存

    [VB2008Express]
    ・参照設定:
     Microsoft.Office.Interop.Access.Dao バージョン12.0(Microsoft Office
    14.0 Access database engine Object Library)
    ・フォーム
     テキストボックス2つとボタン1つ
    ・コード
    Imports Microsoft.Office.Interop.Access

    Public Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles Button1.Click
            Dim de As Dao.DBEngine = New Dao.DBEngine
            Dim db As Dao.Database
            Dim rs As Dao.Recordset
            Dim sql As String

            db = de.OpenDatabase(".\TEST.xlsx", False, False, "Excel 12.0
    Xml;HDR=YES;")
            sql = "SELECT * FROM [Sheet1$] WHERE KEY = '" & TextBox1.Text & "'"
            rs = db.OpenRecordset(sql, Dao.RecordsetTypeEnum.dbOpenDynaset)

            rs.Edit()
            rs.Fields(1).Value = TextBox2.Text
            rs.Update()

            rs.Close()
            db.Close()
            rs = Nothing
            db = Nothing

        End Sub
    End Class

    [手順]
    ①VBプログラム上で、Button1実行で「TEST.xlsx」1行目のVALUEをCに更新。
     →TEST.xlsxを開くと更新されている。
    ②VBプログラム上で、Button1実行で「TEST.xlsx」1行目のVALUEを""に更新。(空
    文字セット)
     →TEST.xlsxを開くと更新されている。
    ③VBプログラム上で、Button1実行で「TEST.xlsx」2行目のVALUEをDに更新。
     →TEST.xlsxを開くと更新されてない。
    ④VBプログラム上で、Button1実行で「TEST.xlsx」1行目のVALUEをCに更新。
     →TEST.xlsxを開くと更新されている。
    ③VBプログラム上で、Button1実行で「TEST.xlsx」2行目のVALUEをDに更新。
     →TEST.xlsxを開くと更新されている。

    [現象]
    ・1行目の値を文字で更新後、再度1行目を空文字で更新(DBNull.Value、Nothing
    でも同じ)すると、2行目が更新できなくなる。
    その後、1行目の値を文字で更新すると、2行目の値の更新が可能になる。
    ・更新できなくなった状態でTEST.xlsxをエクセル2010で開いて保存すると、2行
    目の更新が可能になる。
    ・最初に1行目の値を文字で更新せずに、空文字で更新した場合は、2行目の更新
    は可能。
    ・xls形式の場合は発生しない。

    セルを文字で更新した後に空文字で再度更新することで、エクセルが編集できな
    い状態になっています。

    セルに空文字をセットすることが影響しているのではないかと思いますが、原因対策がわかりません。

    また、ADO、ADO.NETも試しましたが同じ現象が発生した。

    引き続きよろしくお願いします。
    2014年4月15日 11:06
  • [手順]
    ①VBプログラム上で、Button1実行で「TEST.xlsx」1行目のVALUEをCに更新。
     →TEST.xlsxを開くと更新されている。
    ②VBプログラム上で、Button1実行で「TEST.xlsx」1行目のVALUEを""に更新。(空
    文字セット)
     →TEST.xlsxを開くと更新されている。
    ③VBプログラム上で、Button1実行で「TEST.xlsx」2行目のVALUEをDに更新。
     →TEST.xlsxを開くと更新されてない。

    遅くなりすみません。私の方で同じ環境で試してみたのですが、再現しませんでした。
    すなわち、③でTEST.xlsxを開くとDに更新されていました。もちろん、①から③の操作は一連の操作として続けて行いました。
    OS等、他の環境は何でしょうか? 私はWindows 7で確認しました。関連しそうなのがOfficeのバージョンですが、私は以下の通りです。

    Microsoft Excel 2010 (14.0.7109.5000) SP2 MSO(14.0.7116.5000) 64bit

    とりあえず最新のサービスパックが当たっているか確認してみて下さい。


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

    • 回答の候補に設定 星 睦美 2014年4月18日 0:30
    • 回答としてマーク tak2014 2014年4月20日 12:25
    2014年4月17日 9:03
    モデレータ
  • Excel を使うことが目的ではなく、何らかのデーターベースが必要でそれに Excel を使うことを検討しているという状況であれば、Excel は止めて SQL Server を使うという選択肢はないのでしょうか。

    それができればこのスレッドに書いてあるような問題に悩むことはなさそうです。

    また、SQL Server を使えば、別スレッドの質問、

    ADO.NETでエクセル使用時、データの大文字・小文字を区別するには
    http://social.msdn.microsoft.com/Forums/ja-JP/2d07ad08-51cd-44db-a90f-4d7c231550c1/adonet?forum=vbgeneralja

    も解決しそうです。


    そんな選択肢はないということであれば失礼しました。

    2014年4月17日 9:13
  • 多少環境が異なりますが、当方でも再現しました。Win7 Pro x64 SP1 + Office 2013 x64 + VS2010 Ultimate SP1、AnyCPU ビルドです。

    ただし、この現象が再現しない方もおられるようなので、特定バージョンだけの問題なのかも知れません。プロフェッショナルサポートへ問い合わせて、HotFix の類が提供されていないか調べてもらっては如何でしょうか。

    以下、参考までに当方の情報を:

    • Microsoft® Excel® 2013 (15.0.4303.1000) MSO (15.0.4605.1000) 64ビット
    • ACEDAO.DLL :更新=2014/01/23 16:05:42;サイズ=617,216バイト;Version=15.0.4569.1503
    • ACEEXCL.DLL :更新=2014/01/23 16:06:22;サイズ=532,704バイト;Version=15.0.4569.1501
    • Microsoft.Office.interop.access.dao.dll:更新=2011/12/12 06:54:10;サイズ=79,744バイト;Version=14.0.4760.1000

    > [準備]
    > ・TEST.xlsx(エクセル2010で作成)
    この時点で TEST.xlsx の内部データを確認したところ、
    .\xl\worksheets\sheet1.xml の中身は、すべて
    .\xl\sharedStrings.xml の文字列プールを参照していました。

    たとえば、Sheet1 の B2 セルは『<c r="B2" t="s"><v>3</v></c>』であり、
    sharedStrings.xml の /sst/si[3]/t/text() には『A』が記録されている状態です。


    > ①VBプログラム上で、Button1実行で「TEST.xlsx」1行目のVALUEをCに更新。
    >  →TEST.xlsxを開くと更新されている。
    この時点で TEST.xlsx の内部データを確認したところ、
    .\xl\worksheets\sheet1.xml の中身は、B2 セルのみが
    inlineStr "C" を参照する状態に変更されていました。
    『<c r="B2" t="inlineStr"><is><t>C</t></is></c>』

    なお、文字列プール(.\xl\sharedStrings.xml)は変更されておらず、
    最初の "KEY", "VALUE", "KEY1", "A", "KEY2", "B" が保持されたままです。


    > ②VBプログラム上で、Button1実行で「TEST.xlsx」1行目のVALUEを""に更新。(空
    > 文字セット)
    >  →TEST.xlsxを開くと更新されている。
    ①と同様、文字列プールは更新されず、シート1の B2 セルが inlineStr で
    埋め込まれていました。ただしデータは空要素です。『<c r="B2" t="inlineStr"/>』


    > ③VBプログラム上で、Button1実行で「TEST.xlsx」2行目のVALUEをDに更新。
    >  →TEST.xlsxを開くと更新されてない。
    ②→③の段階では、TEST.xlsx の内容は 1bit たりとも変更されていませんでした。

    2014年4月18日 1:26
  • 当方でも再現できました。

    環境Excel2003 + 互換パック + Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント上のVBAです。

    XLSXにネイティブ対応していないEXCELのVBAからの更新でも現象が発生するのでデータベースエンジン側のExcelドライバに問題があると推定できます。

    Option Explicit
    
    Sub Test()
        Const xlExcel12 = 50 'XLSB
        Const xlOpenXMLWorkbook = 51 'XLSX
        Const xlOpenXMLWorkbookMacroEnabled = 52 'XLSM
        
         Dim path As String
         Dim ws As Workbook
         path = GetDeskTopPath() & "\DaoTest_XML.xlsx"
         Call FileTest(path, "Excel 12.0 Xml;", xlOpenXMLWorkbook)
         
         path = GetDeskTopPath() & "\DaoTest_Macro.xlsm"
         Call FileTest(path, "Excel 12.0 Macro;", xlOpenXMLWorkbookMacroEnabled)
         
         path = GetDeskTopPath() & "\DaoTest_Binary.xlsb"
         Call FileTest(path, "Excel 12.0;", xlExcel12)
    End Sub
    Sub FileTest(ByVal filePath, ByVal isam As String, ByVal format As Integer)
         Call CreateTestFile(filePath, format)
         Call Update(filePath, isam, "KEY1", "Test")
         Call Update(filePath, isam, "KEY1", "")
         Call Update(filePath, isam, "KEY2", "Success")
         Call Workbooks.Open(filePath)
    End Sub
    
    Function GetDeskTopPath() As String
        Dim shl
        Set shl = CreateObject("WScript.Shell")
        GetDeskTopPath = shl.SpecialFolders("Desktop")
    End Function
    
    Sub CreateTestFile(ByVal path As String, ByVal format As Integer)
        Dim ws As Workbook
        Dim sh As Worksheet
        Set ws = Workbooks.Add
        Set sh = ws.Worksheets(1)
        sh.Cells(1, 1) = "KEY"
        sh.Cells(1, 2) = "VALUE"
        sh.Cells(2, 1) = "KEY1"
        sh.Cells(2, 2) = "Dummy"
        sh.Cells(3, 1) = "KEY2"
        sh.Cells(3, 2) = "Fail"
        
        ws.SaveAs path, FileFormat:=format
        ws.Close
    End Sub
    
    Sub Update(ByVal filePath As String, ByVal isam As String, ByVal key As String, ByVal value As Variant)
        Dim de ' As New DAO.DBEngine 'Microsoft Office 14.0 Access Database engine object Libraly
        Dim db 'As DAO.Database
        Dim rs 'As DAO.Recordset
        Dim sql As String
        Set de = CreateObject("DAO.DBEngine.120")
        Set db = de.OpenDatabase(filePath, False, False, isam & "HDR=YES;")
        sql = "SELECT * FROM [Sheet1$] WHERE KEY = '" & key & "'"
        Set rs = db.OpenRecordset(sql, DAO.RecordsetTypeEnum.dbOpenDynaset)
        
        rs.Edit
        rs.Fields(1).value = value
        rs.Update
        
        rs.Close
        db.Close
        
        Set rs = Nothing
        Set db = Nothing
        Set de = Nothing
    End Sub

    魔界の仮面弁士さんが書かれているように、文字列がブランクのinlineStrが1行以上あるとSharedStringの行の更新に失敗するようです。(inlineStrの位置は前後どこにあっても)
    xlsx内のシートデータ(XML)を取り出してブランクのセルを削除してみると正常に更新できます。

    現状では空文字になる場合はダミーの文字列を入れておくぐらいしか回避方法はないみたいです。


    個別に明示されていない限りgekkaがフォーラムに投稿したコードにはフォーラム使用条件に基づき「MICROSOFT LIMITED PUBLIC LICENSE」が適用されます。(かなり自由に使ってOK!)

    • 回答としてマーク tak2014 2014年4月20日 12:22
    2014年4月18日 4:01
  • 空文字になる場合はダミーの文字列を入れておく

    ということで、空文字の代用としてタブ文字などは如何でしょう。

    rs.Fields(1).Value = If(String.IsNullOrWhiteSpace(TextBox2.Text), vbTab, TextBox2.Text)

    上記にて B2 セルにタブをセットした場合、当方の Excel 環境では、式「=B2=""」に対して TRUE を返し、「=LEN(B2)」に対して 0 を返すようになっていました。「ISBLANK(B2)」 は FALSE を返してしまいますが。

    …あとは、直接 Excel.Application 経由で処理するとか、EPPlus等を利用してみるとか。

    • 回答としてマーク tak2014 2014年4月20日 12:24
    2014年4月18日 7:02
  • ご回答ありがとうございます。

    こちらの環境です。

    Excel 2010(14.0.7109.5000)SP2 MSO(14.0.7116.5000)32bit
    ACEDAO.DLL バージョン:14.0.7010.1000
    ACEEXCL.DLL バージョン:14.0.7104.5000
    Microsoft.Office.Interop.Access.Dao.dll バージョン:14.0.4760.1000
    VisualBasic2008Express version9.0.30729.1SP
    .NET Framework 3.5 SP1

    エクセルのバージョンが古かったのでWindows UPDATEで最新にしましたが、改善されませんでした。

    64bitと32bitの違いがあるのかもしれませんが、再現性もあるようですので、ドライバの問題と判断したいと思います。

    今回は急を要する修正ですので、空文字更新は断念し削除フラグ等代替案で対応したいと思います。


    • 編集済み tak2014 2014年4月20日 12:32
    2014年4月20日 12:13