Jump to content
Sign in to follow this  
NickT

Array to Excel

Recommended Posts

NickT

I believe it's time to consult the experts on what I am trying to do...I am trying to take a large text file and write it to an Excel spreadsheet. After 2 days of trial and error I finally got it to work. What I would like help with is on making it faster. In my opinion it is taking WAY too long to write these values to the worksheet.

SCRIPT:

test.au3

TEXT FILE:

aptsource.txt

Share this post


Link to post
Share on other sites
water

Didn't have a look at your script in detail. You could try my UDF (Attention! Alpha version!) and use the _Excel_RangeWrite function to write an array to a worksheet. Or use the _Excel_BookOpenText function to directly read the file into a sheet.

That's much faster!


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

Okay, I tried using the _Excel_BookOpenText function with no luck. I keep getting a failed object error on it.

Share this post


Link to post
Share on other sites
water

Can you post your code?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

#include <Excel.au3>

#include <File.au3>

#include <String.au3>

#include <Array.au3>

Global $ext,$slnum = "-1"

Func write($book,$val,$row,$x,$y,$z)

$zVAL = _ExcelReadCell($book,$row - 1,3)

If _ExcelReadCell($book,$row,3) = $zVAL Then

_ExcelWriteCell($book,$slnum,$row,10)

Else

$slnum += 1

_ExcelWriteCell($book,$slnum,$row,10)

EndIf

_ExcelWriteCell($book,$x,$row,7)

_ExcelWriteCell($book,$y,$row,8)

_ExcelWriteCell($book,$z,$row,9)

If Number(Round(($row - 2) / $val * 100)) > "0" Then

$per = Round(($row - 2) / $val * 100)

ProgressSet($per,$per & " Percent complete")

EndIf

EndFunc

Func parse($file,$tmpfile,$book,$lcount)

ProgressOn("AptSource Conversion","","",1,1)

Local $line

If Not StringInStr(FileRead($file),"GOTO",1) Then

MsgBox(0,"ERROR",'This is not an "aptsource" file.')

ex($book,$tmpfile)

EndIf

_ExcelWriteCell($book,"in machine datum : xm=-zb ; ym=-xb ; zm=yb with rotation (0.223deg)",1,1)

_ExcelWriteCell($book,"Xc",2,1)

_ExcelWriteCell($book,"Yc",2,2)

_ExcelWriteCell($book,"Zc",2,3)

_ExcelWriteCell($book,"i",2,4)

_ExcelWriteCell($book,"j",2,5)

_ExcelWriteCell($book,"k",2,6)

_ExcelWriteCell($book,"x+i",2,7)

_ExcelWriteCell($book,"y+j",2,8)

_ExcelWriteCell($book,"z+k",2,9)

_ExcelWriteCell($book,"scan line " & @CRLF & "number",2,10)

_ExcelHorizontalAlignSet($book,1,1,1,1,"center")

$book.Range($book.Cells(1,1),$book.Cells(1,10)).MergeCells=True

$book.Columns.NumberFormat="@"

$read = StringReplace(FileRead($file),"GOTO","",0,1);_StringStripChr(FileRead($file),"GOTO/ ",8)

$read = StringReplace($read," ","",0,1)

$read = FileWrite($tmpfile,StringReplace($read,"/","",0,1))

For $i = 1 To $lcount

$read = FileReadLine($tmpfile,$i)

$read = _StringExplode($read,",")

$x = StringReplace($read[0] + $read[3],".",",")

$y = StringReplace($read[1] + $read[4],".",",")

$z = StringReplace($read[2] + $read[5],".",",")

$read = StringReplace(_ArrayToString($read),".",",")

$read = _StringExplode($read,"|")

_ExcelWriteArray($book, $i + 2, 1, $read)

write($book,$lcount,$i + 2,$x,$y,$z)

Next

$book.columns.autofit

