ActiveCell Offset error

[hidefguy]

Code: Select all

xlApp := ComObjActive("Excel.Application")
FileTo_Path := A_Desktop "\Perm.xlsx"
FileTo := xlApp.Workbooks.Open(FileTo_Path)
FileFrom_Pattern := A_Desktop "\Test\*.xlsx"
Loop, Files, % FileFrom_Pattern
{
	FileFrom := xlApp.Workbooks.Open(A_LoopFileFullPath)
	if (A_Index = 1)
	{
		FileTo.Sheets(1).Range("G2").value := FileFrom.Sheets(2).Range("D4").value
		FileTo.Sheets(1).Range("H2").value := FileFrom.Sheets(2).Range("I4").value
	}
	else
	{
		FileTo.Sheets(1).Range("G2").Offset(0,A_Index).value := FileFrom.Sheets(2).Range("I4").value
	}
	FileFromFullPath := FileFrom.Path "\" FileFrom.Name
	FileFrom.Close()
}
Exit App

I'm getting an error when trying to convert the above to the following. Error message states that ActiveCell.Offset is not recognized. The goal is to use an active cell in the first column as a reference point for value destination cells, instead of specific range coordinates. Appreciate any help. Tried a few different things with no success.

Code: Select all

xlApp := ComObjActive("Excel.Application")
FileTo_Path := A_Desktop "\Perm.xlsx"
FileTo := xlApp.Workbooks.Open(FileTo_Path)
FileFrom_Pattern := A_Desktop "\Test\*.xlsx"
Loop, Files, % FileFrom_Pattern
{
	FileFrom := xlApp.Workbooks.Open(A_LoopFileFullPath)
        if (A_Index = 1)
	{
		FileTo.Sheets(1).ActiveCell.Offset(0,6).value := FileFrom.Sheets(2).Range("D4").value
		FileTo.Sheets(1).ActiveCell.Offset(0,7).value := FileFrom.Sheets(2).Range("I4").value
        }
	else
	{
		FileTo.Sheets(1).ActiveCell.Offset(0,6).Offset(0,A_Index).value := FileFrom.Sheets(2).Range("I4").value
	}
	FileFromFullPath := FileFrom.Path "\" FileFrom.Name
	FileFrom.Close()
}
Exit App

[Odlanir]

Since the ActiveCell could be only one in all opened worksheets/Workbooks you should refer to the application ActiveCell

Code: Select all

xlApp.ActiveCell

[hidefguy]

Code: Select all

xlApp := ComObjActive("Excel.Application")

FileTo_Path := A_Desktop "\Perm.xlsx"
FileTo := xlApp.Workbooks.Open(FileTo_Path)

FileFrom_Pattern := A_Desktop "\Test\*.xlsx"
Loop, Files, % FileFrom_Pattern
{
	FileFrom := xlApp.Workbooks.Open(A_LoopFileFullPath)
	if (A_Index = 1)
	{
        x1App.ActiveCell.Offset(0,6).value := FileFrom.Sheets(1).Range("D4").value
	    x1App.ActiveCell.Offset(0,7).value := FileFrom.Sheets(1).Range("I4").value
	}
	else
	{        
        x1App.ActiveCell.Offset(0,6).OffSet(0,A_Index).value := FileFrom.Sheets(1).Range("I4").value
	}
	FileFromFullPath := FileFrom.Path "\" FileFrom.Name
	FileFrom.Close()
}
ExitApp

I tried this, based on your suggestion. The script completes but destination file (Perm) doesn't show any values. Just blank cells.

[FanaticGuru]

Code: Select all

xlApp := ComObjActive("Excel.Application")

FileTo_Path := A_Desktop "\Perm.xlsx"
FileTo := xlApp.Workbooks.Open(FileTo_Path)

FileFrom_Pattern := A_Desktop "\Test\*.xlsx"
Loop, Files, % FileFrom_Pattern
{
	FileFrom := xlApp.Workbooks.Open(A_LoopFileFullPath)
	if (A_Index = 1)
	{
        x1App.ActiveCell.Offset(0,6).value := FileFrom.Sheets(1).Range("D4").value
	    x1App.ActiveCell.Offset(0,7).value := FileFrom.Sheets(1).Range("I4").value
	}
	else
	{        
        x1App.ActiveCell.Offset(0,6).OffSet(0,A_Index).value := FileFrom.Sheets(1).Range("I4").value
	}
	FileFromFullPath := FileFrom.Path "\" FileFrom.Name
	FileFrom.Close()
}
ExitApp

I tried this, based on your suggestion. The script completes but destination file (Perm) doesn't show any values. Just blank cells.

It appears you used x1App when you should have used xlApp. The character after the x should be the letter L not the number 1. It is an abbreviation for Excel Application as that is the type object it stores. It could be any variable name as long as you are consistent but xlApp is a generally recognized abbreviation convention.

Also the ActiveCell is probably not where you think it is. It is probably not in your FileTo or FileFrom workbooks. Since you used ComObjActive it is very likely that you had a workbook open before you run the script and the ActiveCell is probably in that workbook but then it is hard to predict where the ActiveCell might be when working with a lot of workbooks and worksheets.

