Jump to content

excel read and write function?


Recommended Posts

i created this excelfile

#include <Excel.au3>
#include <IE.au3>




Local $excelfile = "C:\Users\joesoef pc\Desktop\autoit\twitter.xlsx"
Local $oExcel = _ExcelBookOpen($excelfile);Create new book, make it visible

    _ExcelWriteCell($oExcel, "username", 1, 1) ;Write to the Cell
    _ExcelWriteCell($oExcel, "followers", 1, 4) ;Write to the Cell
    _ExcelWriteCell($oExcel, "following", 1, 8) ;Write to the Cell
_ExcelWriteCell($oExcel, "followbacklist3", 3, 1)

for $followers = 3 To 7
next


$nummers = ("4342")
$functie = _ExcelReadArray ($oExcel,3,1,8) ; username's
$actie = _ExcelWriteArray($oExcel,3,3,$nummers,1) ;follwers 

at the first collow it needs to read the username's ($functie) from first colum and row 3 to 9

and at the 3e collow it needs to write numbers/a function ( i already got the function for this)

but when i want run i dont get the nummer 4342 ($nummers),($nummers is just a test)

i need somefuntion like "_IEFormElementSetValue" for excel

Edited by Arclite86
Link to comment
Share on other sites

  • Moderators

Arclite86,

To edit the title, edit the first post and select the "Use Full Editor" option - that opens up the title as well. ;)

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

Arclite86,

Did you modify your post after Melba replied?

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 comment
Share on other sites

You want to write an array but  $nummers isn't an array, it's a simple variable.

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 comment
Share on other sites

You want to write an array but  $nummers isn't an array, it's a simple variable.

ok but instead of using a variable I want to create function that reads  the username's and gives the nummber of followers,I already got the function that will track the username's followers , I want to know if it possible in excel? could somebody please show me

Link to comment
Share on other sites

Sure it is possible.

But I'm not sure I understand what you have and what you need to do. You script is quite misleading. Example: You open an existing book but the comment says "Create new book" ;)

Which information does the book contain when you open it. Can you post a short example?

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 comment
Share on other sites

Sure it is possible.

But I'm not sure I understand what you have and what you need to do. You script is quite misleading. Example: You open an existing book but the comment says "Create new book" ;)

Which information does the book contain when you open it. Can you post a short example?

yes offcourse

141p47r.jpg

i need a function that reads the username's and shows the followers
I already got code to read one username
$link = GUICtrlRead($tekst4)

        $oIE = _IECreate("https://twitter.com/"&$link,1,0)

$oInput2s = _IETagNameGetCollection($oIE, "a")
For $oInput2 In $oInput2s
    If $oInput2.className() = "js-nav" Then
        If StringInStr($oInput2.innerHTML(), 'Volgend') Then
            ConsoleWrite(_StringBetween($oInput2.innerHTML(),'>','<')[0] & @CRLF)
            ExitLoop
        EndIf
    EndIf
Next
$numbvolgers = (_StringBetween($oInput.innerHTML(),'>','<')[0] & @CRLF)
$numbvolgend = (_StringBetween($oInput2.innerHTML(),'>','<')[0] & @CRLF)

GUICtrlSetData($tekst3,"volgers"& $numbvolgers &"volgend"&$numbvolgend)
if @error then MsgBox(0, "", "not found" )

 

 
but it only works for 1 username, so thats why i thought of using excel to read multiple username,
and than use a excel formula to calcullate the total followers 
Edited by Arclite86
Link to comment
Share on other sites

Put the stuff where you read from the website into a function, use _ExcelReadArray to read the usernames into an array, loop through this array and call the IE-function for every element of the array. Then write the result back into the Excel sheet.

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 comment
Share on other sites

Put the stuff where you read from the website into a function, use _ExcelReadArray to read the usernames into an array, loop through this array and call the IE-function for every element of the array. Then write the result back into the Excel sheet.

I do not quite understand you compeletly but this is what i got:

#include <Excel.au3>
#include <IE.au3>
#include <string.au3>



Local $excelfile = "C:\Users\joesoef pc\Desktop\autoit\twitter.xlsx"
Local $oExcel = _ExcelBookOpen($excelfile)

    _ExcelWriteCell($oExcel, "username", 1, 1)
    _ExcelWriteCell($oExcel, "followers", 1, 4)
    _ExcelWriteCell($oExcel, "following", 1, 8)
_ExcelWriteCell($oExcel, "followbacklist3", 3, 1)

for $followers = 3 To 7
next


