A weird problem of converting xls to xlsx Topic is solved

[songdg]

I want to convert some xls(tsv in fact) files to xlsx format.

Code: Select all

xlApp := ComObjActive("Excel.Application")
xlWb := xlApp.ActiveWorkbook
FilePathNoExt := RegExReplace(xlWb.FullName,"\..*?$") ; Remove last . and everything after
xlApp.DisplayAlerts := false ; avoid overwrite alerts
xlWB.SaveAs(FilePathNoExt ".xlsx", xlOpenXMLWorkbook := 51)
xlApp.DisplayAlerts := true

If I open one xls file manually, and then run the code above it works well. However if I want to open that xls file automatically and then convert it, problem occurs.

Code: Select all

Run, %A_WorkingDir%\Table.xls
Sleep, 2000
ControlGetPos , X, Y, Width, Height, Button1, Microsoft Excel
ControlClick, Button1, Microsoft Excel
Sleep, 2000
Click, %X%, %Y%
xlApp := ComObjActive("Excel.Application")
xlWb := xlApp.ActiveWorkbook
FilePathNoExt := RegExReplace(xlWb.FullName,"\..*?$") ; Remove last . and everything after
xlApp.DisplayAlerts := false ; avoid overwrite alerts
xlWB.SaveAs(FilePathNoExt ".xlsx", xlOpenXMLWorkbook := 51)
xlApp.DisplayAlerts := true

[mikeyww]

You do need an open file if you are going to use ComObjActive. I think you complicated the testing process by combining so many steps. I simplified and found that the following basic "conversion" process (saving the file) worked. You could test this and see if it works for you. If it does, then you can expand the script.

Code: Select all

#Requires AutoHotkey v1.1.33
xls  := A_ScriptDir "\test2.xls"
out  := A_ScriptDir "\test2-new.xlsx"
XL   := ComObjActive("Excel.Application")
xlWb := XL.ActiveWorkbook
xlWB.SaveAs(out, xlOpenXMLWorkbook := 51)
MsgBox 64, Status, Done!

Start with a new XLS file, saved in XLS format. Verify that you can first open the file normally in Excel.

[flyingDman]

Your table.xls file is not an Excel file (see error msg below). It won't open in excel if you double click it (but it will open in a text editor if you change the extension to .txt). It is just a tab separated values file and the best way to incorporate it into an existing xlsx file is to read it with fileread like so.

Code: Select all

fileread, var, table.xls
clipboard := Var
xl := ComObjActive("excel.application")
xl.activesheet.range("a1").pastespecial(-4104)

or creating a new xlsx file

Code: Select all

fileread, var, table.xls
clipboard := Var
xl := ComObjCreate("excel.application")
xl.visible := true
wrkbk := xl.workbooks.add
xl.activesheet.range("a1").pastespecial(-4104)

You can then use wrkbk.saveas(path) to save it.

If you had a "real" .xls file you would do something like this:

Code: Select all

xl := ComObjCreate("excel.application")
flnm := "testfile.xls"
wrkbk := xl.workbooks.open(flnm)
wrkbk.SaveAs(substr(wrkbk.fullname,1,-4), 51)
wrkbk.close(0)
xl.quit()

20230316_151516.jpg (23.23 KiB) Viewed 323 times

To avoid using the clipboard use:

Spoiler

Code: Select all

var =
(
a	b	c
d	e
K		i
j	k	l
)

MxCl := 0, MnRw := 1, MnCl := 1							; MnRw and MnCl address of 1st cell of range to paste in
ClDl := a_tab
for x,y in z:=strsplit(var,"`n","`r")							; or strsplit(var,"`r", "`n") depending on what the end of line code(s) is/are	
	MxCl := max(MxCl,strsplit(y,ClDl).Length())
MxRw := z.Length()
sArr := ComObjArray(12, MxRw, MxCl)
for x,y in z
	for a,b in strsplit(y,ClDl)
		sArr[x - 1, a - 1] := b
xl := ComObjActive("excel.application")
xl.Range(xl.Cells(MnRw, MnCl), xl.cells(MnRw+MxRw-1,MnCl+MxCl-1)).Value := sArr

[songdg]

Your table.xls file is not an Excel file (see error msg below). It won't open in excel if you double click it (but it will open in a text editor if you change the extension to .txt). It is just a tab separated values file and the best way to incorporate it into an existing xlsx file is to read it with fileread like so.

Code: Select all

fileread, var, table.xls
clipboard := Var
xl := ComObjActive("excel.application")
xl.activesheet.range("a1").pastespecial(-4104)

or creating a new xlsx file

Code: Select all

fileread, var, table.xls
clipboard := Var
xl := ComObjCreate("excel.application")
xl.visible := true
wrkbk := xl.workbooks.add
xl.activesheet.range("a1").pastespecial(-4104)

You can then use wrkbk.saveas(path) to save it.

If you had a "real" .xls file you would do something like this:

Code: Select all

xl := ComObjCreate("excel.application")
flnm := "testfile.xls"
wrkbk := xl.workbooks.open(flnm)
wrkbk.SaveAs(substr(wrkbk.fullname,1,-4), 51)
wrkbk.close(0)
xl.quit()

20230316_151516.jpg

To avoid using the clipboard use:

Spoiler

Code: Select all

var =
(
a	b	c
d	e
K		i
j	k	l
)

MxCl := 0, MnRw := 1, MnCl := 1							; MnRw and MnCl address of 1st cell of range to paste in
ClDl := a_tab
for x,y in z:=strsplit(var,"`n","`r")							; or strsplit(var,"`r", "`n") depending on what the end of line code(s) is/are	
	MxCl := max(MxCl,strsplit(y,ClDl).Length())
MxRw := z.Length()
sArr := ComObjArray(12, MxRw, MxCl)
for x,y in z
	for a,b in strsplit(y,ClDl)
		sArr[x - 1, a - 1] := b
xl := ComObjActive("excel.application")
xl.Range(xl.Cells(MnRw, MnCl), xl.cells(MnRw+MxRw-1,MnCl+MxCl-1)).Value := sArr

Thanks, much appreciated for your help

[songdg]

You do need an open file if you are going to use ComObjActive. I think you complicated the testing process by combining so many steps. I simplified and found that the following basic "conversion" process (saving the file) worked. You could test this and see if it works for you. If it does, then you can expand the script.

Code: Select all

#Requires AutoHotkey v1.1.33
xls  := A_ScriptDir "\test2.xls"
out  := A_ScriptDir "\test2-new.xlsx"
XL   := ComObjActive("Excel.Application")
xlWb := XL.ActiveWorkbook
xlWB.SaveAs(out, xlOpenXMLWorkbook := 51)
MsgBox 64, Status, Done!

Start with a new XLS file, saved in XLS format. Verify that you can first open the file normally in Excel.

Thanks, it save a lot of click work by using FindText.