AHK & Excel

[DLP]

Hello,

I need help with the following: How to make a single step script for this.

Step1: Build Excel file (ComObject)

Step2: Convert .txt to .xls

Step3: Copy Header from Book1.xls (Copy A1:W1 and paste it into the Excel file after the step 1 & 2 for all Excel file generated)

Actually I have to select each file manually, it would be easier to make 1 step script that will select each .txt in the script folder & do step1, step2 & step3 while Excel is not open/visible, if possible. It has to overwrite the file without popping a window Yes/No overwrite (Actually it does ask) or if the pasted format is a little larger it poppup a window yes/no for the paste.

I hope this is acheivable, thanks for the help!

Step1 Code
It select the .txt and paste the data into book1.xls (it use it format, like a template). I need to select each time the file until I am done and I stop the script.

Code: Select all

#persistent


FileSelectFile, SelectedFile
FullPath = %A_ScriptDir%\Book1.xls

oWorkBook := ComObjGet(FullPath)
oWorkbook.Application.Windows(oWorkbook.Name).Visible := 1

fileread, filevar, %SelectedFile%

clipboard = %filevar%

NewStr := RegExReplace(SelectedFile, ".txt", ".xls", Value1)

oWorkbook.Worksheets("Feuil1").Range("A:W").PasteSpecial

oWorkbook.SaveAs(SelectedFile)
oWorkbook.Close(1)	; save changes and close Workbook
oWorkBook := "", oSheet := ""	; release references


sleep, 250

reload

return

numpad7::
reload
return

numpad9::
exitapp
return

Step2 Code Can be easily added to the single step script

Code: Select all

filemove, %a_scriptdir%\*.txt, %a_scriptdir%\*.xls

Step3
I need to open Book1.xls (after I edited the header A1:W1 in this file) select A1:W1 and copy (it get stored into the clipboard) then I do this Step3, again I need to select each file manually.

Code: Select all

FileSelectFile, fullpath


oWorkBook := ComObjGet(FullPath)
oWorkbook.Application.Windows(oWorkbook.Name).Visible := 1

oWorkbook.Worksheets("Feuil1").Range("A1:W1").PasteSpecial
oWorkbook.Close(1)	; save changes and close Workbook
oWorkBook := "", oSheet := ""	; release references


sleep, 500

reload

return

numpad7::
reload
return

numpad9::
exitapp
return

[Gio]

Hello DLP.

Welcome to the AutoHotkey community forums.

AutoHotkey is capable of automating Excel through COM and should certainly be able to do what you are asking for. However, i tried some of your code and it seems to be not functional at all (which is fine, since Learning to operate Excel through COM does require some more time and practice than most other subjects).

Your question is also too broad and a single forum post is probably not suitable to explain everything. You will need to debugg ealier steps (and possibly post questions about them) before later steps can be accomplished. But do bear with us by asking for new directions as soon as you have succesfully completed each step (or not), and we may soon get everything operational.

1 - Let us begin by opening an existing Excel File and making it visible. The syntax goes like this:

Code: Select all

Path := A_ScriptDir . "/Book1.xls"
Xl := ComObjCreate("Excel.Application")
Xl_Workbook := Xl.Workbooks.Open(Path) ; open an existing file AND ALSO get a handle to the current workbook.
Xl.Visible := True

The first line should contain a string with the complete folder and file path. A_ScriptDir will dereference to the scripts current folder path. "Book1.xls" must exist inside that folder. Do remember .xls is for Excel 97-2003 files (newer excel files use .xlsx extension).

How does that work? Did you succesfully opened the excel file?

2 - reading from text files to the clipboard
You have correctly guessed this one. FileRead followed by an assignmet to the clipboard is the way to go.

3 - Pasting values in the clipboard to an excel sheet
Selecting a broader range than the data to be pasted may cause Excel to exhibit a pop-up warning the user. You can avoid this by provind a single inital cell as the range of PasteSpecial. If the data contains either tabs or enters, Excel will paste the data into more than one cell automatically.

Code: Select all

Xl.Range("A1").PasteSpecial

How does that work? Did it suffice for pasting the data you whanted to paste?