local $aArray1 = _ExcelReadArray($oExcel,3,1,7,1)

        $oIE = _IECreate("https://twitter.com/"&$aArray1)

$oInput2s = _IETagNameGetCollection($oIE, "a")
For $oInput2 In $oInput2s
    If $oInput2.className() = "js-nav" Then
        If StringInStr($oInput2.innerHTML(), 'Volgend') Then
            ConsoleWrite(_StringBetween($oInput2.innerHTML(),'>','<')[0] & @CRLF)
            ExitLoop
        EndIf
    EndIf
Next
$numbvolgers = (_StringBetween($oInput2s.innerHTML(),'>','<')[0] & @CRLF)
$numbvolgend = (_StringBetween($oInput2.innerHTML(),'>','<')[0] & @CRLF)

_ExcelWriteArray ($oExcel,3,3,$numbvolgend,1)

With  _excelreadcell it reads the cell but with array it doesn't 

can sombody please adjust this code so it works, or tells me what to chance

Link to comment
Share on other sites

I'm sure this line won't work. How can you attach an array to an URL?

Global $aArray1 = _ExcelReadArray($oExcel, 3, 1, 7, 1)
$oIE = _IECreate("https://twitter.com/" & $aArray1)
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 comment
Share on other sites

 

I'm sure this line won't work. How can you attach an array to an URL?

Global $aArray1 = _ExcelReadArray($oExcel, 3, 1, 7, 1)
$oIE = _IECreate("https://twitter.com/" & $aArray1)

I agree, could you please show me what i should do instead so i understand the function , i try to learn from "help" but its not very clear to me

Link to comment
Share on other sites

Something like this (untested):

#include <Excel.au3>
#include <IE.au3>
#include <string.au3>

Global $excelfile = "C:\Users\joesoef pc\Desktop\autoit\twitter.xlsx"
Global $oExcel = _ExcelBookOpen($excelfile)

_ExcelWriteCell($oExcel, "username", 1, 1)
_ExcelWriteCell($oExcel, "followers", 1, 4)
_ExcelWriteCell($oExcel, "following", 1, 8)
_ExcelWriteCell($oExcel, "followbacklist3", 3, 1)

Global $aArray1 = _ExcelReadArray($oExcel, 3, 1, 7, 1)
For $i = 1 to $aArray1[0]
    _Volgend($aArray1[$i], $i + 2)
Next
Exit

Func _Volgend($sUser, $iRow)
    $oIE = _IECreate("https://twitter.com/" & $sUser)
    $oInput2s = _IETagNameGetCollection($oIE, "a")
    For $oInput2 In $oInput2s
        If $oInput2.className() = "js-nav" Then
            If StringInStr($oInput2.innerHTML(), 'Volgend') Then
                ConsoleWrite(_StringBetween($oInput2.innerHTML(),'>','<')[0] & @CRLF)
                Loop
            EndIf
        EndIf
    Next
    $numbvolgers = (_StringBetween($oInput2s.innerHTML(),'>','<')[0] & @CRLF)
    $numbvolgend = (_StringBetween($oInput2.innerHTML(),'>','<')[0] & @CRLF)
    _ExcelWriteCel($oExcel, $numbvolgend, $iRow, 3)
EndFunc
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 comment
Share on other sites

Something like this (untested):

#include <Excel.au3>
#include <IE.au3>
#include <string.au3>

Global $excelfile = "C:\Users\joesoef pc\Desktop\autoit\twitter.xlsx"
Global $oExcel = _ExcelBookOpen($excelfile)

_ExcelWriteCell($oExcel, "username", 1, 1)
_ExcelWriteCell($oExcel, "followers", 1, 4)
_ExcelWriteCell($oExcel, "following", 1, 8)
_ExcelWriteCell($oExcel, "followbacklist3", 3, 1)

Global $aArray1 = _ExcelReadArray($oExcel, 3, 1, 7, 1)
For $i = 1 to $aArray1[0]
    _Volgend($aArray1[$i], $i + 2)
Next
Exit

Func _Volgend($sUser, $iRow)
    $oIE = _IECreate("https://twitter.com/" & $sUser)
    $oInput2s = _IETagNameGetCollection($oIE, "a")
    For $oInput2 In $oInput2s
        If $oInput2.className() = "js-nav" Then
            If StringInStr($oInput2.innerHTML(), 'Volgend') Then
                ConsoleWrite(_StringBetween($oInput2.innerHTML(),'>','<')[0] & @CRLF)
                Loop
            EndIf
        EndIf
    Next
    $numbvolgers = (_StringBetween($oInput2s.innerHTML(),'>','<')[0] & @CRLF)
    $numbvolgend = (_StringBetween($oInput2.innerHTML(),'>','<')[0] & @CRLF)
    _ExcelWriteCel($oExcel, $numbvolgend, $iRow, 3)
