使用VBA将Excel范围的总和拉入Access [英] Pull Sum of Excel Range into Access using VBA

问题描述

我正在从Access打开Excel电子表格，需要在工作表（1）上总结一个范围，范围G13：G100。根据下面的代码，我已经能够从特定的单元格中获取数据并将其带入，但是如何获得范围的总和？



I尝试过：

I am opening an excel spreadsheet from Access, and need to sum up a range on Worksheet(1), range G13:G100. I have been able to grab the data from a specific cell and bring it in, per the code below, but how do I get the sum of a range?

I have tried:

rec.Fields("TotalTime") = xlsSht.WorksheetFunction.Sum("G13:G100")

但是收到错误运行时错误'438'：对象不支持此属性或方法。

but am getting the error "Run-time error '438': Object doesn't support this property or method."

Private Sub cmdImport_Click()
  Dim rec As DAO.Recordset
Dim xls As Object
Dim xlsSht As Object
Dim xlsWrkBk As Object
Dim xlsPath As String
Dim xlsFile As String
Dim fullXlsFile As String
Dim fullFile As String

Dim Msg, Style, title, Response
  Msg = "Importing is Done, Files are imported!"    ' Define message.
  Style = vbOKOnly
  title = "Import Mesage"
 
    xlsPath = Me.txtFolder & "\"
    xlsFile = Dir(xlsPath & "*.xls", vbNormal)     ' Retrieve the first entry.
    
    Do While xlsFile <> ""
      ' Ignore the current directory and the encompassing directory.
      fullXlsFile = xlsPath & xlsFile
      fullFile = xlsPath & xlsFile
      
      If Right(fullXlsFile, 4) = ".xls" Then 'import it
        DoCmd.SetWarnings False
        Set xls = CreateObject("Excel.Application")
        xls.Application.Visible = False
        xls.Application.displayalerts = False
        Set xlsWrkBk = GetObject(fullXlsFile)
        Set xlsSht = xlsWrkBk.Worksheets(1) 'NestSummary

        'open table
        Set rec = CurrentDb.OpenRecordset("tblNestImport")
        rec.AddNew
        rec.Fields("NestID") = Nz(StrConv(xlsSht.cells(2, "E"), vbProperCase), "badnestid")
        rec.Fields("MaterialPartNumber") = Nz(StrConv(xlsSht.cells(5, "N"), vbProperCase), "badmaterialpartnumber")
        rec.Fields("TotalTime") = xlsSht.WorksheetFunction.Sum("G13:G100")
        
        rec.Update
        
        DoCmd.SetWarnings True
      End If
      
      'close excel
      xlsWrkBk.Application.Quit
      Set xls = Nothing
      
      xlsFile = Dir()
    Loop
    
    Response = MsgBox(Msg, Style, title)
        
End Sub

推荐答案

请先阅读我的评论，然后看看这里： WorksheetFunction.Sum [ ^ ]。



你需要传递 Variant 数据类型作为输入参数。这对你意味着什么？您需要通过范围 [ ^ ]对象！

Please, read my comment first, then have a look here: WorksheetFunction.Sum[^].

You need to pass Variant data type as input parameter. What it means to you? You need to pass Range[^] object!

rec.Fields("TotalTime") = xlsSht.WorksheetFunction.Sum(xlsSht.Range("G13:G100"))

它应该有效。如果没有，请告诉我。

It should works. If not, please let me know.

这篇关于使用VBA将Excel范围的总和拉入Access的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！