4 - Quickly saving the file and than closing Excel.

This can be acomplished like this:

Code: Select all

Xl_Workbook.Save()
Xl_Workbook.Close(1)

* Code above will only work if you have opened the file using the code in section 1, because we need XL_Workbook to be a valid reference.

5 - Reloading the script
There is actually no need to release references to the objects if you are going to reload the script instead of looping it.

6 - Converting a .txt file to a .xls file
This refers to your "step 2". Converting a file is not as straightforward as renaming the file by the means of FileMove. The data in an Excel file is not encoded as raw text (such as in a text-file). Instead, you should either find a coversion tool, create one yourself or just do something like you have done in step one, by reading the value from the text value, pasting it into Excel itself and having Excel saving the file as .xls

7 - opening the excel file and reading data from it
Opening the file has been covered in section 1. Getting data from it is pretty much straightforward but the output is NOT always plain text. If the range to be copied contains more than 1 cell, you will get a table object (2d object) as output, otherwise you will get plain text in a regular variable as output.

Object as output (when more than 1 cell is copied from):

Code: Select all

VALUE_TO_READ := Xl.Range("A1:B1").Value
msgbox % VALUE_TO_READ[1,1] ; Displays text in A1
msgbox % VALUE_TO_READ[1,2] ; Displays text in B1

Plain text inside a variable as output (when only 1 cell was copied):

Code: Select all

VALUE_TO_READ := Xl.Range("A1").Value
msgbox % VALUE_TO_READ

So how did it all go? Have any more doubts? Feel free to ask if so

Best wishes.

[DLP]

Hello Gio,

I am currently using this script for like 1 year and it work perfectly, the only matter is that I need to manually select file by file. (If I have 50 files, it is such boring to do...)

Yes I am using .xls and not the .xlsx

I am extracting information from a UNIX server and it send the data formated into a .txt file (copy an paste this file into a Excel sheet and everything follow the line and column)

Step1

You need to have Excel open. (you can minimize it)

Be sure to rename the first tab in the Book1.xls --> oWorkbook.Worksheets("Feuil1").Range("A:W").PasteSpecial

Put colors into A1 to W1, save that file and you can close it.

Make a .txt file and enter only 1 character, save it.

Try again this script, Step1 will ask to select a file, select your txt. (exit this script after)

it will convert the .txt file into an Excel file but without modifying the extension. (so you will need to rename the .txt to .xls in next step)

Step2

You can continue with step2 (renaming the .txt to .xls)

Now the file (.xls) is formated with the format of your Book1.xls but with the data from your .txt

Step3

Keep Excel open, open book1.xls and change the color of A1 to W1, save book1.xls, select A1 to W1 and CTRL+C or right click and copy.

Open the step3 script. select your .xls file generated, close the script after it finish and go open your .xls file and you will see the A1 to W1 with new color.


It should work, let me know if you succeed

Thanks

[DLP]

Make a .txt file and enter only 1 character, save it. (do enter and enter 1 char) If not it will paste into A1 and if you put something as header it will overwrite it with your value and so we dont want to affect the A1 to W1, only A2+ to W2+

[nli]

Forget your "steps", they have a lot of unnecessary stuff in them. For example, why SaveAs "SelectedFile"? "SelectedFile" is a .txt file path. Why not just SaveAs an excel file? That would eliminate Step 2 entirely.

Correct me if I am wrong, but it appears that you just want to:

1. Copy an excel file.
2. Put some text in it from a .txt file.
3. SaveAs the same name as the .txt file but in Excel .xls/xlsx format.

Is your .txt file really only one character? Really? Or was that just an example but that actual text file could be larger?

Step1
...
Put colors into A1 to W1, save that file and you can close it.
...
Step3

Keep Excel open, open book1.xls and change the color of A1 to W1

Why do you have to change to colors? (twice) Or are you just describing how excel template is formatted. Does your script need to change the colors, or could it just open the excel file and use the colors that are already in it, then SaveAs a different file name?

It sounds like I or someone else could help you out, but a more concise explanation of what you are trying to accomplish would help a lot.