EndFunc

the code doesn't work could you please check it agian and test it,

because i dont know how to adjust it correctly

i have adjust it it a little bit but it still doesn work

#include <Excel.au3>
#include <IE.au3>
#include <string.au3>

Global $excelfile = "C:\Users\joesoef pc\Desktop\autoit\twitter.xlsx"
Global $oExcel = _ExcelBookOpen($excelfile)

_ExcelWriteCell($oExcel, "username", 1, 1)
_ExcelWriteCell($oExcel, "followers", 1, 4)
_ExcelWriteCell($oExcel, "following", 1, 8)
_ExcelWriteCell($oExcel, "followbacklist3", 3, 1)

Global $aArray1 = _ExcelReadArray($oExcel, 3, 1, 7,1)
For $i = 1 to $aArray1[0]
    _Volgend($aArray1[$i], $i + 2) ;_volgend?
Next
Exit

Func _Volgend($sUser, $iRow)   ;_volgend?
    $oIE = _IECreate("https://twitter.com/" & $sUser)
    $oInput2s = _IETagNameGetCollection($oIE, "a")
    For $oInput2 In $oInput2s
        If $oInput2.className() = "js-nav" Then
            If StringInStr($oInput2.innerHTML(), 'Volgend') Then
                ConsoleWrite(_StringBetween($oInput2.innerHTML(),'>','<')[0] & @CRLF)
                ExitLoop ; I chanced it from "loop" to exitloop
            EndIf
        EndIf
    Next
    $numbvolgers = (_StringBetween($oInput2s.innerHTML(),'>','<')[0] & @CRLF)
    $numbvolgend = (_StringBetween($oInput2.innerHTML(),'>','<')[0] & @CRLF)

    _ExcelWriteCell ($oExcel, $numbvolgend, $iRow, 3)
EndFunc
Link to comment
Share on other sites

What do you mean by "Doesn't work"?

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 comment
Share on other sites

What do you mean by "Doesn't work"?

excel start but, the "_ExcelWriteCell($oExcel, $numbvolgend, $iRow, 3)" part doesn't work,(the follow tracker normally always works so that is not the problem) the number doesn show up in the 3 collum, i am trying to find a solution.., could you please test it

Link to comment
Share on other sites

Does the ConsoleWrite statement write the correct value to the Console?

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 comment
Share on other sites

Does the ConsoleWrite statement write the correct value to the Console?

yes the consolewrite is okay,

i got a small script that shows how it works 

#include <IE.au3>
#include <String.au3>

$oIE = _IECreate("https://twitter.com/followbacklist3",1,0)
$oInputs = _IETagNameGetCollection($oIE, "a")
For $oInput In $oInputs
    If $oInput.className() = "js-nav" Then
        If StringInStr($oInput.innerHTML(), 'Volgers') Then;or Followers depends withc language your using
            ConsoleWrite(_StringBetween($oInput.innerHTML(),'>','<')[0] & @CRLF);number of them
            ExitLoop
        EndIf
    EndIf
Next
MsgBox(0, "number followers", _StringBetween($oInput.innerHTML(),'>','<')[0])

 as you see it this only works with 1 username at a time thats why i thought of excel 

 i am trying to make it work but i cant solve it ,could you please check it out.

Link to comment
Share on other sites

But you are using a different web address in your example.

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 comment
Share on other sites

But you are using a different web address in your example.

#include <IE.au3>

#include <String.au3>
$name = "followbacklist6"
$oIE = _IECreate("https://twitter.com/"&$name,1,1)
$oInputs = _IETagNameGetCollection($oIE, "a")
For $oInput In $oInputs
    If $oInput.className() = "js-nav" Then
        If StringInStr($oInput.innerHTML(), 'Volgers') Then
            ConsoleWrite(_StringBetween($oInput.innerHTML(),'>','<')[0] & @CRLF)
            ExitLoop
        EndIf
    EndIf
Next
MsgBox(0, "number followers", _StringBetween($oInput.innerHTML(),'>','<')[0])

now i use a variable to read 1 username and it also works, but i want to read multiple username at the same time and write it down somewhere so i can see it

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...