Access (VBA)

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

 
(Windows 10全般 : Access 2016)
既存のExcelファイルにエクスポートするとセルに勝手に色がつく
投稿日時: 19/03/29 14:53:45
投稿者: おばちゃん

こちらにはいつもヒントをいただいていて、勉強になります。
 
最近ですが、Accessから既存のExcel(Excel2016)ファイルにエクスポートすると、
データがあるセルに勝手に色がつく様になりました。
 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "クエリ名", strFile, True
 
手作業でエクスポートしても、同様の症状が出ますが、
新規ファイルにエクスポートしたら色が付きませんでした。
 
一応Excel側で「データ範囲の形式および数式を拡張する」のチェックは外してあります。
条件付き書式などの設定もしていません。
 
よろしくお願いします。

回答
投稿日時: 19/03/29 15:55:18
投稿者: sk

引用:
最近ですが、Accessから既存のExcel(Excel2016)ファイルにエクスポートすると、
データがあるセルに勝手に色がつく様になりました。

セルの何の色のことをおっしゃっているのでしょうか。
 
・フォントの色
 
・罫線の色
 
・塗りつぶしの色
 
また、特定のセルにおいて発生しているのか、
エクスポートされたレコードの全てのセルにおいて
発生しているのか、どちらなのでしょうか。

回答
投稿日時: 19/03/29 18:14:33
投稿者: Suzu

引用:
既存のExcel(Excel2016)ファイルにエクスポートすると、
データがあるセルに勝手に色がつく様になりました。

 
既存との事なので、
 条件付き書式が設定されており、最近その条件のデータになったので色がついた。
 誰かが条件付き書式を設定した
 
はありませんか?

投稿日時: 19/04/01 13:24:33
投稿者: おばちゃん

 
SK様
背景色が色が付きます。
 
Suzu様
条件付き書式は設定してありません。
 
vbaでやっていること。
1回目・・・Access側からクエリを3つ分、新規でエクスポート、
その後、ファイルの加工処理をVBAで行っています。
 
加工処理のプログラム内容は
エクスポートしたファイルを開き、1行目から2行分挿入し、1行目(A1)にはシート名を代入し、2行目には列分のSUM関数を代入、3行目の項目部分の背景色に色(背景色はシートごと違う色)を3シート分同じ作業をし、保存しています。
 
2回目・・・加工後のファイルをAccess側からクエリを1つ、エクスポートしています。
その後、1回目と同じように加工し、保存しています。
 
保存したファイルを手作業で開くと2回目にエクスポートしたシートの項目名、データがある全セルの背景色に勝手に色がつきます(背景色は1回目の加工で最後のシートに項目名に色を付けていると同じ色でした。)。
 
デバッグしてわかったこと
1回目にVBAで加工処理をしていないと、2回目にエクスポートしても背景色は付きませんでした。
 
 
 

回答
投稿日時: 19/04/01 14:51:31
投稿者: sk

引用:
vbaでやっていること。
1回目・・・Access側からクエリを3つ分、新規でエクスポート、
その後、ファイルの加工処理をVBAで行っています
  
加工処理のプログラム内容は
エクスポートしたファイルを開き、1行目から2行分挿入し、1行目(A1)にはシート名を代入し、2行目には列分のSUM関数を代入、3行目の項目部分の背景色に色(背景色はシートごと違う色)を3シート分同じ作業をし、保存しています。

引用:
保存したファイルを手作業で開くと2回目にエクスポートしたシートの項目名、データがある全セルの背景色に勝手に色がつきます(背景色は1回目の加工で最後のシートに項目名に色を付けていると同じ色でした。)。

それは「勝手に」というより、VBA のコードを実行した結果として
そうなった可能性があるように思えます。
 
引用:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "クエリ名", strFile, True

引用:
1回目・・・Access側からクエリを3つ分、新規でエクスポート
その後、ファイルの加工処理をVBAで行っています。

また、次のどのケースに該当するかによって、
TransferSpreadsheet メソッドによる実行結果は異なります。
 
A. 変数 strFile に格納されているファイルパスに該当する
   Excel ブックが存在しない場合。
 
B. 変数 strFile に格納されているファイルパスに該当する
   Excel ブックが存在している場合。
 
   B-1. エクスポートされるクエリと同じ名前の
        名前付きセル範囲が定義されていない場合。
        (そのブックにそのクエリがまだ1度もエクスポートされたことがない場合)
 
   B-2. エクスポートされるクエリと同じ名前の
        名前付きセル範囲が定義されている場合。
        (そのブックにそのクエリがエクスポートされたことがある場合)
 
引用:
2回目・・・加工後のファイルをAccess側からクエリを1つ、エクスポートしています。
その後、1回目と同じように加工し、保存しています。

ここでエクスポートされるクエリは「1回目」の処理で
エクスポートされた 3 つのクエリの 1 つなのでしょうか。
 
引用:
デバッグしてわかったこと
1回目にVBAで加工処理をしていないと、2回目にエクスポートしても背景色は付きませんでした。

いずれにせよ、実際のコードを拝見しないことには
原因を特定出来ません。

投稿日時: 19/04/01 17:37:38
投稿者: おばちゃん

1回目のクエリ1では新規ファイルでエクスポートし、クエリ2以降はクエリ1でエクスポートしたファイルにエクスポートしています。
2回目のクエリ4も1回目のクエリ1〜クエリ3の既存のファイルにエクスポートしています。
 
長いのですが、コードです。
1回目のExportはcmdExport1_Click
2回目のExportはcmdExport2_Click
 
Private Sub cmdExport1_Click()
 
