CODEWITHSUNDEEP
excelautohotkey
Nikki Luzader
Nikki Luzader

Reputation: 111

Why am I getting a format/extension error when appending to an xls file with AutoHotkey?

I was appending to a .csv file with no problems.

Instead, I want to append to an .xlsx file, because I want to be able to make a nice looking table.

Unfortunately, upon opening the xlsx file, I am getting this message:

"Excel cannot open the file 'printlog.xls' because the file format or the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

It does not do this with a csv file.

There is no official documentation on the AHK website that says it is possible to FileAppend to .xlsx format, but there are plenty of examples online of people doing so.

Here is my code:

SetWorkingDir %A_ScriptDir%

ControlGetText, test, Edit10, TeamViewer

filename = %A_ScriptDir%\printlog.xlsx

FileAppend,
(
%test%,%test%,%test%,%test%
%test%,%test%,%test%,%test%
), %filename%

UPDATE: the first portion of this has been solved thanks to @user3419297's solution. I have a new problem listed below.

FilePath := A_ScriptDir "\printlog.xlsx"   ; get file path
SetWorkingDir %A_ScriptDir%                ; set working directory

ControlGetText, itemcode, Edit1, Print -   ; get item code from print menu
ControlGetText, quantity, Edit2, Print -   ; get quantity from print menu
ControlGetText, amount, Edit3, Print -     ; get amount from print menu
ControlGetText, initials, Edit4, Print -   ; get quantity from print menu
ControlGetText, info, Edit5, Print -       ; get quantity from print menu
ControlGetText, batch, Edit6, Print -      ; get quantity from print menu

Formattime,ts,,Shortdate                   ; generate short date variable

oExcel := ComObjCreate("Excel.Application")  ; create Excel COM object
oWorkbook := oExcel.Workbooks.Open(FilePath) ; open workbook in Excel object
oExcel.Visible := false                      ; work in background

                                               ; check for blank rows and append values
For Row in oExcel.Range["A1"]                  ; starting with the first row
{
        If (Row.Value = "")                        ; if first row is blank
        {
                oExcel.Range("A1").Value := %itemcode% ; set value of cell A-G
                oExcel.Range("B1").Value := %quantity%
                oExcel.Range("C1").Value := %amount%
                oExcel.Range("D1").Value := %initials%
                oExcel.Range("E1").Value := %info%
                oExcel.Range("F1").Value := %batch%
                oExcel.Range("G1").Value := %ts%
        }
        else ; else find the next blank row and set values of A-G
        For eachRow in oExcel.Range["A1:A" oExcel.Columns("A").Find[""].Row]
        {
        If (eachRow.Value = "")
            {
                        oExcel.Range("A" . A_Index).Value := %itemcode%
                        oExcel.Range("B" . A_Index).Value := %quantity%
                        oExcel.Range("C" . A_Index).Value := %amount%
                        oExcel.Range("D" . A_Index).Value := %initials%
                        oExcel.Range("E" . A_Index).Value := %info%
                        oExcel.Range("F" . A_Index).Value := %batch%
                        oExcel.Range("G" . A_Index).Value := %ts%
                        break
            }
        }
}
oWorkbook.Save() ; save workbook and close excel
oExcel.Quit()

error image

as you can see the error that I am getting is an illegal character. I don't understand because the contents of the variable is the string. Does this have something to do with the format of the variable in itself, or is it the format in which it is being appended to the excel file?

Upvotes: 2

Views: 561

Answers (2)

CodeKiller
CodeKiller

Reputation: 107

For your second problem with the screenshot it is easy, as I made this one a lot of time...

This :

oExcel.Range("A1").Value := %itemcode%

Means : take the value of the variable which the name is the content of the variable itemcode ("this. is. a. test." I guess ?).

It cannot work in your case. Example :

itemcode := "A B"
msgbox %itemcode% ; display will be : A B

So this :

Test := %itemcode%

is the same as :

Test = %A B%

Not compiling. :-) So you need to remove the % % unless you have dynamic variables (rarely the case) so you store their name in an other variable (varception ?).

oExcel.Range("A1").Value := itemcode

Upvotes: 1

Relax
Relax

Reputation: 10568

To append text to an .xlsx file, you have to use COM:

test := "my text"

FilePath := A_ScriptDir "\printlog.xlsx"
oExcel := ComObjCreate("Excel.Application")
oWorkbook := oExcel.Workbooks.Open(FilePath)
oExcel.Visible := false
oExcel.Range("A1").Value := test
oWorkbook.Save()
oExcel.Quit()

Another example:

Append the first 3 words of the copied text to the next empty row in their respective columns a,b,c:

FilePath := A_ScriptDir "\printlog.xlsx"

Array:=[]
Array := StrSplit(clipboard, " ")

oExcel := ComObjCreate("Excel.Application")
oWorkbook := oExcel.Workbooks.Open(FilePath)
oExcel.Visible := false
For Row in oExcel.Range["A1"]
{
    If (Row.Value = "")
     {
        oExcel.Range("A1").Value := Array[1]
        oExcel.Range("B1").Value := Array[2]
        oExcel.Range("C1").Value := Array[3]
    }
    else
    For eachRow in oExcel.Range["A1:A" oExcel.Columns("A").Find[""].Row]
    {
     If (eachRow.Value = "")
       {
            oExcel.Range("A" . A_Index).Value := Array[1]
            oExcel.Range("B" . A_Index).Value := Array[2]
            oExcel.Range("C" . A_Index).Value := Array[3]
                break
        }
    }
}
oWorkbook.Save()
oExcel.Quit()

Upvotes: 1

Related Questions