Descolada wrote: ↑03 Apr 2022, 13:44
Yes, my example forgot to close the first workbook. You could use @flyingDman's method or use a combination of our methods:
Code: Select all
xl := ComObjCreate("excel.application")
wrkbk1 := xl.Workbooks.Open("G:\My Drive\Untitled folder\1.xlsm")
wrkbk2 := xl.Workbooks.Open("C:\Users\iViLL\OneDrive\A\2.xlsx")
B17AS21 := wrkbk1.Sheets("sheet1").range("B21:AS21")
for cell, in B17AS21 {
wrkbk2.ActiveSheet.Cells(cell.Row, cell.Column) := cell.Text
}
wrkbk1.close(1)
wrkbk2.close(1)
xl.quit()
hi, i've been tested with this new code, it takes 2m30s to finish the job, it's x times slower than the below mentioned solution
Code: Select all
xl := ComObjCreate("Excel.Application")
xl.Workbooks.Open("G:\My Drive\Untitled folder\1.xlsm")
xl.Sheets("sheet1").Select
B17AS21 := xl.range("B21:AS21")
xl.ActiveWorkbook.Save
xl.Workbooks.Open("C:\Users\iViLL\OneDrive\A\2.xlsx")
xl.Visible := false
for cell, in B17AS21 {
xl.Cells(cell.Row, cell.Column) := cell.Text
}
xl.ActiveWorkbook.Save
xl.ActiveWorkbook.Close(0)
xl.Quit()
this code only takes 15s to finish, though i had to click "close" after i re-open the 1.xlsm and 2.xlsx