Excel 互操作格式

Question

使用 Access 2007 vba 寻址 Excel 2007 工作簿（“WB”）...

此例程将工作表添加到现有且打开的 Excel 工作簿中，然后将记录集的行添加到工作表中。现在很混乱，我将其设置为创建多个工作表，所有工作表都以不同的方式查看相同的数据。

我已经成功发布了行，但现在我想更好地设置它们的格式，以避免对不了解 Excel 的目标用户造成混淆。我想更改标题单元格的颜色，并且想将列设置为“自动调整”。

有人可以帮忙吗？

Sub SummarySheets(WB As Excel.WorkBook, TempTableName As String)
Const SummarySheetName As String = "Timesheet Summaries"
Const SummaryQueryName As String = "qrySATempSummarybyWO"
Const SummaryTitleRow As Integer = 1


Dim xlSumSht As Excel.Worksheet
If SheetExists(WB, SummarySheetName) Then
    WB.Sheets(SummarySheetName).Delete
End If
Set xlSumSht = WB.Sheets.Add(After:=WB.Sheets(WB.Sheets.count))
xlSumSht.NAME = SummarySheetName
xlSumSht.Activate

Dim intRow As Integer
Dim intCol As Integer
Dim intStartRow As Integer
Dim strSQL As String
Dim strQry As String

Dim rstSummary As DAO.Recordset

strQry = CurrentDbC.QueryDefs(SummaryQueryName).SQL
strSQL = Replace(strQry, "tblStaffAugTrans", "[" & TempTableName & "]")

Set rstSummary = CurrentDbC.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges + dbFailOnError)
If rstSummary.EOF = False Then
    intRow = SummaryTitleRow
    For intCol = 1 To rstSummary.Fields.count
        xlSumSht.Cells(intRow, intCol).Value = rstSummary.Fields(intCol - 1).NAME
    Next intCol
End If
While rstSummary.EOF = False
    intRow = intRow + 1
    For intCol = 1 To rstSummary.Fields.count
        xlSumSht.Cells(intRow, intCol).Value = rstSummary.Fields(intCol - 1).Value
    Next intCol
    rstSummary.MoveNext
Wend
For intCol = 1 To rstSummary.Fields.count
    'xlSumSht.Columns.EntireColumn(, intCol).AutoFit
Next intCol

rstSummary.Close

结束子

Answer 1

像这样的东西应该可以完成工作。这将简单地将标题设置为黄色内部颜​​色，并自动调整列。

With xlSumSht.Range(Cells(SummaryTitleRow, 1), Cells(SummaryTitleRow, rstSummary.Fields.Count))
    .Interior.Color = vbYellow
    .EntireColumn.AutoFit
End With