I don't know what you are trying to accomplish but ActiveCell is probably not the answer.

FG

[hidefguy]

FG

Didn't realize I had a 1 in there, and although consistent, I changed it to an L so signify the Excel abbreviation. Seems you're right regarding the uncertainty of the ActiveCell location. This appears to be a problem when dealing with multiple open (FileFrom and FileTo) xlsx files. The goal was to be able to start the script from any row. Since the FileFrom files/reports also generate zeros(blanks), in addition to regular values, I can't just direct AHK/COM to look for next blank cell in row/column as a starting point. Probably end up just creating an input where I can enter the desired starting row number like Range "G@" where @=Row#, and script the Offsets from that point. All I can think of. I hate inputs

Thanks again for your help

[FanaticGuru]

Didn't realize I had a 1 in there, and although consistent, I changed it to an L so signify the Excel abbreviation.

That is just the problem. You were not consistent, some places in the script had a number 1 and others had a letter l. Consistent means 100% consistent throughout ever use of the variable throughout the script. I admit with the font the forum uses in code blocks it is hard to tell them apart but one mistype and your script will not properly function.

I can't just direct AHK/COM to look for next blank cell in row/column as a starting point.

Below is code that shows how to find the blank cell at the bottom of existing data in a column.

Code: Select all

#F12::
	xlApp := ComObjActive("Excel.Application")
	xlCell_EmptyBelowData := xlApp.Columns("D").Find("*",,,,,2).Offset(1,0)
	if  !xlCell_EmptyBelowData.Address
		xlCell_EmptyBelowData := xlApp.Range("D1")
	xlCell_EmptyBelowData.Value := "Put Data in Empty Cell"
	xlCell_EmptyBelowData.Offset(0,2).Value := "Data 2 cells to the right"
return

This will put data in the blank cell of column D and the cell two to the right with each push of Win+F12.

FG

[hidefguy]

That is just the problem. You were not consistent, some places in the script had a number 1 and others had a letter l. Consistent means 100% consistent throughout ever use of the variable throughout the script. I admit with the font the forum uses in code blocks it is hard to tell them apart but one mistype and your script will not properly function

You’re right. After a closer look at the code in earlier post, I just noticed the almost identical resemblance. Can’t believe I overlooked that.

Appreciate the blank cell code sample you posted. I’ll play around with it, and hopefully integrate it into my project.

[hidefguy]

Also the ActiveCell is probably not where you think it is. It is probably not in your FileTo or FileFrom workbooks. Since you used ComObjActive it is very likely that you had a workbook open before you run the script and the ActiveCell is probably in that workbook but then it is hard to predict where the ActiveCell might be when working with a lot of workbooks and worksheets.
I don't know what you are trying to accomplish but ActiveCell is probably not the answer

I figured out an alternative to using ActiveCell as a reference point by using an input/box for starting row/column number along with offsets in the script.
After script completes, before ExitApp, I would like to direct AHK to select desired active cells in this manner, so the file gets saved with Active cells in all sheets in a desired position.

FileTo.Sheets(1).Sheets(2).Sheets(3).ActiveCell.Offset(1,0).select

Tried that and get "ActiveCell word not recognized" error

Tried a single sheet > FileTo.Sheets(1).ActiveCell.Offset(1,0).select
Get the same error

Tried xlApp.Sheets(1).ActiveCell.Offset(1,0).select
Same error

[FanaticGuru]

After script completes, before ExitApp, I would like to direct AHK to select desired active cells in this manner, so the file gets saved with Active cells in all sheets in a desired position.

FileTo.Sheets(1).Sheets(2).Sheets(3).ActiveCell.Offset(1,0).select

Tried that and get "ActiveCell word not recognized" error

Tried a single sheet > FileTo.Sheets(1).ActiveCell.Offset(1,0).select
Get the same error

Tried xlApp.Sheets(1).ActiveCell.Offset(1,0).select
Same error

As was stated before ActiveCell is a property of only the Excel application. You can basically only do ActiveCell directly after xlApp. xlApp.ActiveCell.
A running application of Excel only has one ActiveCell regardless of how many workbooks and sheets there are.

You can not use sheets or workbooks. Neither sheets or workbooks have an active cell. There is only ever one active cell. It is the cell that if you was to start typing text would appear in. There is never a time when you could start typing and text would be typed in more than one cell.

Now Excel remembers where the last active cell was in each sheet, and when you change sheets the active cell changes to where ever the prompt was last time you were in that sheet. So I assume you are trying to set this memorized default positions.

You migth try something like:

Code: Select all

xlApp.Worksheet(2).Activate ; Activate the second sheet
xlApp.ActiveCell.Offset(1,0).Activate ; Activate the cell below the active cell in this sheet

Maybe that will cause it to remember that position next time you change to this sheet but untested.

FG

[hidefguy]

Now Excel remembers where the last active cell was in each sheet, and when you change sheets the active cell changes to where ever the prompt was last time you were in that sheet. So I assume you are trying to set this memorized default positions.

