Jump to content
Sign in to follow this  
tuffgong

Excel format

Recommended Posts

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:

  

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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By nacerbaaziz
      hello autoit team
      please
      i tried to create a SPVoice object 
      when i create the object and tried to use this function SetAlertBoundary
      it gave me an error
      please where is the problem?
      here is the code
      local $o_Sapi_OBJ = ObjCreate("SAPI.SpVoice") if IsObj($o_Sapi_OBJ) then $o_Sapi_OBJ.SetAlertBoundary("SPEI_PHONEME") endIf note i got the function name from microsoft web site
       
      https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms719784(v=vs.85)
      i have windows10
    • By Cristin
      Dear all,
      first of all hi.
      I`m in trouble, again 😕
      I can`t find right solution, tried all possible combinations generated by my mind and nothing.
      I need to copy range of values depending by values from two different cells in excel sheet.
      I put an example in attachment.

      I have two values which is never changed (is changed only TEXT). I need to copy all the rows from VALUE 1 to VALUE 2 in new Sheets (Output 1, Output 2, ... , Output X).
      It is possible to do this using Autoit? 🤔
      Also in attachment you can find last example which I have tried and it`s something near, it is copying by _Excel_RangeFind row with needed VALUE 1 but I need to copy whole range from VALUE 1 to VALUE 2 😵 ( see in attachment examples).
      Thank you very much, in advance, for your support, solution really will make my life much easier 🙃
      Have a nice day all of you!
      Best Regards,
      Cristin
      Book1.xlsx something.au3
    • By sudeepjd
      I was looking for a UDF using which I could Add and Update Pivot tables and Pivot Charts in Excel easily and could not find one that I could use. So I build this UDF. 
      It has the following functions :
      _ExcelPivot_CreateCache ; Easily Create a pivot table data cache from a Sheet _ExcelPivot_CreateTable ; Create a table from a cache at a specified location on the sheet _ExcelPivot_RefreshTable ; Refresh the datatable data with a new cache _ExcelPivot_AddField ; Add a Field and Aggregate function to the Datatable _ExcelPivot_AddFilter ; Adds in the Filter to a specific field _ExcelPivot_ClearFilter ; Removes the filter for a field or all the filters in the table _ExcelPivot_GetRange ; Get specific areas of a Pivot as a Range Object _ExcelPivot_AddChart ; Add a Pivot Chart linked to a specific Pivot table Attached the UDF to this post.
      Please do let me know if I can improve or add additional functions to it.
      A detailed example on the usage is below. The excel file and the example can be downloaded from the Example.zip file attached.
      #include "ExcelPivot.au3" $oExcel = _Excel_Open() $oBook = _Excel_BookOpen($oExcel, @ScriptDir & "\TestPivot.xlsx") ;Create a Sheet to put the pivot into $pSheet = _Excel_SheetAdd($oBook, -1, False, 1) $pSheet.Name = "Pivot" ;Get the cache for the pivot table $pCache = _ExcelPivot_CreateCache($oBook, "Data") ;Add in the Pivot Table from the Cache _ExcelPivot_CreateTable($pCache, $pSheet, "A1", "FruitsPivot") ;Add in the Fields into the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Category", "Filter") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Product", "Row") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 1) ;Add in a Running total to the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 2, "PercentageRunningTotal", "Product") ;Filter only the fruits _ExcelPivot_Filter($pSheet, "FruitsPivot", "Category", "Fruit") ;Draw a Paretto Chart $chart = _ExcelPivot_AddChart($oBook, $pSheet, "FruitsPivot", "ColumnClustered", "Paretto", "E2", 570) $chart.Chart.FullSeriesCollection(1).ApplyDataLabels $chart.Chart.FullSeriesCollection(2).ChartType = 4 ;Change the percentage to a line graph $chart.Chart.FullSeriesCollection(2).AxisGroup = 2 ;Move it to secondary axis $chart.Chart.Axes(2, 2).MaximumScale = 1 ;Adjust to scale to 100% max  
       
      ExcelPivot.au3 Example.zip
    • By Page2PagePro
      Excel VBA's IDE registers a Control-y as "cut this line of code".
      For those prone to Undo/Redo (Ctrl+Z/Ctrl+Y) you may find frustration when your code in the editor does not redo, but in fact clears your active line of code while killing redo history.
      Though not perfect, I keep this tool running in background on startup.
      The purpose is to allow Cltr+Y to act normally throughout Windows and Office and only interact *differently* with the "Microsoft Visual Basic for Applications" window that is active.
      If the Standard Menu bar exists, it'll try to click the ReDo (Blue Arrow to the right), else "Alt+e, r" keystrokes (less desired).
       
      Here's the code:
      Opt('MustDeclareVars', 1) Opt("WinTitleMatchMode", 1) HotKeySet("^y", "TriggerRedo") While 1 Sleep(10) WEnd Func TriggerRedo() ConsoleWrite("TriggerRedo()" & @CRLF) Local $title = "Microsoft Visual Basic for Applications - " Local $hWnd If WinExists($title) And WinActive($title) Then ;~ Parent Window Handle $hWnd = WinGetHandle($title) Local $aWindowPos = WinGetPos($hWnd) ;~ Control Bar Handle, Position and If Visible Local $sControlID = "[CLASS:MsoCommandBar; TEXT:Standard;]" Local $hStandardBar = ControlGetHandle($hWnd, "", $sControlID) Local $bIsVisible = ControlCommand($hWnd, "", $sControlID, "IsVisible") If $hStandardBar And $bIsVisible Then ConsoleWrite("Using Mouse Click." & @CRLF) ;~ Determine Redo button location on visible Control Bar Local $aBarPos = ControlGetPos($hWnd, "", $sControlID) Local $mX = $aWindowPos[0] + $aBarPos[0] + 217 + Int(23/2) Local $mY = $aWindowPos[1] + $aBarPos[1] + 27 + Int(22/2) MouseClick("Left", $mX, $mY, 1, 0) Else ConsoleWrite("Using VBA Send Keys." & @CRLF) $sControlID = "[CLASS:MsoCommandBar; TEXT:Menu Bar;]" Local $hMenuBar = ControlGetHandle($hWnd, "", $sControlID) ControlSend($hWnd, "", $hMenuBar, "!e") ;~ Send("r") $sControlID = "[CLASS:MsoCommandBarPopup; TEXT:Edit;]" Local $hPopupBar = ControlGetHandle($hWnd, "", $sControlID) ControlSend($hWnd, "", $hPopupBar, "r") EndIf Else ConsoleWrite("Using NATIVE Send Keys." & @CRLF) HotKeySet("^y") Send("^y") ;~ may cause "yyy..." when held HotKeySet("^y", "TriggerRedo") EndIf EndFunc ;==>TriggerRedo Hope this inspires someone.
       
       
    • By siawpo
      Hi,
      I'd like to change different colors for different portion of text in same cell of Excel application.
      Neither character length nor cell might not fixed.
      Here's the code I've tried to put together but not manage to pull it off.
      I'm appreciate it for any suggestion, thank you.
      $oExcel = ObjCreate("Excel.Application") With $oExcel ; open new workbook .Visible = True .WorkBooks.Add .ActiveWorkbook.Sheets(1).Select() EndWith $oExcel.Cells.Font.Color = 0x000000 $oExcel.ActiveFont.Color = -16776961 Send ("'I'd like this sentence to be red'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) $oExcel.ActiveCell.Selection.Font.Color = 0x000000 Send ("'I like this sentence to be black'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) $oExcel.ActiveFont.Color = -16776961 Send ("'I'd like this sentence to be red again'") Sleep(100) Send ("{AltDown}{Enter}{AltUp}") Sleep(100) Send("{ENTER}")  
×
×
  • Create New...