He intentado simplificar una publicación anterior centrándome en una sola pregunta relacionada con Excel.WorksheetFunction.Sum. Claramente no sé cómo usar esta función, ya que sigo obteniendo Cero por un resultado o arrojando errores.
Simplemente no puedo hacer que esto funcione y lo he estado trabajando durante tres días (literalmente) y he realizado casi cien pruebas distintas. Aunque entiendo bien Access VBA, soy autodidacta y creo que me faltan algunos conceptos clave y eso me está matando en Excel.
Mi código a continuación es de prueba / depuración que he realizado y la documentación explica los resultados para cada versión distinta. El sub llama a la función (que es donde están los problemas).
Algunos puntos:
PUEDO obtener una SUMA en la hoja de trabajo cuando estoy usando Excel manualmente.
1a. Los números en el Rango requerido no siempre son contiguos, pero si reduzco el rango a solo números contiguos, todavía NO funciona.
Estoy escribiendo esto en un módulo de acceso, ya que es parte de una aplicación de acceso (tratando de automatizar la importación de datos desde una hoja de cálculo).
Se ha implicado que mi trabajo anterior no estaba "totalmente calificado", por lo que he creado esto usando un With Bloc. Sin embargo, es probable que NO esté haciendo esto correctamente.
Cualquier orientación sería muy apreciada, especialmente si pudieras explicar misericordiosamente qué conceptos me estoy perdiendo aquí.
Public Function fnImportFileProcessFilePrep2(intClientId As Integer, intEventId As Long, strExcelFileName As String, _
strActiveSheet As String, strQASumColumn As String)
On Error GoTo HandleError
Dim intLastCol As Long
Dim intLastRow As Long
Dim intNextCol As Long
Dim intRecordCount As Long
Dim varSumExcelColumns As Variant
Dim strSUMRange As String
Dim strAddColumnLabel As String
Dim dblSum As Double
Dim rgUseRange As Range
Dim rgSUMRange As Range
Dim strFileName As String
Dim oXLApp As Excel.Application 'Declare the object variables
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
Set oXLApp = New Excel.Application 'Create a new instance of Excel
Set oXLBook = oXLApp.Workbooks.Open(strExcelFileName) 'Open the existing workbook
Set oXLSheet = oXLBook.Worksheets(strActiveSheet) 'Work with the input worksheet
oXLSheet.Activate 'Activate the Worksheet
oXLApp.Visible = True 'Show it to the user
oXLApp.UserControl = True
With oXLSheet
' Replace ALL "(null)" cells - THIS WORKS!!!
.Cells.Replace What:="(null)", _
Replacement:="", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
'BOTH LastRow and LastCol WORK
'Get Last Row & Record Count
intLastRow = oXLSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'This Works
intRecordCount = intLastRow - 1
'Get Last Column
intLastCol = oXLSheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column 'This Works
intNextCol = intLastCol + 1
'Get SUM of Column strQASumColumn for use in QA
'NONE of the following work. Note that if I use Select it's for testing so that I can look at Open Excel Sheet and see the Select Range is correct
strSUMRange = strQASumColumn & "2:" & strQASumColumn & intLastRow ' "M2:M2934"
Set rgSUMRange = .Range(strSUMRange)
'rgSUMRange.Select
varSumExcelColumns = Excel.WorksheetFunction.Sum(rgSUMRange) 'Works BUT IS ZERO??
dblSum = Excel.WorksheetFunction.Sum(rgSUMRange) 'Works but ZERO?
varSumExcelColumns = Excel.WorksheetFunction.Sum(oXLSheet.Range(strSUMRange)) 'Works but Zero
'Try to use Cells
Set rgSUMRange = .Range(.Cells(2, "M"), .Cells(intLastRow, "M"))
rgSUMRange.Select
varSumExcelColumns = Excel.WorksheetFunction.Sum(rgSUMRange) 'Works but Zero SUM
Set rgSUMRange = .Range(.Cells(2, intNextCol), .Cells(intLastRow, intNextCol))
varSumExcelColumns = Excel.WorksheetFunction.Sum(rgSUMRange) 'Works but Zero
'Even Hard-coding the numbers does NOT work
Set rgSUMRange = .Range(.Cells(2, 13), .Cells(2934, 13)) ' Returns Zero Again
'rgSUMRange.Select ' Does show the correct Range
varSumExcelColumns = Excel.WorksheetFunction.Sum(rgSUMRange)
'Still Zero if I use a smaller range that has contiguous numbers
Set rgSUMRange = .Range(.Cells(3, 13), .Cells(7, 13)) ' Returns Zero Again
rgSUMRange.Select
varSumExcelColumns = Excel.WorksheetFunction.Sum(rgSUMRange)
'All these approaches ERROR
'varSumExcelColumns = Excel.WorksheetFunction.Sum(.Range(rgSUMRange)) 'Method Range of Object Worksheet Failed
'varSumExcelColumns = Excel.oXLSheet.WorksheetFunction.Sum(.Range(rgSUMRange)) 'Won't compile
' varSumExcelColumns = Excel.WorksheetFunction.Sum("M2:M2934") 'Unable to get the SUM Property of the WorksheetFunction Class
'varSumExcelColumns = Excel.WorksheetFunction.Sum(Range("M2:M2934")) 'Application defined or object defined error
'dblSum = Excel.WorksheetFunction.Sum("M2:M100") 'ERROR: Unable to get the SUM Property of the Worksheet function Class
'dblSum = Excel.WorksheetFunction.Sum(Range("M2:M100")) 'Application defined or --- Error
'varSumExcelColumns = Excel.WorksheetFunction.Sum(Worksheets(strActiveSheet).Range("M2", "M7")) 'ERROR Application Defined or Object Defined Error
'Go to EMPTY Range next to the Last Column
varSumExcelColumns = Excel.WorksheetFunction.Sum(.Range(.Cells(2, intNextCol), .Cells(intLastRow, intNextCol))) ' Works for SUM but is wrong Range
'THE ABOVE ACTUALLY WORKS, BUT ONLY IF I GO TO OPEN SPREADSHEET AND MANUALLY ENTER NUMBERS INTO THE RANGE AREA ?????????
'Since the above kinda worked, Try setting variables to a Range WITH Number data - Does NOT Work
intNextCol = 13
intLastRow = 7
varSumExcelColumns = Excel.WorksheetFunction.Sum(.Range(.Cells(2, intNextCol), .Cells(intLastRow, intNextCol)))
msgbox "SUM: " & varSumExcelColumns
'Test to see if I am still on the Correct Sheet - This WORKS
Dim dblCellValue As Double
dblCellValue = oXLSheet.Cells(2, 13).Value 'Works
End With
Exit_Label:
fnImportFileProcessFilePrep2 = varSumExcelColumns
oXLBook.Close SaveChanges:=False 'SaveChanges:=True 'Save (and disconnect from) the Workbook
oXLApp.Quit 'Close (and disconnect from) Excel
Set oXLSheet = Nothing 'Disconnect from all Excel objects (let the user take over)
Set oXLBook = Nothing
Set oXLApp = Nothing
Exit Function
HandleError:
msgbox "Error During fnImportFileProcessFilePrep2: " & Err.Description
Resume Next
End Function
Private Sub TestFilePrep()
Dim strFileNameAndPath As String
Dim strUseWorksheet As String
Dim intSUMColumn As Integer
Dim strSUMColumn As String
Dim strAddColumnLabel As String
Dim varAddColumnFixedValue As Variant
Dim dblSUMFromFunction As Double
strFileNameAndPath = "C:\Users\xxxxxxxWITH NULLS2.xlsx"
strUseWorksheet = "Sheet1"
intSUMColumn = 13
strSUMColumn = "M"
strAddColumnLabel = "SourceFile"
varAddColumnFixedValue = 77
dblSUMFromFunction = fnImportFileProcessFilePrep2(10, -3, strFileNameAndPath, _
strUseWorksheet, strSUMColumn)
End Sub
2 respuestas
Uso:
varSumExcelColumns = oXLApp.WorksheetFunction.Sum(rgSUMRange)
Ya que su aplicación de Excel está "representada" en el objeto oXLApp
Creo que el problema podría ser (al menos en parte) la forma en que declaras tus variables de rango. No he trabajado con VBA en Access, pero he usado VBA en Excel para crear y manipular documentos de Word, y me encontré con un problema similar.
Cuando declara una variable de rango, debe especificar que es una variable de rango de Excel (si es así como se usa). Entonces cuando tu
Dim rgUseRange As Range
Dim rgSUMRange As Range
En cambio, debería ser
Dim rgUseRange as Excel.Range
Dim rgSUMRange as Excel.Range
Esto es necesario porque el modelo de objetos de Access también tiene un objeto Range, pero es diferente del objeto Access Range (y el objeto Word Range).
Suponga que esto es cierto para cualquier objeto desde cualquier lugar que no sea el modelo de objeto "nativo" con el que está trabajando. Aunque, también está bien especificar ese modelo de objeto.
Espero que esto ayude.
Editar: aparentemente no hay ningún objeto Range en Access. Todavía intentaría esto para ver si me ayuda.
Nuevas preguntas
excel
Solo para preguntas sobre programación contra objetos o archivos de Excel, o desarrollo de fórmulas complejas. Puede combinar la etiqueta de Excel con VBA, VSTO, C #, VB.NET, PowerShell, automatización OLE y otras etiquetas y preguntas relacionadas con la programación, si corresponde. La ayuda general con respecto a MS Excel para funciones de hoja de trabajo única está disponible en Super User.