access vba 操作excel

最新推荐文章于 2025-08-09 11:36:06 发布

最新推荐文章于 2025-08-09 11:36:06 发布 · 1.6k 阅读

access vba开发专栏收录该内容

14 篇文章

订阅专栏

本文提供了一个使用VBA操作Excel文件的示例代码，包括显示Excel、读取指定路径下的工作簿、修改单元格内容、设置边框样式等操作，并在完成操作后关闭并释放资源。

Private Sub showExcel()
  Dim xlApp As Excel.Application
  Set xlApp = CreateObject("Excel.Application")
  xlApp.Visible = True    '显示EXCEL应用程序
  '申明EXCEL工作簿对象
  Dim xlBook As Excel.Workbook
  '设置工作簿对象为指定的工作簿
  Set xlBook = xlApp.Workbooks.Open(CurrentProject.Path & "\导出结果\清册查询.xls")
  xlBook.Save
End Sub

以上为显示excel表，以下为操作excel表

Private Sub myExcel()
'操作excel
Dim xlApp As Excel.Application
Set xlApp = GetObject(, "Excel.Application")
    '申明EXCEL工作簿对象
     Dim xlBook As Excel.Workbook
    '设置工作簿对象为指定的工作簿
     Set xlBook = xlApp.Workbooks.Open(CurrentProject.Path & "\导出结果\清册查询.xls")
     xlApp.Visible = False  '显示EXCEL应用程序
     hang = ActiveSheet.UsedRange.Rows.Count
     '以下部分为EXCEL录制宏录制整理后所得代码
    Range("A" & hang + 1).Select
    ActiveCell.FormulaR1C1 = "制表日期"
    Range("B" & hang + 1).Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("A" & hang + 2).Select
    ActiveCell.FormulaR1C1 = "总数"
    Range("B" & hang + 2).Select
    ActiveCell.FormulaR1C1 = hang - 1
    Range("A" & hang + 3).Select
    ActiveCell.FormulaR1C1 = "总额"
    Range("B" & hang + 3).Select
    ActiveCell.Formula = "=SUM(T6:T" & hang + 4 & ")"
    Range("A1:T" & hang).Select
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
   Range("A" & hang + 1 & ":B" & hang + 5).Select
     '退应用程序并释放内存
     xlBook.Save
     xlBook.Close
     xlApp.Quit
     Set xlApp = Nothing
     Set xlBook = Nothing
End Sub