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
[help] : insert into last row of excel sheet Topic is solved
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: [help] : insert into last row of excel sheet Topic is solved
This acts on the open active sheet in Excel.navmesh wrote: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
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
FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
Re: [help] : insert into last row of excel sheet
Thank you for the help
Unfortunately it does not work with me, it switch to excel windows but nothing inserted
Unfortunately it does not work with me, it switch to excel windows but nothing inserted
- FanaticGuru
- Posts: 1906
- Joined: 30 Sep 2013, 22:25
Re: [help] : insert into last row of excel sheet
I can tell you that it works for me on multiple computers using different versions of Excel.navmesh wrote:Thank you for the help
Unfortunately it does not work with me, it switch to excel windows but nothing inserted
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
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
Re: [help] : insert into last row of excel sheet
Thank you very much, FanaticGuruFanaticGuru wrote:
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.
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
Who is online
Users browsing this forum: Google [Bot], Joey5 and 181 guests