excel comobject problem

[ivill]

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").Value
xl.ActiveWorkbook.Save
xl.Quit
xl := ComObjCreate("Excel.Application")
xl.Workbooks.Open("C:\Users\iViLL\OneDrive\A\2.xlsx")
xl.Visible := false
xl.range("B21:AS21").Value := B17AS21
xl.ActiveWorkbook.Save
xl.Quit

Hi, all, in my script, the line 4&10 seems not working...at all, any advise?

[Descolada]

I think your code does not close the Excel process, so if you run it more than once then it keeps your second xls file as read-only and prevents further changes from being made. Open Task Manager with Ctrl+Alt+Del and kill all "Microsoft Excel" processes. Then try this:

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").Value
xl.ActiveWorkbook.Save
xl.ActiveWorkbook.Close(0)
xl.Quit()
xl := ComObjCreate("Excel.Application")
xl.Workbooks.Open("C:\Users\iViLL\OneDrive\A\2.xlsx")
xl.Visible := false
xl.range("B21:AS21").Value := B17AS21
xl.ActiveWorkbook.Save
xl.ActiveWorkbook.Close(0)
xl.Quit()

[ivill]

I think your code does not close the Excel process, so if you run it more than once then it keeps your second xls file as read-only and prevents further changes from being made. Open Task Manager with Ctrl+Alt+Del and kill all "Microsoft Excel" processes. Then try this:

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").Value
xl.ActiveWorkbook.Save
xl.ActiveWorkbook.Close(0)
xl.Quit()
xl := ComObjCreate("Excel.Application")
xl.Workbooks.Open("C:\Users\iViLL\OneDrive\A\2.xlsx")
xl.Visible := false
xl.range("B21:AS21").Value := B17AS21
xl.ActiveWorkbook.Save
xl.ActiveWorkbook.Close(0)
xl.Quit()

Hi, it works like a charm, but there's another issue, how can i be able to use B17AS21 := xl.range("B21:AS21").Text instead of B17AS21 := xl.range("B21:AS21").Value ? with single cell, B17 := xl.range("B17).Text will works.
p.s. i think this is why i'm using the following code to get the text of cells, though it is stupid solution:

Code: Select all

B17 := xl.Range("B17").Text
C17 := xl.Range("C17").Text
D17 := xl.Range("D17").Text
E17 := xl.Range("E17").Text
F17 := xl.Range("F17").Text
G17 := xl.Range("G17").Text
H17 := xl.Range("H17").Text
I17 := xl.Range("I17").Text
J17 := xl.Range("J17").Text
K17 := xl.Range("K17").Text
L17 := xl.Range("L17").Text
M17 := xl.Range("M17").Text
N17 := xl.Range("N17").Text
O17 := xl.Range("O17").Text
P17 := xl.Range("P17").Text
Q17 := xl.Range("Q17").Text
R17 := xl.Range("R17").Text
S17 := xl.Range("S17").Text
T17 := xl.Range("T17").Text
U17 := xl.Range("U17").Text
V17 := xl.Range("V17").Text
W17 := xl.Range("W17").Text
X17 := xl.Range("X17").Text
Y17 := xl.Range("Y17").Text
Z17 := xl.Range("Z17").Text
AA17 := xl.Range("AA17").Text
AB17 := xl.Range("AB17").Text
AC17 := xl.Range("AC17").Text
AD17 := xl.Range("AD17").Text
AE17 := xl.Range("AE17").Text
AF17 := xl.Range("AF17").Text
AG17 := xl.Range("AG17").Text
AH17 := xl.Range("AH17").Text
AI17 := xl.Range("AI17").Text
AJ17 := xl.Range("AJ17").Text
AK17 := xl.Range("AK17").Text
AL17 := xl.Range("AL17").Text
AM17 := xl.Range("AM17").Text
AN17 := xl.Range("AN17").Text
AO17 := xl.Range("AO17").Text
AP17 := xl.Range("AP17").Text
AQ17 := xl.Range("AQ17").Text
AR17 := xl.Range("AR17").Text
AS17 := xl.Range("AS17").Text

B18 := xl.Range("B18").Text
C18 := xl.Range("C18").Text
... ...
... ...
AS21 := xl.Range("AS21").Text

[Descolada]

It depends on what you plan to do with the cell texts. This for example would loop through the range and display cell texts one by one:

Code: Select all

B17AS21 := xl.range("B21:AS21")
for cell, in B17AS21 {
  ToolTip, % "Cell text: " cell.Text
  Sleep, 500
}

[ivill]

It depends on what you plan to do with the cell texts. This for example would loop through the range and display cell texts one by one:

Code: Select all

B17AS21 := xl.range("B21:AS21")
for cell, in B17AS21 {
  ToolTip, % "Cell text: " cell.Text
  Sleep, 500
}

Hi, I want to copy cell text(not cell value) to another sheet

[Descolada]

There probably is a more elegant way of doing it, but this should work:

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()

[flyingDman]

Try using the copy method (https://docs.microsoft.com/en-us/office/vba/api/excel.range.copy):

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk1 := xl.Workbooks.Open(a_scriptdir . "\test123A.xlsx")						; source
wrkbk2 := xl.Workbooks.Open(a_scriptdir . "\test123B.xlsx")						; target
wrkbk1.ActiveSheet.range("A1:A20").copy(wrkbk2.ActiveSheet.range("A1:A20"))
wrkbk1.close(1)
wrkbk2.close(1)
xl.quit()

[ivill]

There probably is a more elegant way of doing it, but this should work:

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()

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

it works, but it seems like a little issue, this 1.xlsm did not end with xl.quit, while the script was done, i opened the 1.xlsm, it notified "read only", i had to end excel process to fix it.

[Descolada]

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()

[ivill]

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

[ivill]

Try using the copy method (https://docs.microsoft.com/en-us/office/vba/api/excel.range.copy):

Code: Select all

xl := ComObjCreate("excel.application")
wrkbk1 := xl.Workbooks.Open(a_scriptdir . "\test123A.xlsx")						; source
wrkbk2 := xl.Workbooks.Open(a_scriptdir . "\test123B.xlsx")						; target
wrkbk1.ActiveSheet.range("A1:A20").copy(wrkbk2.ActiveSheet.range("A1:A20"))
wrkbk1.close(1)
wrkbk2.close(1)
xl.quit()

Hi, i need to copy the cells text, this code seems not working for me, thanks anyway

[ivill]

There probably is a more elegant way of doing it, but this should work:

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()

Can i active the source sheet again, then save&quit, to ignore the read only warning
p.s.
it's not working, i put
xl.WorkBooks("1.xlsm").Activate
xl.ActiveWorkbook.Save
xl.Quit
at the end, and run the script, i got the error "Invalid index"

[flyingDman]

It would be helpful if you tell me what does not work. The copy function together with the "pastespecial" function allow you to customize the result. To copy just the values use:

Code: Select all

wrkbk1.ActiveSheet.range("A1:A20").copy
wrkbk2.ActiveSheet.range("A1:A20").pastespecial(-4163)

see here for other possibilities: https://docs.microsoft.com/en-us/office/vba/api/excel.xlpastetype