2 Questions Excel On OneDrive And comments in excel.

[caramen]

Hello all  

First question. 

If i want to inspect an item in a webpage without the right click how can i do ? 

This is what i want : 

This is what i got :

It is in an excel sheet on OneDrive. 

I need to automate Google or chrome or IE actions. With this sheet and i cant get the source code of wanted item. 

If you guys got a solution in this case i take it  

 

Second question : 

For @water Or Excel's experts  

Is there a macro to get the content of a comment in excel ? 

It look like this in OneDrive :

Like this in excel : 

 

Thanks before everyone  

 

[water]

Question 2:

#include <Excel.au3>
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
; Set a comment
With $oWorkbook.Worksheets(1).Range("E5").AddComment
    .Text("reviewed")
    .Visible = True
EndWith
; Retrieve the comment
MsgBox(0, "Comment", $oWorkbook.Worksheets(1).Range("E5").Comment.Text)

 

[caramen]

1000 Thanks   @water

[water]

[caramen]

 

#include <Excel.au3>

Global $oExcel = _Excel_Open()

Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; Set a comment
;~ With $oWorkbook.Worksheets(1).Range("E5").AddComment
;~     .Text("reviewed")
;~     .Visible = True
;~ EndWith

; Retrieve the comment
MsgBox(0, "Comment", $oWorkbook.Worksheets(1).Range("C2").Comment.Text)

Ok thanks

Edited November 21, 2018 by caramen

[caramen]

@water

As i can see in all other function

If i do an excel randge read. I can read a randge of cells somthing like A1 to A150 

Or just all used cells. 

$vRange [optional] Either a range object or an A1 range. If set to Default all used cells will be processed (default = keyword Default)

I whould like to do same with the comments. 

 

Becose i will have to check a calendar where i dont know for each month where are the comments. 

How should i do ?

Btw i tryed this : 

MsgBox(0, "Comment", $oWorkbook.Worksheets(1).Range("C1","C150").Comment.Text)

Ohhh wait let me try 

Range("C1:C10")

Facepalm  

 

I guess i got it but i have to figure out how to not exit after @error

I already asked you that but maybe 5 years ago i have to find back the answer

For $yNum = 1 To 10 Step 1
    $ReadedComment =  $oWorkbook.Worksheets(1).Range("C"&$yNum).Comment.Text)

    If $ReadedComment = "" Then 
    Else 
        MsgBox (0 , "" , $ReadedComment ) 
    EndIf

Next

 

Edited November 21, 2018 by caramen

[water]

RangeRead is not possible.
But you can specify a range and then loop through the comments in this range.
Will do some reading and come up with an example.

[caramen]

1 minute ago, water said:

RangeRead is not possible.
But you can specify a range and then loop through the comments in this range.
Will do some reading and come up with an example.

Finded that link. From where you had the exmple  

https://docs.microsoft.com/fr-fr/office/vba/api/excel.comment.next

[water]

This example lists all comments of a worksheet (address and comment):

#include <Excel.au3>
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
; Set a comment
With $oWorkbook.Worksheets(1).Range("E5").AddComment
    .Text("Test1")
    .Visible = True
EndWith
With $oWorkbook.Worksheets(1).Range("C3").AddComment
    .Text("Test2")
    .Visible = True
EndWith

; Retrieve all comments of a worksheet. Display address and comment
$oComments = $oWorkbook.ActiveSheet.Comments
For $oComment In $oComments
    ConsoleWrite("Address: " & $oComment.parent.Address & ", comment: " & $oComment.Text & @CRLF)
Next

 

[caramen]

Thanks @water

I worked non stop... phew...

 

This is what i got :

expandcollapse popup

#include <Excel.au3>
#include <File.au3>

Global $oExcel , $sWorkbook , $oWorkbook

$Seconde = 0
$Minute = 0
$TempDeRefresh = 1

ExporterComments ()

While (1)
   $Seconde += 1000
   ConsoleWrite($Seconde&"Seconde"&@CRLF)
   Sleep (1000)
      If $Seconde = 59000 Then
      $Seconde = 0
      $Minute +=1
      EndIf
      If $Minute = $TempDeRefresh Then
      $Minute = 0
      ExporterComments ()
      EndIf