$folder = FileSaveDialog("Save",@ScriptDir,"Excel (*.xlsx)",18)

$book.ActiveWorkbook.SaveAs($folder);,"","",False,False,1,2,False)

MsgBox(0,"",@error)

ex($book,$tmpfile)

EndFunc

Func lcount($file)

If $file = "" Then Exit

Local $line

_FileReadToArray($file,$line)

parse($file,_TempFile(),_ExcelBookNew(1),$line[0])

EndFunc

Func ex($book,$tmpfile)

ProgressOff()

_ExcelBookClose($book,0)

FileDelete($tmpfile)

EndFunc

lcount(FileOpenDialog("Open","","All(*.*)",3))

Edited by NickT

Share this post


Link to post
Share on other sites
water

Sorry, my bad.

I didn't mean the original code which you already posted in #1 but the code where _Excel_BookOpenText didn't work.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

Apologies for taking so long, been busy. Here is my code with _Excel_BookOpenText not working.

#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <Array.au3>

Global $ext,$slnum = "-1"

Func write($book,$val,$row,$x,$y,$z,$time)
Local $per,$min[1]
_ExcelWriteCell($book,$x,$row,7)
_ExcelWriteCell($book,$y,$row,8)
_ExcelWriteCell($book,$z,$row,9)
$zVAL = _ExcelReadCell($book,$row - 1,3)
If _ExcelReadCell($book,$row,3) = $zVAL Then
_ExcelWriteCell($book,$slnum,$row,10)
Else
$slnum += 1
_ExcelWriteCell($book,$slnum,$row,10)
EndIf
If Number(Round(($row - 2) / $val * 100)) > "0" Then
$per = Round(($row - 2) / $val * 100)
; ProgressSet($per,$per & " Percent complete" & @CRLF & @CRLF & Round(TimerDiff($time) / 1000))
EndIf
$dif = Round(TimerDiff($time) / 1000)
If $dif >= "60" Then
$min[0] = $dif / 60
If StringInStr($min[0],".") Then $min[0] = _StringBetween($min,"",".")
Else
$min[0] = 0
EndIf
$sec = $dif / 60
$sec = _StringBetween($sec,".","")
$sec = _StringInsert(_ArrayToString($sec),".",0)
$sec = Round($sec * 60)
ProgressSet($per,$per & " Percent complete" & @CRLF & @CRLF & "Time elapsed: " & $min[0] & " minutes and " & $sec & " seconds")
EndFunc

Func parse($file,$tmpfile,$book,$lcount)
$time = TimerInit()
ProgressOn("AptSource Conversion","","",1,1,18)
Local $line
If Not StringInStr(FileRead($file),"GOTO",1) Then
MsgBox(0,"ERROR",'This is not an "aptsource" file.')
ex($book,$tmpfile)
EndIf
_ExcelWriteCell($book,"in machine datum : xm=-zb ; ym=-xb ; zm=yb with rotation (0.223deg)",1,1)
_ExcelWriteCell($book,"Xc",2,1)
_ExcelWriteCell($book,"Yc",2,2)
_ExcelWriteCell($book,"Zc",2,3)
_ExcelWriteCell($book,"i",2,4)
_ExcelWriteCell($book,"j",2,5)
_ExcelWriteCell($book,"k",2,6)
_ExcelWriteCell($book,"x+i",2,7)
_ExcelWriteCell($book,"y+j",2,8)
_ExcelWriteCell($book,"z+k",2,9)
_ExcelWriteCell($book,"scan line " & @CRLF & "number",2,10)
_ExcelHorizontalAlignSet($book,1,1,1,1,"center")
$book.Range($book.Cells(1,1),$book.Cells(1,10)).MergeCells=True
$book.Columns.NumberFormat="@"
$read = StringReplace(FileRead($file),"GOTO","",0,1)
$read = StringReplace($read," ","",0,1)
$read = FileWrite($tmpfile,StringReplace($read,"/","",0,1))
_Excel_BookOpenText($book,$tmpfile,3,1,Default,Default,",",2,Default,Default,Default)
; For $i = 1 To $lcount
; $read = FileReadLine($tmpfile,$i)
; If @error Then
; MsgBox(0,"",$read & @CRLF & $i & @CRLF & $lcount)
; ExitLoop
; EndIf
; $read = _StringExplode($read,",")
; $x = StringReplace($read[0] + $read[3],".",",")
; $y = StringReplace($read[1] + $read[4],".",",")
; $z = StringReplace($read[2] + $read[5],".",",")
; $read = StringReplace(_ArrayToString($read),".",",")
; $read = _StringExplode($read,"|")
; _ExcelWriteArray($book, $i + 2, 1, $read)
; write($book,$lcount,$i + 2,$x,$y,$z,$time)
; Next
$book.columns.autofit
$folder = FileSaveDialog("Save",@ScriptDir,"Excel (*.xlsx)",18)
$book.ActiveWorkbook.SaveAs($folder)
ex($book,$tmpfile)
EndFunc

