Overwrite last row from one workbook to another Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
euras
Posts: 429
Joined: 05 Nov 2015, 12:56

Overwrite last row from one workbook to another

16 Jan 2017, 07:17

Hi, I need Your help With some simple request.

I have 2 CSV file and I want to copy last row from file "1.csv" and paste that row to file "2.csv". Both files are not open. How to do that?
User avatar
jmeneses
Posts: 524
Joined: 28 Oct 2014, 11:09
Location: Catalan Republic

Re: Overwrite last row from one workbook to another

16 Jan 2017, 10:14

Hi euras, i'm sure there are more elegant ways but....
there goes that

Code: Select all

xlPasteAll := -4104
                                                       
fCsv1 := "C:\Temp\csv1.CSV"
fCsv2 := "C:\Temp\csv2.CSV"

oCSV1 := ComObjCreate("Excel.Application")
oCSV1.Workbooks.open(fCsv1,, readonly := true)
oCSV1.Visible := 1 

oCSV2 := ComObjCreate("Excel.Application")
oCSV2.Workbooks.open(fCsv2)
oCSV2.Visible := 1                                                                            

oRngCSV1 := "A" RegExReplace(oCSV1.ActiveCell.SpecialCells(xlLastCell:=11).Address,"\D") ":"
	 . RegExReplace(oCSV1.ActiveCell.SpecialCells(xlLastCell:=11).Address,"[^A-Z]")  
   . RegExReplace(oCSV1.ActiveCell.SpecialCells(xlLastCell:=11).Address,"\D")
oCSV1.Range(oRngCSV1).Copy()

oRngCSV2 := "A" RegExReplace(oCSV2.ActiveCell.SpecialCells(xlLastCell:=11).Address,"\D") + 1 ":"
	 . RegExReplace(oCSV2.ActiveCell.SpecialCells(xlLastCell:=11).Address,"[^A-Z]")  
   . RegExReplace(oCSV2.ActiveCell.SpecialCells(xlLastCell:=11).Address,"\D") + 1

oCSV2.Range(oRngCSV2).pastespecial(xlPasteAll)

oCSV1.ActiveWorkbook.Close(0)      
oCSV2.Save() 
oCSV1:=""
oCSV2:=""	

;Msgbox 0x40000,, % "Copied1!!`n`n" fCsv1 " Range "  oRngCSV1 "`nto`n" fCsv2 " Range "  oRngCSV2
ExitApp

Donec Perficiam
User avatar
FanaticGuru
Posts: 1688
Joined: 30 Sep 2013, 22:25

Re: Overwrite last row from one workbook to another

16 Jan 2017, 14:22

jmeneses wrote:Hi euras, i'm sure there are more elegant ways but....
Below technically does not "paste" the row as the OP asked but it does copy the row.

Code: Select all

F7::
	Xl := ComObjCreate("Excel.Application")
	Xl.Visible := true
	Wb1 := Xl.Workbooks.Open("D:\Users\FG\Documents\1.csv")
	Wb2 := Xl.Workbooks.Open("D:\Users\FG\Documents\2.csv")
	Row := Wb1.ActiveSheet.UsedRange.Rows.Count
	Wb1.Sheets(1).Rows(Row).Copy(Wb2.Sheets(1).Rows(Row))
return

F8::
	Xl := ComObjCreate("Excel.Application")
	Wb1 := Xl.Workbooks.Open("D:\Users\FG\Documents\1.csv")
	Wb2 := Xl.Workbooks.Open("D:\Users\FG\Documents\2.csv")
	Row := Wb1.ActiveSheet.UsedRange.Rows.Count
	Wb1.Sheets(1).Rows(Row).Copy(Wb2.Sheets(1).Rows(Row))
	Wb2.Save()
	Xl.DisplayAlerts := false
	Xl.Quit()
return
F7 will open the files and do the copy for you to see.
F8 does the entire process invisibly and saves the change to 2.csv.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
[Function] Timer - Create and Manage Timers
euras
Posts: 429
Joined: 05 Nov 2015, 12:56

Re: Overwrite last row from one workbook to another

17 Jan 2017, 06:15

there was a little mistake that last line from file "1.csv" was overwrite to the same line in file "2.csv". I fixed it:

Code: Select all