Do you know that FileSelectFile has the "M" option where you can select multiple files? Or what about GuiDropFiles; a label that executes when you drag-and-drop files on a gui which allows you to process the dropped files?

[DLP]

See in RED below, sorry my native language is not english and it is hard for me to explain it.

Forget your "steps", they have a lot of unnecessary stuff in them. For example, why SaveAs "SelectedFile"? "SelectedFile" is a .txt file path. Why not just SaveAs an excel file? That would eliminate Step 2 entirely.

Correct me if I am wrong, but it appears that you just want to:

1. Copy an excel file. YES
2. Put some text in it from a .txt file. YES
3. SaveAs the same name as the .txt file but in Excel .xls/xlsx format.

YES

I want to use my template (Book1.xls) all the color and format in it but with the data of each .txt file.

Is your .txt file really only one character? Really? Or was that just an example but that actual text file could be larger? NO, it was just an example and file are really larger...

Step1
...
Put colors into A1 to W1, save that file and you can close it.
...
Step3

Keep Excel open, open book1.xls and change the color of A1 to W1

Why do you have to change to colors? (twice) Or are you just describing how excel template is formatted. Does your script need to change the colors, or could it just open the excel file and use the colors that are already in it, then SaveAs a different file name?

It sounds like I or someone else could help you out, but a more concise explanation of what you are trying to accomplish would help a lot.

Do you know that FileSelectFile has the "M" option where you can select multiple files? Or what about GuiDropFiles; a label that executes when you drag-and-drop files on a gui which allows you to process the dropped files?

[DLP]

Each of the .txt files have a different name and it should be saved into samename.xls

[nli]

Can you provide an example of the actual text file (or at least something similar?)

How are the cells delimited? (tabs, commas, etc.)

[DLP]

Can you provide an example of the actual text file (or at least something similar?)

How are the cells delimited? (tabs, commas, etc.)

With tabs

[kon]

Try:
(You can drag-and-drop multiple files at once. You can also select multiple files in "Select Files" by holding Ctrl or Shift.)

Code: Select all

global TemplatePath

; Change this to your template's path
TemplatePath := A_ScriptDir "\Book1.xls"

Gui, Font, s12
Gui, Add, Text, , Drag and drop text files here.
Gui, Add, Button, gSelectFiles, Select Files
Gui, Show, w400 h400
return

SelectFiles:
FileSelectFile, Files, M3,, Select file(s) to batch convert, Documents (*.txt)
if (ErrorLevel)
    return
