Posts by John_w

    Re: Web Scraping city code question, specific site

    Obviously I wasn't explicit enough. If x = 0 then Cells(x, 11) is an invalid cell reference, hence the error. Remember, it is Cells(row number, column number) and there is no such row number on a sheet as row 0.

    Put x = 1 somewhere before that line to define the x variable and then the line should work. But, as I said, I'll leave it to you to incorporate my code to suit.

    Re: Web Scraping city code question, specific site

    Quote from wittonline;796878

    After giving up and using the fixed URL you used in your example I get (run-time error '1004': Application-definded or object defined error) when I try to get city into the worksheet using... ThisWorkbook.Sheets("PhoneToCity").Cells(x, 11).Value = city

    Because the x variable is undefined and therefore zero. You should put Option Explicit at the top of the module to trap these sort of errors.

    No reason why this can't be a Sub procedure, as long as it has a unique name.

    Re: Web Scraping city code question, specific site

    The code below extracts the city from the <title> tag within the <head> tag, and is based on a new discovery when assigning the responseText from a XMLhttpRequest to a HTMLDocument.

    I'll leave it to you to incorporate the above method into your code.

    Analysis of two different methods of assigning the responseText string from a XMLhttpRequest to a HTMLDocument

    1. The usual method, using early binding of the HTMLDocument object, is:

    Dim HTMLdoc As HTMLDocument  'Or As Object
        Set HTMLdoc = New HTMLDocument
        HTMLdoc.body.innerHTML = XMLHttpRequest.responseText

    However, because that is assigning the responseText to the body element it results in an empty <head> tag and the <title> tag is lost:


    You might think that assigning the responseText to the parent of the body element, i.e. the main html element itself, would work:

    HTMLdoc.body.parentElement.innerHTML = XMLHttpRequest.responseText
    HTMLdoc.DocumentElement.innerHTML = XMLHttpRequest.responseText

    but both give the error "Could not set the innerHTML property. Invalid target element for this operation."

    2. Another method of assigning the responseText to a HTMLDocument uses late binding of the HTMLDocument object and the Write method:

    Dim HTMLdoc As Object
        Set HTMLdoc = CreateObject("HTMLfile")
        HTMLdoc.write XMLHttpRequest.responseText

    This time the <head> and <title> tags are fully populated in the HTMLDocument object:

    <!DOCTYPE html>
    <html lang="en">
    <title>240-505-00## Silver Spring &#x2714; CALLER ID &#x2714; CALLER NAME check-caller.NET</title>
    <meta name="description" content="Find out who owns the number 240-505-00## and some special information &#x2714; Click here to find out who calls you." />

    and we can extract the title tag text and parse the city from it.

    Re: New dynamic named range when open workbook

    And because your sheet name contains spaces you must surround it with apostrophes:

    ActiveWorkbook.Names.Add Name:="myCheckList", RefersTo:="='Release Deployment Manual'!$B$6:$J$" & LastRow

    Re: Import data from multiple pages of a website into an Excel workbook

    You can enter the date in any recognised regional format, and the VBA code above should correctly translate it into an Excel date.

    For dd/mm/yyyy region dates, any of these formats should work: 23/7/17, 23-07-17, 23-Jul-2017, etc.

    For mm/dd/yyyy region dates, any of these formats should work: 7/23/17, 07-23-17, Jul-23-2017, etc.

    Remember, the filtered data is written to Sheet2.

    Re: Insert row and populate from data line above = $30

    The macro will need a few changes - not trivial, I think.

    I have some questions about the Sheet2 data to help me understand it:

    1. Should the Q (tax) and R (shipping) cells be cleared when a tax or shipping amount is found? They were cleared in the first macro, but your dataset shows them not cleared.
    2. Shouldn't H10 value be 50.98, not 18.18? Similarly H16 should be 67.01.
    3. Row 11 shouldn't be there because shipping (R7) is zero. Correct?
    4. H17 should be 15, not 78.7?
    5. Similarly, further down the rows, the tax and shipping in column H don't tally with the first line of the invoice values in columns Q and R.
    6. Invoice number 8049157962 is in non-contiguous rows - C19, C21, C23, C25. Is that correct? Is that how the 'real' data will appear?

    Re: Insert row and populate from data line above = $30

    Payment received, thank you.

    Here is your workbook with macro.

    Note that the macro "Insert_Tax_and_Shipping_Rows" operates on the active sheet, so you must be on the sheet containing the data before running the macro, for example Sheet1 in the posted workbook.

    Let me know if it works as required or if you want any changes.