使用条件格式将导出文件从Access格式化为Excel [英] Formatting an export file from access to excel with conditional formatting

问题描述

在执行格式检查时需要帮助.好的，现在我所拥有的是访问表单上的一个按钮，单击该按钮可将结果导出到新的空白Excel文件中.我现在要完成的工作是添加一些代码，检查整个WHOLE数据集中的字段6-10是否加在一起<>字段5，然后执行条件格式设置并突出显示与黄色不同的字段.

Needing assistance on performing a format check. Ok so what I have now is a button on my access form that exports the results into a new blank Excel file when clicked. What I am trying to accomplish now is add some code that checks if Fields 6-10 added together <> field 5 throughout the WHOLE dataset then performing conditional formatting and highlight the ones that aren't the same as yellow.

一旦安装到位，我希望出现一个消息框，但是Excel文件中突出显示的许多项目都出现在列表框msg中.有一个例子，其中有(9)个单元格不匹配，在提交上载之前需要引起注意！"

Once this is in place, Then I would like a message box to appear and however many items that are highlighted in the Excel file to appear within the list box msg. An example being there are (9) cells that are mismatched and need attention before an upload can be submitted!"

Dim dbs As DAO.Database
Dim Excel_App As Excel.Application 'Creates Blank Excel File
Dim strTable As String ' Table in access
    Dim queryDelete As String 'SQL to delete records in local table
    Dim strAssigned As DAO.Recordset


    strTable = "Make_allUp" 'Access Table I am trying to copy
    Set Excel_App = CreateObject("Excel.Application")
        Set dbs = CurrentDb

Dim rs As DAO.Recordset
Set rs = dbs.OpenRecordset(strTable)

    Excel_App.Visible = True

Dim wkb As Excel.Workbook
Set wkb = Excel_App.Workbooks.Add

Dim rg As Excel.Range
Dim i As Long
' Add the headings
For i = 0 To rs.Fields.Count - 1
    wkb.Sheets(1).Cells(1, i + 1).Value = rs.Fields(i).Name
Next i

Set rg = wkb.Sheets(1).Cells(2, 1)
rg.CopyFromRecordset rs

' make pretty
rg.CurrentRegion.EntireColumn.AutoFit

If wkb.Sheets(1).Cells(5, 2) <> wkb.Sheets(1).Cells(6, 2) + wkb.Sheets(1).Cells(7, 2) + wkb.Sheets    (1).Cells(8, 2) + wkb.Sheets(1).Cells(9, 2) + wkb.Sheets(1).Cells(10, 2) Then
Format.Cells
****Okay above is what I am having troubles with. Just needing to somehow say if the vaule in field 6 + field 7 + field 8 + field 9 + field 10 <> field 5 Then
highlight those records yellow



Set rs = Nothing
Set wkb = Nothing
Set dbs = Nothing


End Sub

推荐答案

据我所知，Access不会也不能格式化导出信息.可以编写Excel VBA代码来格式化目标电子表格中的单元格.同样，可以在Access VBA中编写代码以编写Excel VBA代码来格式化单元格，并将代码导出到电子表格中.此外，还可以打开导出信息的电子表格，并使用Access VBA代码在Access女士中设置单元格的格式.但是，据我所知，您无法在导出过程本身之前或期间获得访问权限来格式化信息.

As I know, Access will not, and can not, format the export information. It is possible to write Excel VBA code to format the cells in the target spreadsheet. Also, it is possible to write code in Access VBA to write the Excel VBA code to format the cells and to have the code exported tot he spreadsheet. In addition, it is also possible to open the spreadsheet where the information was exported and format the cells from within Ms. Access using Access VBA code. But, as I know, you cannot get Access to format the information prior to, or during, the export process itself.

这篇关于使用条件格式将导出文件从Access格式化为Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！