Func lcount($file)
If $file = "" Then Exit
Local $line
_FileReadToArray($file,$line)
parse($file,_TempFile(),_ExcelBookNew(0),$line[0])
EndFunc

Func ex($book,$tmpfile)
ProgressOff()
_ExcelBookClose($book,0)
FileDelete($tmpfile)
EndFunc

lcount(FileOpenDialog("Open","","All(*.*)",3))
Apologies for taking so long, been busy. Here is my code with _Excel_BookOpenText not working.

Share this post


Link to post
Share on other sites
water

You can't combine the Excel UDF that comes with AutoIt and my rewrite of the Excel UDF.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

I've tried it both ways, using your rewrite and $book.OpenText method.

-- Note that I have not only tried $book.OpenText but also $book.ActiveWorkbook.OpenText and $book.Workbooks.OpenText.

Share this post


Link to post
Share on other sites
water

If you run the _Excel_BookOpenText.au3 example of my rewritten Excel UDF. Does it work?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

Sweet! That works like I wanted it too! Is there any way to attach the "OpenText" book that is created to the previously created workbook instead of having two seperate books? I've looked around _ExcelBookAttach but it doesn't seem to be what I'm looking for. Of course I'm probably wrong.

Share this post


Link to post
Share on other sites
water

Not at the moment. The Excel method OpenText is only available for workbooks.

What should be possible is to copy the worksheet to another book.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

I suppose what I could do is close the unused book and set my variable to the new book. Thank you for all of your help. It is greatly appreciated!

Share this post


Link to post
Share on other sites
water

I will test how to copy a worksheet from one workbook to another tomorrow.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

How to copy a worksheet from one workbook to another:

#include <Excel Rewrite.au3>
$oExcel = _Excel_Open()
$oBook1 = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls")
$oSheet1 = $oBook1.Sheets(1)
$oBook2 = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel2.xls")
MsgBox(0, "", "Copying sheet1 of workbook1 before sheet1 of workbook2")
$oSheet1.Copy($oBook2.Sheets(1))
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

I finally got the script to work. Thanks for your help. I have another question that pertains to the same script but this is more of a "want to know how" sort of deal. I'm wondering what makes this work:

dim $Array1[2]=[0,2]

dim $Array2[2]=[7,2]

dim $Array3[2]=[41,2]

dim $Array4[2]=[47,2]

dim $Array5[2]=[61,1]

dim $Array6[2]=[98,1]

dim $Array7[2]=[99,1]

dim $ArrayComplet[7]=[$Array1,$Array2,$Array3,$Array4,$Array5,$Array6,$Array7]

When I run _ArrayDisplay($ArrayComplet) I get 7 rows, 1 column, and no values. I use this in my script but I just want to know how this is working. This came from

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=..\..\..\..\Program Files (x86)\AutoIt3\Icons\au3.ico
#AutoIt3Wrapper_Outfile=AptSource_Converter.exe
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include
#include
#include

