Sign in to follow this  
Followers 0
directbuy420

excel array?

6 posts in this topic

OK i have read the excelcom udf and played with many of the examples, written my own basic add and copy script.

I am still completely lost. Can someone please get me started on writing a basic script to read column A from test.xls and output it to a text file

I would appreciate this greatly.

Share this post


Link to post
Share on other sites



Here you are:

Opt("MustDeclareVars", 1)

; Declarations
Dim $oAppXL, $oWorkbook, $oWorksheet
Dim $hOutputFile
Dim $sWorkbookPath, $sOutputFilePath

; Flags declarations - Excel
Global Const $xlCellTypeLastCell = 11

; Initialize variables
$sWorkbookPath = "C:\Test.xls"
$sOutputFilePath = "C:\Test.txt"

; Create Excel object
$oAppXL = ObjCreate("Excel.Application")
        
; Check if object creation succeeded
If Not IsObj($oAppXL) Then
    MsgBox(16, "ActiveX Error", "Excel object creation failed." & @CRLF & _
                "Please verify the installation of Microsoft Excel.")
    Exit
EndIf

; Open workbook and text file
$oAppXL.Workbooks.Open($sWorkbookPath)
$hOutputFile = FileOpen($sOutputFilePath, 2)

; Find the opened workbook in all the workbooks that are open
For $i = 1 to $oAppXL.Workbooks.Count
    If $oAppXL.Workbooks($i).FullName = $sWorkbookPath Then
        $oWorkbook = $oAppXL.Workbooks($i)
    EndIf
Next

; Associate first sheet in workbook with $oWorksheet
$oWorksheet = $oWorkbook.Worksheets(1)

; Read all rows in column A and write contents to output text file
For $i = 1 to $oWorksheet.Cells.SpecialCells($xlCellTypeLastCell).Row
    FileWriteLine($hOutputFile, $oWorksheet.Cells($i, 1).Value)
Next

; Close files
FileClose($hOutputFile)
$oAppXL.Quit

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Hi,

Clumsy, perhaps; [i prefer t@tonedeaf's if you're OK with obj commands; ]

#include<ExcelCom.au3>

$XLFilePath=@ScriptDir&"\book1.xls"

$sFile=@ScriptDir&"\results.txt"

$XLArray=_XLColumnToArray( $XLFilePath,1, "A1:A10000")

$s_Line=_ArrayToString($XLArray,"|")

$h_file=FileOpen($sFile,2)

FileWriteLine($h_file,$s_Line)

FileClose($h_file)

RunWait("notepad.exe "&$sFile, @WorkingDir, @SW_SHOW)

Best, Randall Edited by randallc

Share this post


Link to post
Share on other sites

guys that was great, i was able to modify the scrip to do what i needed to do, i really appreciate it, and randall you were right the 1st script worked perfectly.

I do have one more question, i would like to be able to have some type of open file box or select file box to choose which file the script will parse, is this possible?

Share this post


Link to post
Share on other sites

FileOpenDialog()?


[center]Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.[/center]

Share this post


Link to post
Share on other sites

FileOpenDialog()?

yah lol im an idiot, i got it to work. thanks i appreciate it

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