Bingo. That’s exactly the mechanism I’m looking for. I noticed, on multiple sheets that I can move the active cell(s) around and when saving file, it memorizes the last position(s). I’ll try out your sample code and post results. Thank you once again. I officially designate you AHK/COM master of the universe.

[hidefguy]

This ended up doing the trick. The @ signifies the starting row input/box and the offset moves the active cell down to next row.

FileTo.Sheets(1).select
FileTo.Sheets(1).Range("A"@).Offset(1,0).select
FileTo.Sheets(2).select
FileTo.Sheets(2).Range("A"@).Offset(1,0).select

[jeeswg]

- @FanaticGuru, thinks very much for the info. re. ActiveCell only applying to the active sheet, I believe this applies to Selection also. Although it appears that you *can* use Select without having to activate the sheet first. This had been causing me unexplained error messages.
- I couldn't find any workarounds to get the active/selected cells in a sheet, without first activating it. I checked methods/properties here, but no luck:
Application Object (Excel)
https://msdn.microsoft.com/en-us/vba/ex ... ject-excel
Worksheet Object (Excel)
https://msdn.microsoft.com/en-us/vba/ex ... ject-excel
Range.SpecialCells Method (Excel)
https://msdn.microsoft.com/en-us/vba/ex ... thod-excel

- [EDIT:] Actually, even with Select() there are complications:

Code: Select all

q:: ;focus cells in sheets
vTrue := -1
oXl := ComObjCreate("Excel.Application")
oXl.Visible := vTrue
oXl.Workbooks.Add
;oXl.Range("A3").Select() ;works
;oXl.ActiveSheet.Range("A3").Select() ;works
oXl.Sheets(1).Range("A3").Select() ;works if it's the active sheet
;oXl.Sheets(2).Range("A3").Select() ;didn't work
oXl := ""
return

- A certain syntax appears to work, however, it only works if the sheet happens to be active.

[hidefguy]

;oXl.Range("A3").Select() ;works
;oXl.ActiveSheet.Range("A3").Select() ;works
oXl.Sheets(1).Range("A3").Select() ;works if it's the active sheet
;oXl.Sheets(2).Range("A3").Select() ;didn't work

Just curious. Why the use of parentheses after select?

[jeeswg]

AFAIK, usually, if something is a property (get/set information), no parentheses are used, and if something is a method (perform an action), parentheses are used. Anyhow, I believe that that's how it works with standard AHK arrays. It seems that sometimes with Excel, certain methods work even if you omit the parentheses. People are welcome to correct/expand on what I just stated. Cheers.

[FanaticGuru]

- @FanaticGuru, thinks very much for the info. re. ActiveCell only applying to the active sheet, I believe this applies to Selection also. Although it appears that you *can* use Select without having to activate the sheet first. This had been causing me unexplained error messages.
- I couldn't find any workarounds to get the active/selected cells in a sheet, without first activating it. I checked methods/properties here, but no luck:
Application Object (Excel)
https://msdn.microsoft.com/en-us/vba/ex ... ject-excel
Worksheet Object (Excel)
https://msdn.microsoft.com/en-us/vba/ex ... ject-excel
Range.SpecialCells Method (Excel)
https://msdn.microsoft.com/en-us/vba/ex ... thod-excel

- [EDIT:] Actually, even with Select() there are complications:

Code: Select all

q:: ;focus cells in sheets
vTrue := -1
oXl := ComObjCreate("Excel.Application")
oXl.Visible := vTrue
oXl.Workbooks.Add
;oXl.Range("A3").Select() ;works
;oXl.ActiveSheet.Range("A3").Select() ;works
oXl.Sheets(1).Range("A3").Select() ;works if it's the active sheet
;oXl.Sheets(2).Range("A3").Select() ;didn't work
oXl := ""
return

- A certain syntax appears to work, however, it only works if the sheet happens to be active.

You are looking for a solution around something that is by design. I do not believe you can have a Select of a Sheet that is not active. Similar to ActiveCell, the Select always occurs on the ActiveSheet. You can by a round about way get there by switch and selecting in one command:

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlApp.Goto(xlApp.Sheets(2).Cells(6, 5))
xlApp.Selection.Value := 123

But this Goto will make the sheet become the active sheet.

Selection is like ActiveCell, each sheet memorizes the last position but you don't ever really have more than one Selection at a time. One ActiveCell and one Selection per application and they are both always in the active workbook on the active sheet. Also ActiveCell is always one cell that is a subset of Selection, which can be one or more cells. If it helps you could think of it as ActiveSelection which is basically what it is. All the active things there are only one of each at any given time.

Personally I try to use Select, Selection, Activate, and ActiveCell as little as possible. Basically if I only specifically need to know where the cursor is when I start and to specifically place the cursor when finished.

You see them a lot in VBA example code but I think a lot of that is the VBA is simulating the way a user would physically do things kind of the way the Excel VBA macro record works. But many of the Select and Selections from the macro record can be eliminated. For example in the above code there is no good reason normally to select a cell and then use Selection.Value := 123. Much easier to just do xlApp.Sheets(2).Cells(6, 5).Value := 123.

FG