Func parse($file,$tmpfile,$book,$lcount)
Dim $arr0[2] = [0,2]
Dim $arr1[2] = [1,2]
Dim $arr2[2] = [2,2]
Dim $arr3[2] = [3,2]
Dim $arr4[2] = [4,2]
Dim $arr5[2] = [5,2]
Dim $arr6[2] = [6,2]
Dim $arr7[2] = [7,2]
Dim $arr8[2] = [8,2]
Dim $arr9[2] = [9,2]
Dim $array[10] = [$arr0,$arr1,$arr2,$arr3,$arr4,$arr5,$arr6,$arr7,$arr8,$arr9]
$tmpfile2 = _TempFile()
If Not StringInStr(FileRead($file),"GOTO",1) Then
MsgBox(0,"ERROR",'This is not an "aptsource" file.')
ex($book,$tmpfile,$tmpfile2,"")
EndIf
ProgressOn("Converting","","",1,1,18)
$read = StringReplace(FileRead($file),"GOTO","",0,1)
$read = StringReplace($read," ","",0,1)
$read = FileWrite($tmpfile,StringReplace($read,"/","",0,1))
$book.Application.DisplayAlerts=0
$book.Workbooks.OpenText($tmpfile,Default,1,Default,1,False,False,False,True,False,False,"",$array, Default,Default,Default,True)
_ExcelWriteCell($book,0,1,10)
$book.Range($book.Cells(1,7),$book.Cells($lcount,9)).Formula="=A1:A" & $lcount & "+D1:D" & $lcount
$book.Range($book.Cells(2,10),$book.Cells($lcount,10)).Formula="=IF(C2=C1,J1,J1+1)"
MsgBox(0,"","")
$book.ActiveWorkbook.SaveAs($tmpfile,20,Default,Default,False,False,1,2)
_ExcelBookClose($book,0)
ProgressSet(25,"25 percent complete" & @CRLF & @CRLF & $file)
$read = StringReplace(FileRead($tmpfile),".",",")
FileWrite($tmpfile2,$read)
ProgressSet(50,"50 percent complete" & @CRLF & @CRLF & $file)
$book = _ExcelBookNew(0)
$book.Application.DisplayAlerts=0
$book.Application.ErrorCheckingOptions.NumberAsText=False
$book.Workbooks.OpenText($tmpfile2,Default,1,Default,1,False,True,False,False,False,False,"",$array, Default,Default,Default,True)
$book.ActiveSheet.Rows(1).Insert()
$book.ActiveSheet.Rows(1).Insert()
ProgressSet(75,"75 percent complete" & @CRLF & @CRLF & $file)
_ExcelWriteCell($book,"in machine datum : xm=-zb ; ym=-xb ; zm=yb with rotation (0.223deg)",1,1)
_ExcelWriteCell($book,"Xc",2,1)
_ExcelWriteCell($book,"Yc",2,2)
_ExcelWriteCell($book,"Zc",2,3)
_ExcelWriteCell($book,"i",2,4)
_ExcelWriteCell($book,"j",2,5)
_ExcelWriteCell($book,"k",2,6)
_ExcelWriteCell($book,"x+i",2,7)
_ExcelWriteCell($book,"y+j",2,8)
_ExcelWriteCell($book,"z+k",2,9)
_ExcelWriteCell($book,"scan line " & @CRLF & "number",2,10)
_ExcelHorizontalAlignSet($book,1,1,1,1,"center")
_ExcelHorizontalAlignSet($book,3,1,$lcount + 2,10,"right")
$book.Range($book.Cells(1,1),$book.Cells(1,10)).MergeCells=True
$book.Columns.AutoFit
ProgressSet(100,"100 percent complete" & @CRLF & @CRLF & $file)
$folder = FileSaveDialog("Save",@ScriptDir,"Excel (*.xlsx)",18)
ProgressOff()
If Not $folder Then ex($book,$tmpfile,$tmpfile2,"")
$book.ActiveWorkbook.SaveAs($folder,51,Default,Default,False,False,1,2)
ex($book,$tmpfile,$tmpfile2,$folder)
EndFunc

