[help] : insert into last row of excel sheet Topic is solved

[navmesh]

hello,
i have an excel filename "E:\h\ahk\off.xlsm"
i want to paste the value of variable %var1% into the empty row next to the last "used" row of column A ,for example column A has 6 used row, i want to paste it into the row 7 of column "A" ("A7" cell)
then paste %var2% into the cell in the same row but in the next column (in the previous example ,which is cell "B7")

i tried writing a script to write data to excel but it keeps open new window of that file, instead of switching to it and write ,and the result is i have multiple windows of the same file ( i use windows 10 64 bits )
thanks

[FanaticGuru]

hello,
i have an excel filename "E:\h\ahk\off.xlsm"
i want to paste the value of variable %var1% into the empty row next to the last "used" row of column A ,for example column A has 6 used row, i want to paste it into the row 7 of column "A" ("A7" cell)
then paste %var2% into the cell in the same row but in the next column (in the previous example ,which is cell "B7")

i tried writing a script to write data to excel but it keeps open new window of that file, instead of switching to it and write ,and the result is i have multiple windows of the same file ( i use windows 10 64 bits )
thanks

This acts on the open active sheet in Excel.

Code: Select all

var1 := "One"
var2 := "Two"

xlApp := ComObjActive("Excel.Application")
xlCell_EmptyBelowData := xlApp.Columns("A").Find("*",,,,,2).Offset(1,0)	; get cell just below last cell with anything in it
xlCell_EmptyBelowData.Value := var1
xlCell_EmptyBelowData.Offset(0,1).Value := var2

This is mainly to demonstrate how to find the last empty cell in a column and then use offsets to get the cells you want from there.

FG

[navmesh]

Thank you for the help
Unfortunately it does not work with me, it switch to excel windows but nothing inserted

[FanaticGuru]

Thank you for the help
Unfortunately it does not work with me, it switch to excel windows but nothing inserted

I can tell you that it works for me on multiple computers using different versions of Excel.

Nothing about the script will cause "it switch to excel windows" so maybe you are attempting to intergrate with your own code which if the case you should post. An Excel workbook just has to be running. It can even be minimized.

One important note is that column A cannot be completely empty. This script looks for the last cell with data in it. If there is no data in the column then it cannot find the first empty cell below data.

You can add a check for a case like that.

Code: Select all

var1 := "One"
var2 := "Two"

xlApp := ComObjActive("Excel.Application")
xlCell_EmptyBelowData := xlApp.Columns("A").Find("*",,,,,2).Offset(1,0)	; get cell just below last cell with anything in it
if  !xlCell_EmptyBelowData.Address
	xlCell_EmptyBelowData := xlApp.Range("A1")

xlCell_EmptyBelowData.Value := var1
xlCell_EmptyBelowData.Offset(0,1).Value := var2

FG

[navmesh]

Nothing about the script will cause "it switch to excel windows" so maybe you are attempting to intergrate with your own code which if the case you should post. An Excel workbook just has to be running. It can even be minimized.

Thank you very much, FanaticGuru
it switch to excel windows because i add my code to open my excel file if not exist
i forgot the case that the column A is empty, that's why it does not work
now it works fine,
thank you very much for the help, i really appreciate it