Passing HTML Elements to Procedures (as parameter)
I would like to call a procedure and pass some HTML elements to it.
If I call procedure this way
SimpleSearch (eNames) then the
For Each statement returns error
Object doesn't support this property or method
Sub Navigate() Set eNames = IE.Document.getElementsByTagName("button") SimpleSearch (eNames) End Sub Sub SimpleSearch(ssitems) For Each ssitem In ssitems If ssitem.innerText = "Simple Search" Then ssitem.Focus ssitem.Click Exit For End If Next ssitem Do While IE.Busy Loop Do While IE.ReadyState < 4 Loop End Sub
Remove the parenthesis from the procedure call:
Sub Navigate() Set eNames = IE.Document.getElementsByTagName("button") SimpleSearch eNames End Sub
In VBA functions use parenthesis but procedures don't:
Sub Test() MyString = MyFunction("parameter") 'function call with parenthesis MyProcedure "parameter" 'procedure call without parenthesis End Sub Function MyFunction(Parameter As String) As String 'some code MyFunction = "Return Value" 'a function should return a value End Function Sub MyProcedure(Parameter As String) 'some code End Sub
See also questions close to this topic
VBA: why I cannot use "=" to transfer data from one sheet to another sheet?
I want to use
"="operator to transfer data from "Sheet1" to "Sheet2" because it is fast. However, the following code doesn't work:
Sheets("Sheet2").range(cells(1, 1),cells(1,5)).value = Sheets("Sheet1") .range(cells(1, 1),cells(1,5)).value
Delete hidden rows in auto-filter in coauthoring spreadsheet
What I want to do is to reset the filter then delete all the rows below row 9.
This should be a easy task, but this is a coauthoring spreadsheet, i want to avoid using
ActiveSheet.ShowAllDataas this will require all people to refresh the spreadsheet.
I tried to use keybd_event to simulate ALT+A+C to clear the filter, but it dosen't quite work.
keybd_event VK_Alt, 0, 0, 0 keybd_event VK_A, 0, 0, 0 keybd_event VK_A, 0, KEYEVENTF_KEYUP, 0 keybd_event VK_Alt, 0, KEYEVENTF_KEYUP, 0 keybd_event VK_C, 0, 0, 0 keybd_event VK_C, 0, KEYEVENTF_KEYUP, 0 Worksheets(1).Rows(9 & ":" & Worksheets(1).Rows.Count).Delete
My thought is when the marco simulate alt+a+c, the hidden rows do not show until the whole macro is finished. So it only delete those visible rows, and after the macro finished, those hidden rows come out.
Dose anyone know better ways to do it? Any help would be appreciated! Thanks!
Stop Excel Form Auto Convert Date Time To Number
In column A I have the value of:
A|and in column B I have
I am merging both of the columns with the formula
=A1&""&B1&, yet the result I am getting is
A|43451.4670486111. However, this result does not meet my expectations as I need the date and time formatting preserved.
Here is an example of the expected result:
I have 380 column to merge, How to stop Excel from converting date & time to number?
How to play windows media player in VBA, starting from a spesific duration
I am doing a project of video based analysis. I want to know how to play a windows media player in excel using vba, for a specific time, such as playing the video from duration 00.06 until 00.14. Because the current state that I have known is playing the video using vba code from 00.00 to the end of the duration.
Thank you, your help will be much appreciated
VBA Highlight Cells that don't match master list in a column
I'm having some trouble taking apart this code I found. I'm looking to error check a group of cells and I'd like to highlight the ones that don't match a Master list.
For some reason I can't get this to highlight the cells that DON'T match the list.
Please help me through this, I'd be very grateful!
Dim myRange, v, f, a Set myRange = Range("P12:P200") For Each v In Workbooks("Formula_Weighup Audit Auto-Fill Final").Worksheets("Active Master List").Range("J2:J1054") Set f = myRange.Find(what:=v, lookat:=xlPart) If f Is Nothing Then a = f.Address Do f.Interior.ColorIndex = 5 Set f = myRange.FindNext Loop While f.Address <> a End If Next MsgBox "Error: R&D #(s) do not exist" & vbNewLine & "(see highlighted items)" Exit Sub
Runtime error 1004 range assigned function
I am doing operations on stock data, and I am attempting to assign functions to ranges.
Sub RSIFunction() Dim numberOrRowsToSkip As Integer: numberOFRowsToSkip = 70 Dim TrialsToExecute As Integer: TrialsToExecute = 127 Dim firstFormula As Range: Set firstFormula = Range("J154:J220") Dim secondFormula As Range: Set secondFormula = Range("K154:K220") Dim thirdFormula As Range: Set thirdFormula = Range("L154:L220") Dim fourthFormula As Range: Set fourthFormula = Range("M167:M220") Dim fifthFormula As Range: Set fifthFormula = Range("N167:N220") Dim sixFormula As Range: Set sixFormula = Range("O167:O220") Dim sevenFormula As Range: Set sevenFormula = Range("P167:P220") Dim i As Integer For i = 0 To TrialsToExecute firstFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=RC[-6]-R[-1]C[-6]" secondFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=IF(RC[-1]>0,RC[-1],"")" thirdFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=IF(RC[-2]<0,RC[-2],"")" fourthFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=(SUM(R[-13]C[-2]:RC[-2])/14)" fifthFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=(SUM(R[-13]C[-2]:RC[-2])/14)" sixFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=RC[-2]/RC[-1]" sevenFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=IF(RC[-2]=0,100,100-(100/(1+RC[-1])))" Next i End Sub
Runtime error '1004': Application-defined or Object-defined error
After pressing Debug, this line is highlighted:
secondFormula.Offset(i * numberOFRowsToSkip).FormulaR1C1 = _ "=IF(RC[-1]>0,RC[-1],"")
Replace countifs with advanced filter or other macro - guidance appreciated
I have quite a heavy workbook. I am curious if any of you could advise how to best do it so that the processing speed isn't affected much.
TblOccupancy[code] TblOccupancy[start] TblOccupancy[end] TblOccupancy[setting]
The above is used on another sheet.
column B6:B370contains dates
c3:ie5contains 3 criteria
I have created a formula that counts number of users of given code present on specific date.
I was hoping you could help me converting this into macro [Excel-vba]
and that it would in turn speed up processing and automation in general.
This is what I use in
Thank you in advance. At the moment testing advanced filter, struggling with multi-criteria & between dates. I will comment if i solved this one. In the meantime sending hugs for anyone willing to help!
How to get href attributes values of all links on the page?
Hi guys im really confused im trying to get href attributes values of every link on my page. But so far i managed to do it only for one item with this code:
var x = document.getElementsByTagName("a").getAttribute("href");
Well so i said to myself ez... if i get rid of  it will get href from all the anchor tags. But that didnt obviously happend it gave me error "getAttribute is not a function"
Do i have to create some kind of loop?
Java dom document.getElementsByTagName("") returns null?
I've got a xml as below:
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <bookshelf> <book ISBN="a01" press="AD press"/> <book ISBN="a11" press="BC press"> <book>JavaSE</book> <Author>John</Author> <price>35.00</price> </book> <book ISBN="b11" press="XY press"> <book>Android</book> <Author>Smith</Author> <price>35.00</price> </book> </bookshelf>
And a simple Java dom program as below:
System.out.println(currentPath); DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); Document document = builder.parse(currentPath + "/book.xml"); Node node = document.getElementsByTagName("").item(1); System.out.println(node);
(1) It prints out "null", why?
(2) I've googled and find many sample code to call document.getElementsByTagName(""), I wish to know if "" stands for root element or sth else?
Thanks a lot.
VBA cannot get data from HTML with .getElementsByTag() nor .getElementByID()
My current project consists of retrieving data from HTML source code. Specifically, I am looking at crash cases on this website:
I would like to gather all relevant data from the HTML by looking for
.innertextof specific tags/IDs.
My code so far:
Sub ExtractData() mystart: 'First I create two Internet Explorer object Set objIE = CreateObject("InternetExplorer.Application") 'this browser contains the list of cases objIE.Top = 0 objIE.Left = 0 objIE.Width = 1600 objIE.Height = 900 objIE.Visible = True 'We can see IE Set objIEdata = CreateObject("InternetExplorer.Application") 'this browser opens the specific case objIEdata.Top = 0 objIEdata.Left = 0 objIEdata.Width = 1600 objIEdata.Height = 900 objIEdata.Visible = True 'We can see IE On Error Resume Next objIE.navigate ("https://crashviewer.nhtsa.dot.gov/LegacyCDS/Index") 'url of website Do DoEvents If Err.Number <> 0 Then objIE.Quit Set objIE = Nothing GoTo mystart: End If Loop Until objIE.readystate = 4 'we define an object variable Alllinks and loop through all the links to search for Set aAlllinks = objIE.document.getElementsByTagName("button") 'looks for Search Button For Each Hyperlink In aAlllinks If Hyperlink.innertext = " Search" Then Hyperlink.Click Exit For Else MsgBox "Search Button was not found. Please improve code!" End If Next Application.Wait (Now + TimeValue("0:00:02")) Set bAlllinks = objIE.document.getElementsByTagName("a") 'all Hyperlinks on webpage start with Tag "a" For Each Hyperlink In bAlllinks If UBound(Split(Hyperlink.innertext, "-")) = 2 And Len(Hyperlink.innertext) = 11 Then 'case specific to find the Hyperlinks which contain cases Debug.Print Hyperlink.innertext '2nd IE is used for each case restart: objIEdata.navigate (Hyperlink.href) 'url of each case Do DoEvents If Err.Number <> 0 Then objIEdata.Quit Set objIE = Nothing GoTo restart: End If Loop Until objIEdata.readystate = 4 Set register = objIEdata.document.getElementByTagName("tbody") 'objIEdata.document.getElementByID("main").getElementByID("mainSection") '.getElementByID("bodyMain").getElementsByTagName("tbody") For Each untermenue In register Debug.Print untermenue.innerHTML Next Application.Wait (Now + TimeValue("0:00:02")) End If Next objIE.Quit objIEdata.Quit End Sub
Note that the visibility of IE is just for debugging reasons.
The part which confuses me is
Set register = objIEdata.document.getElementByTagName("tbody").
If I look for
.TagName("tbody")the variable register is returned empty and the same happens if I look for
.ID("bodyMain"). Unfortunately, I am not familiar with HTML and how VBA interacts with a HTML document. I was under the impression that I could address all elements by their ID, if they happen to have one, but this does not seem to work.
Do I need to work myself through the HTML "branches" or should the code be able to find each ID, no matter the "child" it is to be found in?
Thanks a lot