Func lcount($file)
If $file = "" Then Exit
Local $line
_FileReadToArray($file,$line)
For $i = UBound($line) - 1 To 0 Step -1
If $line[$i] = "" Then _ArrayDelete($line,$i)
Next
$line = UBound($line) - 1
parse($file,_TempFile(),_ExcelBookNew(1),$line)
EndFunc

Func ex($book,$tmpfile,$tmpfile2,$folder)
_ExcelBookClose($book,0)
Do
FileDelete($tmpfile)
FileDelete($tmpfile2)
Until Not FileExists($tmpfile) And Not FileExists($tmpfile2)
If Not $folder = "" Then MsgBox(0,"Conversion",'Apt Source conversion complete!!' & @CRLF & @CRLF & 'New file is located at: ' & $folder & ".xlsx")
Exit
EndFunc

lcount(FileOpenDialog("Open","","All(*.*)",3))
Edited by NickT

Share this post


Link to post
Share on other sites
water

Array in Array is not recommended. See the end of this page.

Function _ArrayDisplay doesn't know how to handle arrays in arrays.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

Okay, that's understandable. Is there a way to do this without an array in an array?

Share this post


Link to post
Share on other sites
water

Use a two-dimensional array. Something like this:

#include <Array.au3>
Global $Array[7][2]=[[0,2],[7,2],[41,2],[47,2],[61,1],[98,1],[99,1]]
_ArrayDisplay($Array)

BTW: Don't use "DIM", use "Global"


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
NickT

