Excel format

[tuffgong]

Good morning. I have a system I am trying to automate that works like this: user fills a column in an Excel spreadsheet with values they would like printed and saves it to a folder on their desktop, they start the script and it formats their data into a text file (adding a prefix) and sends the text file as a .bch file where it needs to go. This is working:

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

Global $sSTCArray
Global $sFilename = @DesktopDir & "\Labels\print.txt"
Global $sWorkbook = @DesktopDir & "\Labels\Labels.xlsx"
Global $oExcel = _Excel_Open(False,False,False,False,True)

barcodePrint()

Func barcodePrint()

$Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default)
FileOpen($sFilename, $FO_OVERWRITE)

;Global $oWorkbook = _Excel_BookAttach($oExcel)

Global $sSTCArray = _Excel_RangeRead($Read)

For $i = 0 to UBound($sSTCArray, 1) - 1
    FileWriteLine($sFilename, "!StaticShelving1x3_ZPL," & $sSTCArray[$i])
Next

_Excel_Close($oExcel)

FileMove($sFilename, "***file path***\print.bch")

EndFunc

However, it only works if the user first formats the spreadsheet to text. I want to automate that. From what I have read it appears AutoIt does not like formatting cells that already have values. True? Can I pull the values from an un-formatted (default GENERAL format) spreadsheet and go straight to my text file? I have also considered opening a second spreadsheet, formatting it, and copying the values over. Like this:

  

expandcollapse popup

Global $sPrefix
Global $oPath
Global $sSTCArray
Global $sFilename = @DesktopDir & "\Labels\print.txt"
Global $sWorkbook = @DesktopDir & "\Labels\Labels.xlsx"
Global $oExcel = _Excel_Open(False,False,False,False,True)
Global $aArray
Global $bExcel
_Excel_BookNew($bExcel)



While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            $aArray = IniReadSection("***File path***\barcode.ini", "stc/rvt/mgm")
            $sPrefix = $aArray[1][1]
            $oPath = $aArray[2][1]
            barcodePrint()

    EndSwitch
WEnd

Func barcodePrint()

    $oWorkbook = _Excel_BookOpen($bExcel, @DesktopDir & "\Labels\print.xlsx")
    $oWorkbook.ActiveSheet.Columns("A").NumberFormat = "@"

    Local $Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default)
    $oCopy = _Excel_RangeRead($sWorkbook)
    _Excel_RangeWrite($oWorkbook, Default, $oCopy)
    FileOpen($sFilename, $FO_OVERWRITE)
    $sSTCArray = _Excel_RangeRead($Read,"Default","Default",3)
    ;_ArrayDisplay($sSTCArray)
    For $i = 0 to UBound($sSTCArray, 1) - 1
        FileWriteLine($sFilename, $sPrefix & $sSTCArray[$i])
    Next

    _Excel_Close($oExcel)


    ;FileMove($sFilename, $oPath)

    Exit

EndFunc

This does not like the formatting of $oWorkbook: "Variable must be of type 'Object'".  Do I need this second sheet? If so, how can I format it? Is there a better way to get the Excel values into a .txt file? Any ideas would be appreciated. Thanks!

[xcaliber13]

tuffgong   I use this to pull all data from an excel spreadsheet to a text file.  

Global $oRange = $oWorkBook.ActiveSheet.Range("A1:A700").EntireRow

_Excel_RangeCopyPaste($oWorkBook.ActiveSheet, $oRange, Default)
$aResults = ClipGet()
$oWorkbook.Close
_Excel_Close($oExcel, Default, True)
Sleep (2000)
Do
    ProcessClose('excel.exe')
Until Not ProcessExists('excel.exe')

_FileCreate("Your file path and name")
FileOpen("Your file path and name", $FO_APPEND)
Sleep(1000)
FileWrite("Your file path and name", $aResults)

Hope this help you

[tuffgong]

Thanks for the reply. I will try this!

[tuffgong]

Why do I get an error when I treat an open workbook as an Object? I am opening it incorrectly? In my original code these lines:

$oWorkbook = _Excel_BookOpen($bExcel, @DesktopDir & "\Labels\print.xlsx")
$oWorkbook.ActiveSheet.Columns("A").NumberFormat = "@"

gives me a "Variable must be of type 'Object'" error. In the example that xcaliber13 was kind enough to share I again get the same error on these lines:

Local $Read = _Excel_BookOpen($oExcel, $sWorkbook, True, False, Default, Default, Default)
    Global $oRange = $sWorkbook.ActiveSheet.Range("A").EntireRow

I am unable to act on the workbook as an Object. Why?

[water]

Use _Excel_Open to start or connect to the Excel application. Then run _Excel_BookAttach to connect to an already opened workbook. 

Edited August 2, 2018 by water

[Juvigy]

I like to pull excel data to an array - it is easy and fast one line of code:

$WorkArray = $oExcel.Application.Activeworkbook.Sheets("name").UsedRange.Value

Then you can easily manipulate it or just check it with the _array functions.

[water]

To start with the questions from your OP:

Q: However, it only works if the user first formats the spreadsheet to text.
A: What do you get when you read unformatted cells?
What do you mean by "format"? Formatting the content (font size, bold etc.) or the cell itself (color, border ...)?

Q: From what I have read it appears AutoIt does not like formatting cells that already have values. True?
A: No. If you define "formatting" we can tell you how to do.

Q: Can I pull the values from an un-formatted (default GENERAL format) spreadsheet and go straight to my text file?
A: Pulling values from Excel always just pulls the value independant of the formatting. Means: If a cell is formatted as date you still get the internal representation of the date independant of how you format the data.

How to solve your problem:

• Tell us what you mean by "formatting"
• Give us an example workbook to play with
• Tell us how the result you expect should look like