Data (table) from web page to excel

[pecsenye]

Here it's the code. It's good and works for me but...

My problem: It's  opening the "Test.xlsm" and working in it and still working while I using this "Test.xlsm" excel. Cause I always using the "Test.xlsm" therefore I would like to the currently opened Test.xlsm working in DataSheet and don't open another "Test.xlsm" Excel.

Code:

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

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test.xlsm")
$oWorkbook.Sheets("DataSheet").Activate
Local $iRow2Write2 = 1
Local $oIE = _IECreate("URL", 0, 0)
Local $oTableCollection = _IETableGetCollection($oIE)
For $oAll In $oTableCollection
    $aTableData = _IETableWriteToArray($oAll, True)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aTableData, "A" & $iRow2Write2)
    $iRow2Write2 = $oWorkBook.Activesheet.UsedRange.Rows.Count + 1
 Next

_IEQuit($oIE)

Thanks for helping!

[Danp2]

Have you tried using _Excel_BookAttach instead of _Excel_BookOpen?

[pecsenye]

Then error message: Variable must be of type "Object". 

Edited February 15, 2022 by pecsenye

[water]

You should always check @error and @extended after calling an _Excel_* function to make sure everything worked as expected.

[pecsenye]

ok and how do i do it? The original code works perfectly, but if I replace Excel_BookOpen / Excel_BookAttach then I get an error message. 

[water]

Check the help file. Every _Excel_* function comes with examples that do full error checking.

[Danp2]

You haven't shown us the revised line of code, so we can only guess at what is wrong. If you simply changed the function name without adjusting the parameters, then that won't work.

[pecsenye]

yes i just exchanged in the second row... Yes i see it doesn't work

[pecsenye]

41 minutes ago, water said:

You should always check @error and @extended after calling an _Excel_* function to make sure everything worked as expected.

Okay.  The examples in the help file run without error. 

[water]

That's fine because I have written those example scripts, added code to do the error checking and tested them
Now guess what you have to do to your code?

[pecsenye]

4 hours ago, water said:

That's fine because I have written those example scripts, added code to do the error checking and tested them
Now guess what you have to do to your code?

I'm trying...

Update: It worked! Sure, the code "isn't nice", but it works!

Edited February 16, 2022 by pecsenye

[water]

[juxwillx]

Did you check @extended and @error after calling the function?

[pecsenye]

2 hours ago, water said:

Thanks! I have an other question: is there any solution that the code in the url came from an Excel cell? For example if A1 cell valie = www.autoit.com then open this link, if A1 cell value = www.google.com then open it. 

[water]

Use _Excel_RangeRead to read the cell into a variable ($sCellValue) , then use "ShellExecute($sCellValue)" to call the default browser and open the URL.

[pecsenye]

15 minutes ago, water said:

Use _Excel_RangeRead to read the cell into a variable ($sCellValue) , then use "ShellExecute($sCellValue)" to call the default browser and open the URL.

Ok, so this is original code:

Local $sFileName1 = @ScriptDir & "\Test.xlsm"

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test.xlsm")
$oWorkbook = _Excel_BookAttach($sFileName1)
$oWorkbook.Sheets("DataSheet").Activate
Local $iRow2Write2 = 1
Local $oIE = _IECreate("URL", 0, 0)
Local $oTableCollection = _IETableGetCollection($oIE)
For $oAll In $oTableCollection
    $aTableData = _IETableWriteToArray($oAll, True)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aTableData, "A" & $iRow2Write2)
    $iRow2Write2 = $oWorkBook.Activesheet.UsedRange.Rows.Count + 1
 Next

_IEQuit($oIE)

It's working...

I trying what you wrote to me, but I need help!  

Local $sFileName1 = @ScriptDir & "\Test.xlsm"
$sCellValue = $oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test.xlsm")
$oWorkbook = _Excel_BookAttach($sFileName1)
$oWorkbook.Sheets("DataSheet").Activate
Local $iRow2Write2 = 1
ShellExecute($sCellValue)
;~ Local $oIE = _IECreate("URL", 0, 0)
Local $oTableCollection = _IETableGetCollection($oIE)
For $oAll In $oTableCollection
    $aTableData = _IETableWriteToArray($oAll, True)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aTableData, "A" & $iRow2Write2)
    $iRow2Write2 = $oWorkBook.Activesheet.UsedRange.Rows.Count + 1
 Next

This might be the last step... Thank you!

[water]

Again, this is wrong:

Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test.xlsm") ; Returns the workbook object
$oWorkbook = _Excel_BookAttach($sFileName1)                            ; Returns the workbook object

BookOpen opens an existing workbook, Attach connects to a workbook manually opened by the user

Means: Use Open OR Attach.

[water]

If you need to process the URL (extract data etc.) then I suggest to use IE:

$oIE = _IECreate($sCellValue)

 

[pecsenye]

 

 

[pecsenye]

3 hours ago, water said:

If you need to process the URL (extract data etc.) then I suggest to use IE:

$oIE = _IECreate($sCellValue)

 

Thanks! It works now! But now I see the open website. i tried @ SW_HIDE but it doesn't work.

Code:

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

Local $sFileName1 = @ScriptDir & "\Test.xlsm"

Local $oExcel = _Excel_Open()
$oWorkbook = _Excel_BookAttach($sFileName1)
$oWorkbook.Sheets("DataTable").Activate
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)

Local $iRow2Write2 = 1
Local $oIE = _IECreate($aResult)

Local $oTableCollection = _IETableGetCollection($oIE)
For $oAll In $oTableCollection
    $aTableData = _IETableWriteToArray($oAll, True)
    _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aTableData, "A" & $iRow2Write2)
    $iRow2Write2 = $oWorkBook.Activesheet.UsedRange.Rows.Count + 1
 Next
 
  _IEQuit($oIE)

 

Edited February 16, 2022 by pecsenye