I tried that in my script but it doesn't work. I've also tried a 3-dimensional array as well.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Similar Content

    • robertocm
      By robertocm
      I'm using this for replacing text strings in the VBProject of all excel files in a folder and subfolders.
      I have the same text string in several lines and those lines could have some differences between files: then not feasible for .ReplaceLine method
      I'm not interested in placing all the code in a xla AddIn, because the code is similar but not exactly the same in all the files.
       
      Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase #include <File.au3> #include <WinAPIFiles.au3> #include <Excel.au3> $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling. Global Const $sMessage = "Select Folder" Global $sFileSelectFolder = FileSelectFolder($sMessage, "") If @error Then MsgBox(0, "", "No folder was selected.") Exit EndIf Global $bFileOpen ;Look for excel files in selected directory and all subdirectories Global $aFileList = _FileListToArrayRec($sFileSelectFolder, "*.xlsm", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) If Not @error Then Local $oAppl = _Excel_Open(Default, Default, False, Default, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended) For $i = 1 To $aFileList[0] $bFileOpen = _WinAPI_FileInUse($aFileList[$i]) If $bFileOpen = 0 Then ;ShellExecute($aFileList[$i]) Local $oWorkbook = _Excel_BookOpen($oAppl, $aFileList[$i]) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oProject = $oWorkbook.VBProject ;From: Adapt VBA in a workbook using VBA / http://www.snb-vba.eu/index_en.html ;2.7.2 Macromodule delete With $oProject .VBComponents.Remove(.VBComponents("SplashText")) If $iEventError Then Consolewrite("SplashText Form not found: " & $aFileList[$i] & @CRLF) $iEventError = 0 ; Reset after displaying a COM Error occurred EndIf .VBComponents.Import("C:\Documents and Settings\XP\Escritorio\PLANTILLAS_EXPORT\SplashText.frm") EndWith ;3.2.1.8 Macro: delete With $oProject.VBComponents("Actual").CodeModule ;3.2.1.2 Macro: find If .Find("Sub Check_NumPed(", 1, 1, -1, -1) Then ;Note: using '+ 1' at the end of the line because i'm used to add an empty line between procedures (see vba help for ProcCountLines) .DeleteLines( .ProcStartLine("Check_NumPed", 0), .ProcCountLines("Check_NumPed", 0) + 1) EndIf EndWith ;Check if range name exists. If not create named ranges If Not IsObj($oWorkbook.Sheets("DATOS").Evaluate("Booking_DestPort")) Then ;If Not IsObj($oWorkbook.Sheets("DATOS").Range("Booking_DestPort")) Then If $oWorkbook.Sheets("DATOS").Range("AC7").value = "DestPort" Then $oWorkbook.Names.Add("Booking_DestPort", "=DATOS!$AC$8") Else ConsoleWrite("-> Not: 'DestPort' in AC7" & @TAB & $aFileList[$i] & @CRLF) EndIf If $oWorkbook.Sheets("DATOS").Range("AD7").value = "FinalDest" Then $oWorkbook.Names.Add("Booking_FinalDest", "=DATOS!$AD$8") Else ConsoleWrite("-> Not: 'FinalDest' in AD7" & @TAB & $aFileList[$i] & @CRLF) EndIf EndIf ;Open VBE Editor (like Alt+F11) $oAppl.VBE.MainWindow.Visible = True ;$oAppl.VBE.Windows("Inmediato").Visible = True ;https://www.autoitscript.com/forum/topic/77545-resolved-vbaofficeexcel-experts/ ;Spiff59, Aug 2008 ;Local $oModules = $oProject.VBComponents ;Local $oModules = $oWorkbook.VBProject.VBComponents ;$oModules.Item(1).CodeModule.CodePane.Show ;$oModules.Item(1).Activate ; With $oModules.Item($y).CodeModule ; .ReplaceLine (1 , "Sub SpellCheck()") ; .DeleteLines (10, 1) ; .InsertLines (7 , "TEST") ; EndWith ;Wait 30 seconds for the window to appear. Local $hWnd = WinWait("Microsoft Visual Basic - ", "Proyecto - VBAProjec", 30) WinActivate($hWnd) WinWaitActive($hWnd, "", 30) If WinActive($hWnd, "") Then ;Sleep(100) ;Send("{F7}") $oProject.VBComponents("Actual").Activate ;Wait 30 seconds for the window to appear. Local $hWnd2 = WinWait(" - [Actual (Código)]", "Proyecto - VBAProject", 30) WinActivate($hWnd2) WinWaitActive($hWnd2, "", 30) ;First Replace If WinActive($hWnd2, "") Then Send("{CTRLDOWN}h{CTRLUP}") ;Wait 30 seconds for the window to appear. Local $hWnd3 = WinWait("Reemplazar", "&Procedimiento actua", 30) WinActivate($hWnd3) WinWaitActive($hWnd3, "", 30) Sleep(100) Send('Sheets("DATOS").Range("AC8")') Sleep(200) Send("{TAB}") Sleep(100) ;Send("{DEL}") Send('Range("Booking_DestPort")') Sleep(200) ControlClick("Reemplazar", "", "[ID:4892]") Sleep(100) Send("{ALTDOWN}z{ALTUP}") Local $hWnd4 = WinWait("Microsoft Visual Basic", "Se ha buscado en la ", 2) ;WinActivate($hWnd4) ;WinWaitActive($hWnd4, "", 3) If WinActive($hWnd4, "") Then Sleep(100) Send("{SPACE}") Else Consolewrite("Not found 1: " & $aFileList[$i] & @CRLF) Local $hWnd5 = WinWait("Microsoft Visual Basic", "No se encontró", 0) ;WinActivate($hWnd5) ;WinWaitActive($hWnd5, "", 2) Sleep(100) Send("{SPACE}") EndIf Sleep(100) If WinActive($hWnd3, "") Then ;Alt+F4 Send("!{F4}") Sleep(100) EndIf EndIf ;Second Replace If WinActive($hWnd2, "") Then Send("{CTRLDOWN}h{CTRLUP}") ;Wait 30 seconds for the window to appear. Local $hWnd3 = WinWait("Reemplazar", "&Procedimiento actua", 30) WinActivate($hWnd3) WinWaitActive($hWnd3, "", 30) Sleep(100) Send('Sheets("DATOS").Range("AD8")') Sleep(200) Send("{TAB}") Sleep(100) Send('Range("Booking_FinalDest")') Sleep(200) ControlClick("Reemplazar", "", "[ID:4892]") Sleep(100) Send("{ALTDOWN}z{ALTUP}") Local $hWnd4 = WinWait("Microsoft Visual Basic", "Se ha buscado en la ", 2) ;WinActivate($hWnd4) ;WinWaitActive($hWnd4, "", 3) If WinActive($hWnd4, "") Then Sleep(100) Send("{SPACE}") Else Consolewrite("Not found 2: " & $aFileList[$i] & @CRLF) Local $hWnd5 = WinWait("Microsoft Visual Basic", "No se encontró", 0) ;WinActivate($hWnd5) ;WinWaitActive($hWnd5, "", 2) Sleep(100) Send("{SPACE}") EndIf Sleep(100) If WinActive($hWnd3, "") Then ;Alt+F4 Send("!{F4}") Sleep(100) EndIf EndIf ;Close VBE Editor If WinActive($hWnd2, "") Then $oAppl.VBE.ActiveWindow.Close ;Send("^{F4}") ;Sleep(100) ;Send("^s") $oAppl.VBE.MainWindow.Visible = False ;Sleep(100) ;Send("!{F4}") EndIf EndIf _Excel_BookClose($oWorkbook, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf Next Else MsgBox(16, "Error", "No files were found in the folder specified.") EndIf _Excel_Close($oAppl) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;This is a custom error handler Func ErrFunc() $HexNumber = Hex($oMyError.number, 8) ;~ MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _ ;~ "Number is: " & $HexNumber & @CRLF & _ ;~ "WinDescription is: " & $oMyError.windescription) ConsoleWrite("-> We intercepted a COM Error !" & @CRLF & _ "-> err.number is: " & @TAB & $HexNumber & @CRLF & _ "-> err.source: " & @TAB & $oMyError.source & @CRLF & _ "-> err.windescription: " & @TAB & $oMyError.windescription & _ "-> err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF) $iEventError = 1 ; Use to check when a COM Error occurs EndFunc ;==>ErrFunc  
    • lattey
      By lattey
      hi,
      i have checkboxes and each checkbox that checked, i put in array. 
      now, im stuck on how to loop the checked array and store in in one variable. what i can do now, is only write the result into a text file. 
      below is the code:
      #include <GUIConstantsEx.au3> ;~ #include <MsgBoxConstants.au3> #include <ButtonConstants.au3> #include <Array.au3> Global $Count = 3 Global $CheckBoxP[$Count] Global $step[$Count] global $array1[1] Global $ExitResult $hGUI = GUICreate("Summary Steps", 500, 400) GUISetFont(12, 400, "Tahoma") GUICtrlCreateLabel( "Please Select the Summary Steps for Script Check", 70, 20) GUISetFont(10, 400, "Tahoma") Global $array_Pstep[3] = ["fix2","fix1","fix3"] global $step[3] = ["2","3","4"] $Spacing = 50 For $i = 0 To UBound($array_Pstep) - 1 $CheckBoxP[$i] = GUICtrlCreateCheckbox($array_Pstep[$i], 80, $Spacing + (20 * $i), 65, 17) Next $submit = GUICtrlCreateButton("Submit",180, 280, 80, 30) $exit = GUICtrlCreateButton("Exit",180, 320, 80, 30) GUISetState() While 1 $Msg = GUIGetMsg() Select case $Msg=$submit For $i = 0 To $Count - 1 If GUICtrlRead($CheckBoxP[$i]) = $GUI_CHECKED Then _ArrayAdd($array1, $step[$i]) EndIf Next Global $logfilerray = @WorkingDir & "\checkedlist.txt" FileDelete ($logfilerray) Global $readlogfile = FileOpen($logfilerray,1) for $a = 1 to UBound($array1) - 1 ;~ $var=$array1[$a] FileWriteLine($readlogfile,$array1[$a]) Next FileClose($readlogfile) Exit case $Msg=$exit $ExitResult = MsgBox(1,"Summary Step", "Continue to Exit ?") if $ExitResult = 1 Then ;ok Exit EndIf Exit EndSelect WEnd  
    • gahhon
      By gahhon
      Hi Guys,
      I was trying to read some data from the excel file and without opening the file. But I tried a lot of methods, it still open the file.
      And also, I am able to capture the ColumnA value but not Column B.
      Thanks for advance information.
      Global $oDataA, $oDataB Call ("ExcelRead", "B2", "C2") Func ExcelRead($oColumnA, $oColumnB) Local $oPath = @ScriptDir & "\MyFile.xlsx" Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, $oPath, 1, 0) $oDataA = _Excel_RangeRead($oWorkbook, "Sheet 1", $oColumnA) $oDataB = _Excel_RangeRead($oWorkbook, "Sheet 1", $oColumnB) MsgBox(0, "Test Value", $oDataA & ", " & $oDataB) EndFunc  
    • omicron
      By omicron
      How do you perform a nested loop function with a multidimensional array from 2 lists.
      for i in list1
      (open file) extract variable
          while open for i in list 2
          (open file2) extract variable
       
      var1 + var2 = (search term)

      The list sizes will more than likely consist of different lengths.
       
      What is the best approach to accomplishing this method?
             
    • omicron
      By omicron
      Hello!

      I am working on a function that I am just getting lost on. The goal is a multiple nested loop.

      Here are the steps:
      Contents of file1.txt::
      [topic] var1=Name var2=OtherName var3=SomeotheName Contents of file2.txt::
      [subTopic] top=sub1 top2=sub2 top3=sub3 The Shell I am working from::
      #include <file.au3> $file = "c:\yourfile.txt" FileOpen($file, 0) For $i = 1 to _FileCountLines($file) $line = FileReadLine($file, $i) msgbox(0,'','the line ' & $i & ' is ' & $line) Next FileClose($file) Understanding however that the "msgbox" needs to then become a variable. in example the following::
      $file = "c:\yourfile.txt" FileOpen($file, 0) While true( prog.exe is running && "WinName" is open) do For $i = 1 to _FileCountLines($file) $line = FileReadLine($file, $i) ;Open File to log "current location of file 1" FileWriteLine ("filename", $i & ' is ' & $line) var = $line Next $file2 = "c:\yourfile.txt" FileOpen($file, 0) For $i = 1 to _FileCountLines($file) $line = FileReadLine($file, $i) ; OpenFile to log "Current location of file 2" FileWriteLine ("filename", $i & ' is ' & $line) Next FileClose($file2) FileClose($file) The goal in written form is the following ::

      While in "OpenWindow"
          read from file 1 starting at line 1 until end of file.
         file 1 is a list of names to be searched.
         With $line selected, add this element to the element in file 2.
       
      The search of a variables in list 1 and list 2 differ on the amount of posts that day. (This is not a web based platform, it is a game) I need to search 2 names and take a screenshot of the out put. The sizes of the names list depend on the activity of names at the time of search.
      This loop continues until all the names from both lists have been searched. Mostly in the format of::
      File1= item
      File2= Vendor
       
      Item + Vendor  ( Capture screen, scroll) -- Not sure how to detect if I need to scroll)
       
      Thank you for your help and support!
×