Dim strFolder As String
Dim strFile As String
 
    If FolderDialogOpen(strFolder) = False Then Exit Sub
     
     
    strFile = strFolder & "Test" & Format(Now, "yyyymmdd") & "-" & Format(DateAdd("d", Now, 1), "yyyymmdd") & ".xlsx"
     
    If Dir(strFile) <> "" Then
        MsgBox strFile & "がファイルが存在します。削除してから実行してください。", vbCritical
        Exit Sub
    End If
     
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "クエリ1", strFile, True
     
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "クエリ2", strFile, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "クエリ3", strFile, True
     
    'Exportファイルの加工
    DeviceFile1 strFile
     
    MsgBox "エクスポートしました。"
     
End Sub
 
Private Sub DeviceFile1(strFile As String)
 
Dim App As New Excel.Application
Dim wb As Workbook
 
     
    'exportしたファイルを開く
    Set wb = App.Workbooks.Open(strFile, False, False)
    App.Visible = True
    App.DisplayAlerts = False
     
     
    SetFormat wb, "クエリ1", RGB(146, 208, 80) '緑
     
    SetFormat wb, "クエリ2", RGB(255, 204, 255) 'ピンク
     
    SetFormat wb, "クエリ3", RGB(75, 172, 198) '青
     
     
    wb.Close True
     
    App.DisplayAlerts = True
     
    App.Quit
     
    Set wb = Nothing
    Set App = Nothing
     
End Sub
 
Private Sub cmdExport2_Click()
 
Dim strFolder As String
Dim strFile As String
 
    If FileDialogOpen(strFile) = False Then Exit Sub
     
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "クエリ4", strFile, True
 
この時点で開くとクエリ4シートのデータのある背景色に色が付きます。
     
    'Exportファイルの加工
    DeviceFile2 strFile
     
    MsgBox "エクスポートしました。"
 
End Sub
Private Sub DeviceFile2(strFile As String)
 
Dim App As New Excel.Application
Dim wb As Workbook
     
    'exportしたファイルを開く
    Set wb = App.Workbooks.Open(strFile, False, False)
    App.Visible = True
    App.DisplayAlerts = False
     
    SetFormat wb, "クエリ4", RGB(247, 150, 70) 'オレンジ
     
    wb.Close True
     
    App.DisplayAlerts = True
     
    App.Quit
     
    Set wb = Nothing
    Set App = Nothing
 
End Sub
Private Sub SetFormat(wb As Workbook, strwsName As String, lngColor As Long)
 
Dim ws As Worksheet
Dim lngEndRow As Long
Dim lngEndCol As Long
 
 
    Set ws = wb.Worksheets(strwsName)
     
    '2行挿入しA1セルにシート名と同じ値を入力
    ws.Rows("1:2").Insert
    ws.Range("A1") = ws.Name
     
    lngEndRow = ws.Range("A3").End(xlDown).Row
    lngEndCol = ws.Range("A3").End(xlToRight).Column
     
    '2行目にSum
    ws.Range(ws.Cells(2, 2), ws.Cells(2, lngEndCol)).Formula = "=SUM(B4:" & ws.Cells(lngEndRow, 2).Address(False, False) & ")"
    ws.Range(ws.Cells(3, 2), ws.Cells(3, lngEndCol)).Interior.Color = lngColor
     
    Set ws = Nothing
 
End Sub

回答
投稿日時: 19/04/01 20:00:12
投稿者: sk

引用:
1回目のクエリ1では新規ファイルでエクスポートし、
クエリ2以降はクエリ1でエクスポートしたファイルにエクスポートしています。
2回目のクエリ4も1回目のクエリ1〜クエリ3の既存のファイルにエクスポートしています。

なるほど。おかげさまで現象を再現できました。
 
とりあえずの結論を申し上げると、これはバグの一種である
と考えられます。
 
また VBA のコードに限らず、通常のエクスポート操作でも
再現される現象です。
 
(再現手順)
 
1. Excel を起動し、新規ブックを作成する。
 
2. いずれかのセルの塗りつぶしを設定し、そのまま名前を付けて保存する。
   (ブックを保存する直前の操作が「セルの塗りつぶしの設定」だけ
   であることが重要)
 
3. ブックを閉じる。
 
4. Access データベース上の任意のテーブル/クエリを
   そのブックにエクスポートする。
 
5. ブックを開き、エクスポート先のワークシートを表示する。
 
(検証結果)
 
・Access 2003 / Excel 2003 では発生しなかった。
 
・Access 2010 / Excel 2010 でも発生した。
 (ブックのファイル形式が .xls でも変わらなかった)
 
・エクスポートによって既存のブックに新規ワークシートが
 追加される場合に発生する。
 (ブック上にクエリと同名の名前付きセル範囲が存在しない場合)
 
・例えば「セルの塗りつぶしの設定」と同時に
 「フォントの色の設定」をして保存した場合は
 発生しない。
 
・しかし「セルの塗りつぶしの設定」をしてから
 任意のセルの値を編集して保存しても発生する。
 
・エクスポートされたテーブル/クエリに
 日付/時刻型のフィールドが含まれている場合、
 その列のデータ行に関しては(何故か)適用されない。
 (見出し行には適用される)
 
とりあえずの回避策としては、
 
引用:
ws.Range(ws.Cells(3, 2), ws.Cells(3, lngEndCol)).Interior.Color = lngColor

With ws.Range(ws.Cells(3, 2), ws.Cells(3, lngEndCol))
    .Interior.Color = lngColor
    .Font.Color = .Font.Color
End With
 
------------------------------------------------------
 
のようなコードを挿入してやると大丈夫っぽいです。

投稿日時: 19/04/02 11:10:56
投稿者: おばちゃん

sk様
 
Font.colorのコードを追加したら、症状が出なくなりました。
ありがとうございます。
 
ネットで調べても、探し出せなかったので助かりました。
 
今後もよろしくお願いします。