Jump to content

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!

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

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?

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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.

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By genius257
      I've made a library, based on AutoItObject UDF with the goal of implementing getter and setter functionality and make it possible to define new object properties in as few steps as possible.
      Thank you to @trancexx for getting me on the right track, and all users in Hooking into the IDispatch interface for the code to get me going.
      If I've forgotten to add credit, please let me know
      Example:
      #include "AutoItObject_Internal.au3" $myCar = IDispatch() $myCar.make = 'Ford' $myCar.model = 'Mustang' $myCar.year = 1969 $myCar.__defineGetter('DisplayCar', DisplayCar) Func DisplayCar($oThis) Return 'A Beautiful ' & $oThis.parent.year & ' ' & $oThis.parent.make & ' ' & $oThis.parent.model EndFunc MsgBox(0, "", $myCar.DisplayCar) More examples: https://github.com/genius257/AutoItObject-Internal/tree/master/Examples
      Version: 4.0.1
      AutoItObject_Internal.au3
      Documentation
      Edit2 (19th March 2017):
      First of all, sorry about the lack of updates on this project. I always start too many projects and end up ignoring old projects, if I run into problems ^^'.
      So I've started moving my AutoIt scripts to GitHub. I will still post the most recent script version here.
    • By sylremo
      I tried to implement the code in this topic:
      Firstly, i have no idea how these lines of code work but meanwhile i noticed that:
      ; Everytime autoit wants to call a method, get or set a property in a object it needs to go to ; IDispatch::GetIDsFromNames. This is our version of that function, note that by defining this ourselves ; we can fool autoit to believe that the object supports a lot of different properties/methods. Func __IDispatch_GetIDsFromNames($pSelf, $riid, $rgszNames, $cNames, $lcid, $rgDispId) ... EndFunc The problem is i ran into is that some object calls didn't go through IDispatch::GetIDsFromNames.
      Here is the code to replicate what i'm mentioning:
      I followed the example in the topic and tried to do the same thing with method .Documents (line 193) and .Open (line 194) but didn't get the same result because .Documents was being passed through __IDispatch_GetIDsFromNames while .Open didn't.
      $Au3_CallByName = 'Documents' Local $oDoc = $oAppl.Au3_CallByName $Au3_CallByName = 'Open' $oDoc = $oDoc.Au3_CallByName($sFilePath, $bConfirmConversions, $bReadOnly, $bAddToRecentFiles, $sOpenPassword, "", $bRevert, $sWritePassword, "", $iFormat) Console outputs:
      ==> The requested action with this object has failed.: $oDoc = $oDoc.Au3_CallByName($sFilePath, $bConfirmConversions, $bReadOnly, $bAddToRecentFiles, $sOpenPassword, "", $bRevert, $sWritePassword, "", $iFormat) $oDoc = $oDoc^ ERROR Is there any workarounds to solve this?

      Thank you!
    • By goku200
      I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel.
      Example of filename:
      12345_v1.0_TEST Name [12345]_01.01.2022.html
      12345 would be in one column
      v1.0 would be in another column
      TEST Name [12345] would be in another column
      01.01.2022 would be in another column
      .html would be in another column
      Note: filenames always change each day.
      Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter
       
    • By SkysLastChance
      I am having a issue of whenever I try to _Excel_RangeWrite a formula that references another workbook I am getting an error @4 and @extended -2147352567

      #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oWorkBook,Default,"=IF(D2=D1,"",VLOOKUP(D2,'J:\Temporary Files\FolderName\FileName.xlsx'!$A:$B,2,0))","W2",False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.") I am not a 100% sure, but I am guessing this is because I have "" and '' in the formula. However, I am not sure how this can best be resolved. 
       
       
    • By SkysLastChance
      I am trying to autofill a range. I am getting stuck and I don't understand what I am doing wrong. 
      My goal is to auto fill some formulas that are next to a pivot table in columns A-C. 
      _Excel_RangeWrite($oNewWorkBook,Default,"2000","D3") _Excel_RangeWrite($oNewWorkBook,Default,"=(B3-D3)","E3") _Excel_RangeWrite($oNewWorkBook,Default,"100","F3") _Excel_RangeWrite($oNewWorkBook,Default,"=(C3-F3)","G3") $oNewWorkbook.ActiveSheet.Range("D3:G3").Select With $oNewWorkbook .Selection.AutoFill(.Range("D3:G77"),0) EndWith The data is not auto filling. 
      Hoping someone can point me in the right direction. 
×
×
  • Create New...