xlApp := ComObjCreate("Excel.Application")
xlApp.DisplayAlerts := False
;~ xlApp.Visible := True
Files := StrSplit(Files, "`n")
Loop, % Files.Length() - 1
    TxtToXL(Files.1 "\" Files[A_Index + 1], xlApp)
xlApp.Quit
TrayTip, Done, Conversion to .xls complete.
return

GuiDropFiles:
xlApp := ComObjCreate("Excel.Application")
xlApp.DisplayAlerts := False
;~ xlApp.Visible := True
Files := StrSplit(A_GuiEvent, "`n")
for i, TxtPath in Files
    TxtToXL(TxtPath, xlApp)
xlApp.Quit
TrayTip, Done, Conversion to .xls complete.
return

GuiClose:
ExitApp

TxtToXL(TxtPath, xlApp) {
    MyArray := {}, Width := 1
    SplitPath, TxtPath, OutFileName, OutDir, OutExtension, OutNameNoExt, OutDrive
    if (OutExtension != "txt")
        return
    FileRead, Txt, % TxtPath
    Loop, Parse, Txt, `n, `r
    {
        Row := A_Index
        Loop, Parse, A_LoopField, `t
        {
            MyArray[Row, A_Index] := A_LoopField
            if (A_Index > Width)
                Width := A_Index
        }
    }
    MyComArray := ComObjArray(12, MyArray.Length(), Width)
    for Row, oRow in MyArray
        for Col, Val in oRow
            MyComArray[Row-1, Col-1] := Val ;Index values start at 0 in COM arrays, hence the -1
    Book := xlApp.Workbooks.Add(TemplatePath)
    Cell1 := xlApp.Cells(2, 1) ; Address of the top left cell
    Cell2 := Cell1.Offset(MyArray.MaxIndex()-1, Width-1).Address ;Address of the bot. right cell
    xlApp.Range(Cell1, Cell2).value := MyComArray
    NewPath := OutDir "\" OutNameNoExt ".xls"
    if FileExist(NewPath)
        FileDelete, % NewPath
    Book.SaveAs(NewPath, 56) ;  xlExcel8 = 56
    Book.Close()
}

[DLP]

Try:

Code: Select all

global TemplatePath

; Change this to your template's path
TemplatePath := A_ScriptDir "\Book1.xls"

Gui, Font, s12
Gui, Add, Text, , Drag and drop text files here.
Gui, Add, Button, gSelectFiles, Select Files
Gui, Show, w400 h400
return

SelectFiles:
FileSelectFile, Files, M3,, Select file(s) to batch convert, Documents (*.txt)
if (ErrorLevel)
    return
xlApp := ComObjCreate("Excel.Application")
xlApp.DisplayAlerts := False
;~ xlApp.Visible := True
Files := StrSplit(Files, "`n")
Loop, % Files.Length() - 1
    TxtToXL(Files.1 "\" Files[A_Index + 1], xlApp)
xlApp.Quit
TrayTip, Done, Conversion to .xls complete.
return

GuiDropFiles:
xlApp := ComObjCreate("Excel.Application")
xlApp.DisplayAlerts := False
;~ xlApp.Visible := True
Files := StrSplit(A_GuiEvent, "`n")
for i, TxtPath in Files
    TxtToXL(TxtPath, xlApp)
xlApp.Quit
TrayTip, Done, Conversion to .xls complete.
return

GuiClose:
ExitApp

TxtToXL(TxtPath, xlApp) {
    MyArray := {}, Width := 1
    SplitPath, TxtPath, OutFileName, OutDir, OutExtension, OutNameNoExt, OutDrive
    if (OutExtension != "txt")
        return
    FileRead, Txt, % TxtPath
    Loop, Parse, Txt, `n, `r
    {
        Row := A_Index
        Loop, Parse, A_LoopField, `t
        {
            MyArray[Row, A_Index] := A_LoopField
            if (A_Index > Width)
                Width := A_Index
        }
    }
    MyComArray := ComObjArray(12, MyArray.Length(), Width)
    for Row, oRow in MyArray
        for Col, Val in oRow
            MyComArray[Row-1, Col-1] := Val ;Index values start at 0 in COM arrays, hence the -1
    Book := xlApp.Workbooks.Add(TemplatePath)
    Cell1 := xlApp.Cells(2, 1) ; Address of the top left cell
    Cell2 := Cell1.Offset(MyArray.MaxIndex()-1, Width-1).Address ;Address of the bot. right cell
    xlApp.Range(Cell1, Cell2).value := MyComArray
    NewPath := OutDir "\" OutNameNoExt ".xls"
    if FileExist(NewPath)
        FileDelete, % NewPath
    Book.SaveAs(NewPath, 56) ;  xlExcel8 = 56
    Book.Close()
}

---------------------------
ComObject.ahk
---------------------------
Error: 0x800401F3 - Chaîne de classe incorrecte


Line#
017: xlApp.DisplayAlerts := False
019: Files := StrSplit(Files, "
")
020: Loop,Files.Length() - 1
021: TxtToXL(Files.1 "\" Files[A_Index + 1], xlApp)
022: xlApp.Quit
023: TrayTip,Done,Conversion to .xls complete.
024: Return
---> 027: xlApp := ComObjCreate("Excel.Application")
028: xlApp.DisplayAlerts := False
030: Files := StrSplit(A_GuiEvent, "
")
031: For i,TxtPath in Files
032: TxtToXL(TxtPath, xlApp)
033: xlApp.Quit
034: TrayTip,Done,Conversion to .xls complete.
035: Return

Continue running the script?
---------------------------
Oui Non
---------------------------

[sv270190]

Myvar := oxl. Range("a1:b8"). Value
MS box, % myvar does not work