Jump to content
Sign in to follow this  
CharlesStamp

Variables not being passed to array?

Recommended Posts

CharlesStamp

Hi. I've managed to piece this script together from tutorials and examples I've found, but I've hit a dead-end and could use a little help. I'm trying to create a script that will transfer text from a specific page onto an Excel worksheet. According to what I've read, it should enter 'pizza' into cel A3 of an Excel worksheet, and 'developer' into cel A4.

#include <Array.au3>
#include <Excel.au3>
#Include <FF.au3>
#include <String.au3>

    ; Go to page and copy the source to clipboard.
If _FFStart("http://ff-au3-example.thorsten-willert.de/") Then
    $sHTML = _FFReadHTML()
    If Not @error Then ClipPut($sHTML)
EndIf


Example()

Func Example()
    ; Declare variables and set to the words surrounding "pizza" and "developer".
    Local $sWordOne = _StringBetween($sHTML, "hlen Sie eine ", ":</legend>")
    Local $sWordTwo = _StringBetween($sHTML, "Mozilla ", " center")


    ; Create application object and create a new workbook
    Local $oAppl = _Excel_Open()
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookNew($oAppl)
        If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oAppl)
        Exit
        EndIf

; *****************************************************************************
; Write a 1D array to the active sheet in the active workbook
; *****************************************************************************
Global $aArray1D = [$sWordOne, $sWordTwo]
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray1D, "A3")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "1D array successfully written.")
EndFunc ;==>Example

But for some reason, the values of variables $sWordOne and $sWordTwo seem to get deleted before it reaches the Excel page, so the cels just wind up blank. I've double (and triple) checked that the _StringBetween variables are correct when used on their own. I've also found that if I change the variables to integers, it copies them to Excel as it should. I've spent a few hours looking at this and getting nowhere. Am I doing something obviously wrong?

Edited by CharlesStamp

Share this post


Link to post
Share on other sites
CharlesStamp

They do, but that doesn't seem to be the issue. If I use a string, integer or combination of them for the variables it passes them to Excel. It also seems to work fine with just one variable, too. But if there's more than one they just go blank. As far as I can tell it should work, but I don't have much experience with this. I had a similar problem displaying them in arrays, too.

Edited by CharlesStamp

Share this post


Link to post
Share on other sites
CharlesStamp

I haven't heard about maps before now, but I tried making the change you suggested and the problem now is that it copies $sWordOne to both cels. Again, I get a similar problem when I just display the array, so the issue doesn't seem to be related to Excel or its UDF. It seems to be a problem with how _StringBetween variables are managed.

Share this post


Link to post
Share on other sites
CharlesStamp

Perfect. Many thanks.

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  

  • Similar Content

    • Skeletor
      By Skeletor
      Hi Virtual People,
      My array works perfectly fine. However, what is the best practice if the line in the array doesn't have the correct amount of columns and if I can add a placeholder?

       
      For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") Next  
    • MrCheese
      By MrCheese
      hi all,
      reviewing the forum, this thread is applicable: 
       
       
      I wanted to know if there is now a better way to do this?
      In essence, I load a tab delimited txt file into an array (works well). I used tab, as some fields in the original csv contains commas.
      However, I needed autoit to manipulate this array, and output it as a csv.
      IF my array contains items with a comma, without double quotes around the field, then how best do I get a csv out of this?
      My current workaround is to filewritefromarray tab delimited, then open it in excel and save as a csv. I will need to check this to see how the address fields behave that contain a comma.
       
      Any thoughts would be appreciated.
       
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
    • CarlD
      By CarlD
      Is there a reliable way to ensure that data assigned to variables in a script is overwritten or deleted when the script exits? I have scripts that encrypt/decrypt data and would like to ensure, if possible, that the encryption keys and decrypted data do not stay in memory after the script exits. Thanks.
    • Skeletor
      By Skeletor
      Hi Guys,
      Is it possible to get a variable on your For..Next loop? 
       
      Local $Lines1 = _FileCountLines(C:\temp\test.txt) Local $linesToCount2 = $Lines1 + 2 $var = Number($linesToCount2) For $count = 1 To _FileCountLines($FileRead2) Step 1 For $i = $var To $count Next ;Code does stuff here Next Somehow my code doesn't work even though I thought I could convert the variable to a Integer / Number.
      This code I posted above does not move to the next value.
      But the code below does... why is that?
      For $count = 1 To _FileCountLines($FileRead2) Step 1 For $i = 2 To $count Next ;Code does stuff here Next  
      Why is the For loop resetting itself?
      Is it because the program does not cache the variable and needs to keep on acquiring this variable each time?
      If so , how would you make this variable static?

       
×