error Object Required: 'ActiveCell.Resize(...).Slect' and sum not wor

[matfishe]

Hello,

Im getting the error " Object Required: 'ActiveCell.Resize(...).Slect' ". My code looks like this:



'Declare variables and open excel then open the specified file
dim objExcel005, ExcelWB005, osheet005
set objExcel005 = createobject("Excel.Application")
objExcel005.visible = true
set ExcelWB005 = objExcel005.Workbooks.Open("C:\Program
Files\Automated Flood Warning Software\Logs\Jackson Creek Post Office.csv")

objExcel005.Selection.find("12:00").activate
objExcel005.ActiveCell(1, 3).Select

Set oSheet005 = ExcelWB005.Worksheets(1)
Set oRange005 = objExcel005.ActiveCell.Resize(3, 1).Select
objExcel005.Selection.Copy
oSheet1.Range("A400") = objExcel005.WorksheetFunction.Sum(oRange005)
oSheet1.Range("A400").Copy



the error happens on the "Set oRange005 = objExcel005.ActiveCell.Resize(3, 1).Select"

Also its not using sum for the data that is selected.


Thanks in advance!

 

[Geates]

this suggests that .Resize needs an object to work on yet objExcel005.ActivateCell does not return an object as it simply sets a status, so .Resize is throwing an error say, where's the object. First, get the selection and then resize it.

maybe something like (just a guess)

set objSelection = objExcel005.Selection
set objRange = objSelection.Resize(3, 1)

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

"There are seldom good technological solutions to behavioral problems."
- Ed Crowley, Exchange guru and technology curmudgeon

 

[matfishe]

Thanks. I added in the lines you suggested but its only doing as far as the line "objExcel005.ActiveCell(1, 3).Select". I not getting anymore errors but its just not selecting the 3 lines I need copied. Heres the code so far:

'Declare variables and open excel then open the specified file
dim objExcel005, ExcelWB005, osheet005, objRange
set objExcel005 = createobject("Excel.Application")
objExcel005.visible = true
set ExcelWB005 = objExcel005.Workbooks.Open("C:\Program Files\Automated Flood Warning Software\Logs\Jackson Creek Post Office.csv")

objExcel005.Selection.find("12:00").activate
objExcel005.ActiveCell(1, 3).Select



Set oSheet005 = ExcelWB005.Worksheets(1)
set objSelection = objExcel005.Selection
set objRange = objSelection.Resize(3, 1)


oSheet005.Range("A400") = objExcel005.WorksheetFunction.Sum(objRange)
oSheet005.Range("A400").Copy

 

[Geates]

you are trying to get the selection from the wrong object. Get it from the workbook object, ExcelWB005, not the application object, objExcel005.

[red]set objSelection = ExcelWB005.Selection[/red]
[s]set objSelection = objExcel005.Selection[/s]

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

"There are seldom good technological solutions to behavioral problems."
- Ed Crowley, Exchange guru and technology curmudgeon

 

[Geates]

I mean, my bad.

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

"There are seldom good technological solutions to behavioral problems."
- Ed Crowley, Exchange guru and technology curmudgeon

 

[matfishe]

I changed it but now Im getting the error " object doesnt support this property or method: 'Selection' "



'Declare variables and open excel then open the specified file
dim objExcel005, ExcelWB005, osheet005, objRange
set objExcel005 = createobject("Excel.Application")
objExcel005.visible = true
set ExcelWB005 = objExcel005.Workbooks.Open("C:\Program Files\Automated Flood Warning Software\Logs\Jackson Creek Post Office.csv")

objExcel005.Selection.find("12:00").activate
objExcel005.ActiveCell(1, 3).Select



Set oSheet005 = ExcelWB005.Worksheets(1)
set objSelection = ExcelWB005.Selection
set objRange = objSelection.Resize(3, 1)


oSheet005.Range("A400") = objExcel005.WorksheetFunction.Sum(objRange)
oSheet005.Range("A400").Copy

 

[Geates]

Again, I making only logical suggestions, I am not familiar with the Excel object. Although, I've got some time. Let me dig in deep and see what I can uncover.

-Geates

"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

"There are seldom good technological solutions to behavioral problems."
- Ed Crowley, Exchange guru and technology curmudgeon

 

[Geates]

As the picture suggests, I got it to the point of finding, selecting, resizing, copying and summing

'Define Excel object (objExcelApp) and load a csv file into the objWorkbook object
set objExcelApp = CreateObject("Excel.Application")
set objWorkbook = objExcelApp.Workbooks.Open("C:\test.csv")

'Set the Excel application to be visible
objExcelApp.visible = true

'Find and select the cell containing strFind
strFind = "36"
objExcelApp.Selection.Find(strFind).Select

'set the current excel application selection to its own object (objSelection)
set objSelection = objExcelApp.Selection

'Resize, Select AND STORE IN NEW OBJECT
set objRange = objSelection.Resize(3, 2)
objRange.Select

'Copy the range to clipboard
objRange.Copy

msgbox objExcelApp.WorksheetFunction.Sum(objRange)

NOTE: .Resize(rows, cols) seems to return a NEW Range object containing the resized selection. However, it DOES NOT effect the current selection.

Hope this helps

-Geates



"Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live."
- Martin Golding

"There are seldom good technological solutions to behavioral problems."
- Ed Crowley, Exchange guru and technology curmudgeon