WEnd

Func ExporterComments ()
   FileDelete (@ScriptDir & "\Output.txt")
   $oExcel = _Excel_Open(False)
   $sWorkbook = @ScriptDir & "\test.xlsx"
   $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
   $File = FileOpen ( @ScriptDir&"\Output.txt",$FO_APPEND)
   $oComments = $oWorkbook.ActiveSheet.Comments
      For $oComment In $oComments
      ConsoleWrite("Address: " & $oComment.parent.Address & ", comment: " & $oComment.Text & @CRLF)
      ;$CommentaireTronquer = StringTrimLeft ( $oComment.Text , 7 )
      $CommentaireTronquer =  $oComment.Text
      $CelluleTromquer = StringReplace ($oComment.parent.Address , "$" , "" )
      $CelluleTrans = StringReplace ($CelluleTromquer , "A" , "B" )
      $CelluleTrans = StringReplace ($CelluleTrans , "B" , "B" )
      $CelluleTrans = StringReplace ($CelluleTrans , "C" , "B" )
      $CelluleTrans = StringReplace ($CelluleTrans , "D" , "B" )
      $CelluleTrans = StringReplace ($CelluleTrans , "E" , "B" )
      $CelluleTrans = StringReplace ($CelluleTrans , "F" , "B" )
      $CelluleTrans = StringReplace ($CelluleTrans , "G" , "B" )
      $CelluleTrans = StringReplace ($CelluleTrans , "H" , "B" )
      $CelluleTrans = StringReplace ($CelluleTrans , "I" , "B" )
      $CelluleTrans = StringReplace ($CelluleTrans , "J" , "B" )
      $Heure = StringReplace ($CelluleTromquer , "1" , "2" )
      $Heure = StringReplace ($Heure , "3" , "2" )
      $Heure = StringReplace ($Heure , "4" , "2" )
      $Heure = StringReplace ($Heure , "5" , "2" )
      $Heure = StringReplace ($Heure , "6" , "2" )
      $Heure = StringReplace ($Heure , "7" , "2" )
      $Heure = StringReplace ($Heure , "8" , "2" )
      $Heure = StringReplace ($Heure , "9" , "2" )
      $Heure = StringReplace ($Heure , "0" , "2" )
      If $CelluleTrans ="B2" Or $CelluleTrans = "B3" Or $CelluleTrans = "B4" Or $CelluleTrans = "B5" Or $CelluleTrans = "B6" Or $CelluleTrans = "B7" Or $CelluleTrans = "B8" Or $CelluleTrans = "B9" Then $CelluleTrans = "B2"
      If $CelluleTrans ="B10" Or $CelluleTrans = "B11" Or $CelluleTrans = "B12" Or $CelluleTrans = "B13" Or $CelluleTrans = "B14" Or $CelluleTrans = "B15" Or $CelluleTrans = "B16" Or $CelluleTrans = "B17" Or $CelluleTrans = "B18" Then $CelluleTrans = "B10"
      $Date = _Excel_RangeRead($oWorkbook, Default, $CelluleTrans )
      $HeureLog = _Excel_RangeRead($oWorkbook, Default, $Heure )
      ConsoleWrite("Address: "&$CelluleTromquer & @CRLF)
      ConsoleWrite("date: "&$CelluleTrans & @CRLF)
      ConsoleWrite("date: "&$Date & @CRLF)
      ConsoleWrite("Heure: "&$Heure & @CRLF)
      ;FileWriteLine ( $File , $Date & " | " & $CommentaireTronquer )
      FileWriteLine ( $File , $Date &" | "& $HeureLog&" | " & $CommentaireTronquer & @CRLF )
      ;FileWriteLine ( $File , $Date )
      ;FileWriteLine ( $File , $CommentaireTronquer )
      ;MsgBox (0,"", $oComment.Text )
      Next
   FileClose ($File)
   _Excel_Close($oExcel)
EndFunc

With this Excel : (Attached)

I think i can fixe everything hours and the string operations are bad atm. I will improve,

exept one thing, how to automate the calcul of the day dunno how to get fusioned cells.

test.xlsx

Awww i just see hours are fusioned cells too. I ll check how to do this morning. 

Edited November 22, 2018 by caramen