Range functions in excel

[MattHiggs]

I seem to be having an issue with getting the "RangeWrite" function in the excel UDF.  whenever I attempt to use it to write to excel, nothing transfers over...  Could anyone shed some light on why this is the case?  I imagine it can be any number of things: Information is not being read from the file that contains the information, the way I have the user input the range they want to use, etc.  Any input appreciated.  Sorry, I do not have a full understanding of COM objects as of yet.  And this occurs regardless if I use the roundabout method displayed in current script (for loop to loop through the rows), or by just setting the value as an array and letting the function assign values.

 

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=..\..\Documents\th.ico
#AutoIt3Wrapper_Res_SaveSource=y
#AutoIt3Wrapper_Res_Language=1033
#AutoIt3Wrapper_Res_requestedExecutionLevel=requireAdministrator
#AutoIt3Wrapper_Add_Constants=n
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
; *** Start added by AutoIt3Wrapper ***
#include <EditConstants.au3>
#include <FileConstants.au3>
#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
; *** End added by AutoIt3Wrapper ***
#cs ----------------------------------------------------------------------------

 AutoIt Version: 3.3.15.0 (Beta)
 Author:         myName

 Script Function:
    Template AutoIt script.

#ce ----------------------------------------------------------------------------

; Script Start - Add your code below here
#include <Constants.au3>
#include <Excel.au3>
#include <GUIListBox.au3>
#include <WindowsConstants.au3>
#include <File.au3>
Do
    FileDelete ( @AppDataDir & "\data.txt" )
Until Not FileExists ( @AppDataDir & "\data.txt" )

$num = 0
$file = FileOpen ( @AppDataDir & "\data.txt", $FO_APPEND )
#Region ### START Koda GUI section ### Form=C:\Users\E0280504\OneDrive\always script\form\paravue sim.kxf
$Form1 = GUICreate("Enter SIM number into excel", 299, 312, 192, 124)
$Label1 = GUICtrlCreateLabel("SIM Barcode transfer to excel", 24, 16, 249, 28)
GUICtrlSetFont(-1, 14, 400, 0, "MS Sans Serif")
$Input1 = GUICtrlCreateInput("", 22, 56, 233, 21)
$Input2 = GUICtrlCreateInput("", 192, 160, 33, 21, BitOR($GUI_SS_DEFAULT_INPUT,$ES_NUMBER))
GUICtrlSetState(-1, $GUI_DISABLE)
$List1 = GUICtrlCreateList("", 32, 96, 121, 149)
$Pic1 = GUICtrlCreatePic("C:\Users\E0280504\Documents\th.jpg", 176, 88, 65, 57)
$Button1 = GUICtrlCreateButton("Send to Excel", 32, 264, 89, 25)
GUICtrlSetCursor (-1, 0)
$Button2 = GUICtrlCreateButton("How to use", 176, 264, 73, 25)
GUICtrlSetCursor (-1, 0)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit

        Case $Input1
            $num += 1
            GUICtrlSetData ( $Input2, $num )
            _GUICtrlListBox_InsertString ( $List1, GUICtrlRead ( $Input1 ), 0 )
            FileWriteLine ( $file, GUICtrlRead ( $Input1 ) )
            GUICtrlSetData ( $Input1, '' )
        Case $List1
            FileFlush ( $file )
            $cry = _GUICtrlListBox_GetCaretIndex ( $List1 )
            ConsoleWrite ( $cry )
            $tot = UBound ( FileReadToArray ($file) ) - 1
            $in = $tot - $cry
            GUICtrlSetData ( $Input2, $in )
            #Region --- CodeWizard generated code Start ---
;InputBox features: Title=Yes, Prompt=Yes, Default Text=No
If Not IsDeclared("sInputBoxAnswer") Then Local $sInputBoxAnswer
$sInputBoxAnswer = InputBox("New value?","What would you like to change the selected value to?")
Select
    Case @Error = 0 ;OK - The string returned is valid
        _GUICtrlListBox_ReplaceString ( $List1, $cry, $sInputBoxAnswer )
        FileFlush ( $file )
        _FileWriteToLine ( $file, $in, $sInputBoxAnswer, True )
        FileFlush ( $file )
        GUICtrlSetData ( $Input2, $num )
    GUICtrlSetState ( $Input1, $GUI_FOCUS )
    Case @Error = 1 Or $sInputBoxAnswer == "" ;The Cancel button was pushed
    GUICtrlSetData ( $Input2, $num )
    GUICtrlSetState ( $Input1, $GUI_FOCUS )
EndSelect
#EndRegion --- CodeWizard generated code End ---

        Case $Button1
            opennow ()
        Case $Button2
            #Region --- CodeWizard generated code Start ---

;MsgBox features: Title=Yes, Text=Yes, Buttons=OK, Icon=Info
MsgBox($MB_OK + $MB_ICONASTERISK,"Simple to use",'This application user interface is very minimalist.  All you have to do is scan the bar codes into the top input box, and the resulting value will appear in the list below it.  If, by any chance, you make a mistake and scan the wrong bar code, all you have to do is click the incorrect value in the list and you will be prompted to change the value to whatever you need to.  Once you have added all of the bar code items into the list, click "Send to excel", and select to either open an existing Excel document or create a new one, and the values you scanned in will automatically input into the excel document (if you open an existing document, you will be prompted to pick the cell you want to designate as your starting point).  Hope this helps.')
#EndRegion --- CodeWizard generated code End ---

    EndSwitch
