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