Xl := ComObjCreate("Excel.Application")
Wb1 := Xl.Workbooks.Open("1.csv")
Wb2 := Xl.Workbooks.Open("2.csv")
Row := Wb1.ActiveSheet.UsedRange.Rows.Count
Row2 := Wb2.ActiveSheet.UsedRange.Rows.Count
Wb1.Sheets(1).Rows(Row).Copy(Wb2.Sheets(1).Rows(Row2+1))
Wb2.Save()
Xl.DisplayAlerts := false
Xl.Quit()
User avatar
FanaticGuru
Posts: 1688
Joined: 30 Sep 2013, 22:25

Re: Overwrite last row from one workbook to another

17 Jan 2017, 13:33

euras wrote:there was a little mistake that last line from file "1.csv" was overwrite to the same line in file "2.csv". I fixed it:

Code: Select all

Xl := ComObjCreate("Excel.Application")
Wb1 := Xl.Workbooks.Open("1.csv")
Wb2 := Xl.Workbooks.Open("2.csv")
Row := Wb1.ActiveSheet.UsedRange.Rows.Count
Row2 := Wb2.ActiveSheet.UsedRange.Rows.Count
Wb1.Sheets(1).Rows(Row).Copy(Wb2.Sheets(1).Rows(Row2+1))
Wb2.Save()
Xl.DisplayAlerts := false
Xl.Quit()
Yes, I was not sure if you were looking to copy the row to the same position or to the end of the second file. Luckily the logic of the code is pretty easy to follow and you were able to adapt it to your needs.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
[Function] Timer - Create and Manage Timers
euras
Posts: 429
Joined: 05 Nov 2015, 12:56

Re: Overwrite last row from one workbook to another

20 Jan 2017, 05:19

FanaticGuru wrote:
euras wrote:there was a little mistake that last line from file "1.csv" was overwrite to the same line in file "2.csv". I fixed it:
Yes, I was not sure if you were looking to copy the row to the same position or to the end of the second file. Luckily the logic of the code is pretty easy to follow and you were able to adapt it to your needs.

FG
One problem when both files have the same name.. I get an error than. Is it possible to fix it?

Code: Select all

Sign := A_Username
Wb1 := Xl.Workbooks.Open("C:\Users\" Sign "\Desktop\" Sign ".csv")
Wb2 := Xl.Workbooks.Open("J:\****\****\****\" Sign ".csv")	
User avatar
FanaticGuru
Posts: 1688
Joined: 30 Sep 2013, 22:25

Re: Overwrite last row from one workbook to another  Topic is solved

21 Jan 2017, 00:39

euras wrote:One problem when both files have the same name.. I get an error than. Is it possible to fix it?

Code: Select all

Sign := A_Username
Wb1 := Xl.Workbooks.Open("C:\Users\" Sign "\Desktop\" Sign ".csv")
Wb2 := Xl.Workbooks.Open("J:\****\****\****\" Sign ".csv")	
One instance of Excel cannot have two files open with the same name even if the paths are different. Even if you try to do that manually Excel will normally choke.

And in order to change the name of an open workbook you have to save it.

So you have to open the first workbook, save a temp copy with a different name in order to change its name, load your second workbook, do your thing, then delete the temp copy.

Code: Select all

Xl := ComObjCreate("Excel.Application")
Wb1 := Xl.Workbooks.Open("D:\Other Documents\1.csv")
Wb1TempFile := A_Temp "\Something Unique.tmp" 
Wb1.SaveAs(Wb1TempFile)
Wb2 := Xl.Workbooks.Open("D:\Users\FG\Documents\1.csv")
Row := Wb1.ActiveSheet.UsedRange.Rows.Count
Row2 := Wb2.ActiveSheet.UsedRange.Rows.Count
Wb1.Sheets(1).Rows(Row).Copy(Wb2.Sheets(1).Rows(Row2+1))
Wb1.Close()
FileDelete, % Wb1TempFile
Wb2.Save()
Xl.DisplayAlerts := false
Xl.Quit()
I am not a fan of creating temporary files like this but it is the easier way of dealing with wanting to open two Excel files with the same name.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
[Function] Timer - Create and Manage Timers

Return to “Ask For Help”

Who is online

Users browsing this forum: Google [Bot], MagEpub, TAC109 and 57 guests