WEnd
Func opennow()
#Region --- CodeWizard generated code Start ---


;MsgBox features: Title=Yes, Text=Yes, Buttons=Yes and No, Default Button=Second, Icon=None
If Not IsDeclared("iMsgBoxAnswer") Then Local $iMsgBoxAnswer
$iMsgBoxAnswer = MsgBox($MB_YESNO + $MB_DEFBUTTON2,"Edit existing document","Do you have a exsisting document you want to edit?  Select no if you want to create new.")
Select
    Case $iMsgBoxAnswer = $IDYES
        $name = FileOpenDialog ( "Choose your file", @MyDocumentsDir & "\", "Excel workbooks (*.xlsx;*.xlsm;*.xls)" )
        If $name == "" Then
            opennow()
        Else
            $oExcel = _Excel_Open ()
            $oWorkbook = _Excel_BookOpen ( $oExcel, $name )
            $row = 0
            #Region --- CodeWizard generated code Start ---
;InputBox features: Title=Yes, Prompt=Yes, Default Text=No, Mandatory
If Not IsDeclared("sInputBoxAnswer") Then Local $sInputBoxAnswer
$sInputBoxAnswer = InputBox("Start Row","Specify the row (numbers on the left) followed by the column (letter at the top) you would like to designate as the starting point for data input.  Separate the row and column with a comma(,) and no spaces.",""," M")
Select
    Case @Error = 0 ;OK - The string returned is valid
        $split = StringSplit ( $sInputBoxAnswer, "," )
        If $split[0] = 2 And StringIsDigit ( $split[1] ) = 1 And StringIsAlpha ( $split[2] ) = 1 Then
        $row = $split[1]
        $colum = $split[2]
        Else
        _Excel_BookClose ( $oWorkbook )
            _Excel_Close ( $oExcel )
        opennow()
        EndIf

    Case @Error = 1 ;The Cancel button was pushed
        Exit
    Case Else ;The InputBox failed to open
        Exit
EndSelect
#EndRegion --- CodeWizard generated code End ---
#Region --- CodeWizard generated code Start ---


;MsgBox features: Title=Yes, Text=Yes, Buttons=OK, Icon=Info
MsgBox($MB_OK + $MB_ICONASTERISK,"Take a moment","Due to the nature of the data that is being entered into excel, excel will automatically try to format the data entered, and will not display or show up as the original values you entered,  To learn how, go to help")
#EndRegion --- CodeWizard generated code End ---
            FileFlush ( $file )
            $filearray = FileReadToArray ( $file )
            $newrow = $row + UBound ( $filearray )
            For $ff = 0 To UBound ( $filearray ) - 1 Step 1
                _Excel_RangeWrite ( $oWorkbook, Default, $filearray[$ff], $colum & $row )
                $row += 1
            Next

            _Excel_BookSave ( $oWorkbook )
#Region --- CodeWizard generated code Start ---

;MsgBox features: Title=Yes, Text=Yes, Buttons=OK, Icon=Info
MsgBox($MB_OK + $MB_ICONASTERISK,"saved","Your excel document has been saved")
#EndRegion --- CodeWizard generated code End ---

        EndIf


    Case $iMsgBoxAnswer = $IDNO

        $name1 = FileSaveDialog ( "Where would you like to save your new document?", @MyDocumentsDir, "Excel workbooks (*.xlsx;*.xlsm;*.xls)", 0, "paravue_tablet_inventory.xlsx" )
        If FileExists ( $name1 ) Then
            Do
                $name1 = FileSaveDialog ( "File already Exists.  Please rename file or save somewhere else.", @MyDocumentsDir, "Excel workbooks (*.xlsx;*.xlsm;*.xls)", 0, "paravue_tablet_inventory.xlsx" )
            Until Not FileExists ( $name1 )
        EndIf
        If $name1 == "" Then
            opennow()
        Else

        $oExcel = _Excel_Open ()
        $oBook = _Excel_BookNew ( $oExcel )
        ;MsgBox features: Title=Yes, Text=Yes, Buttons=OK, Icon=Info
MsgBox($MB_OK + $MB_ICONASTERISK,"Take a moment","Due to the nature of the data that is being entered into excel, excel will automatically try to format the data entered, and will not display or show up as the original values you entered,  To learn how, go to help")
#EndRegion --- CodeWizard generated code End ---
$row = 1
$colum = "A"
FileFlush ( $file )
        $filearray = FileReadToArray ( $file )
        For $ff = 0 To UBound ( $filearray ) - 1 Step 1
                _Excel_RangeWrite ( $oBook, Default, $filearray[$ff], $colum & $row )
                $row += 1
            Next
            _Excel_BookSaveAs ( $oBook, $name1 )
            #Region --- CodeWizard generated code Start ---
;MsgBox features: Title=Yes, Text=Yes, Buttons=OK, Icon=Info
MsgBox(64,"Excel Document saved","Your excel document has been saved to your Documents folder.")
#EndRegion --- CodeWizard generated code End ---
        EndIf

EndSelect
#EndRegion --- CodeWizard generated code End ---
EndFunc
Func imei ()

EndFunc

 

[water]

You need to do some error checking. What is the value of @error after _Excel_RangeWrite?