Posts by John_w

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


    Quote from SLASH729;794972

    what should I do if I want to download data between 1 july 2017 to 15 july 2017

    There is nothing on the web page which allows you to specify a range of dates for the data you want to download. Therefore my macro above downloads all the available data - all the pages - and you would have to filter the data within Excel to restrict it a range of dates. The following macro does that - sorts and filters the data on Sheet1 between start and end dates and copies the filtered data to Sheet2.


    Re: Stop Application.Ontime to stop running upon closing so that it won't open back u


    Store the scheduled time in a Public variable and use that time to start or stop the timer:

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


    SLASH729 try this:

    Re: Click a IE9 Web Page button using VBA


    Your post is a little ambiguous so it's hard to understand why this isn't working.


    Quote from dejamls;794598


    I tried stepping through code and got Runtime error'438': Object doesn't support the property or method.

    Which line causes that error?


    Quote


    With the Ctrl+G action I got Runtime error '91': Object variable or with block variable not set.

    Which means resetButton is Nothing (undefined). Are you entering the command after the Set resetButton line has executed? (Step through the code line by line by pressing the F8 key). Did you try (1) instead of (0)?


    You can also use Debug.Print statements to output to the Immediate Window, for example this line placed after the Set resetButton line:

    Code
    Debug.Print resetButton.outerHTML


    Do you mean after that line or on that line? If the error occurs on that line it means the initEvent method is not supported which I don't understand if you're using IE9.

    Re: Click a IE9 Web Page button using VBA


    If there are 2 instances of the class "t13button" then it's either (0) or (1). To make sure you are referencing the correct instance, after the Set resetButton line has executed type the following command in the VBA Immediate Window:


    ?resetButton.outerhtml


    and press Enter. (Press Ctrl+G in the VBA editor to display the Immediate Window.) If the code is referencing the correct element then you should see this displayed below the command:


    HTML
    <input type="button" onclick="javascript:apex.submit('RESET');" value="Reset Quick Search" class="t13Button" />


    Sometimes the simple Click event isn't enough and dispatchEvent must be used instead. Try this code, which replaces my previous code:


    Code
    Dim evtClick As Object
        Dim resetButton As HTMLInputElement
    
    
        Set evtClick = IE.document.createEvent("HTMLEvents")
        evtClick.initEvent "click", True, False
        Set resetButton = IE.document.getElementsByClassName("t13Button")(0)   'or (1)
        resetButton.dispatchEvent evtClick

    Re: Click a IE9 Web Page button using VBA


    Quote from dejamls;794588

    I'm getting a compile error with the reset button code added: (This line - Dim resetButton As HTMLInputElement) "user-defined type not defined".

    Set (tick) the reference to Microsoft HTML Object Library, via Tools->References in the VBA editor.

    Re: Click a IE9 Web Page button using VBA


    Try:

    Code
    Dim resetButton As HTMLInputElement
        Set resetButton = IE.document.getElementsByClassName("t13Button")(0)
        resetButton.Click

    where IE is the InternetExplorer object loaded with the webpage. The (0) assumes the button is the first element with class name "t13button". If not, try (1), (2), etc.

    Re: Click link in java website


    It's difficult to help without seeing the webpage itself. This code might work - it finds the link and then calls dispatchEvent to click it. Assumes you have referenced the MS HTML Object Library and IE is the InternetExplorer (version 9 or higher) object variable loaded with the page.

    For IE8, use this instead of HTMLevent and dispatchEvent:

    Code
    link.fireEvent "click"

    Re: Macro that lists, hyperlinks and lookup specific data from each sheet


    You just need a simple cell reference formula in each C column cell, for example in C2 ='Task1'!B2. Without seeing how your code is looping through the sheets and creating the hyperlinks I can't post the precise code, but it should be something like this:

    Code
    Worksheets("Summary").Cells(r, "C").Formula = "='" & sheetName & "'!B2"

    where r is the row number within the loop and sheetName is the name of the current sheet in the loop, Task1 or Task2, etc.

    Re: Use VBA to copy &amp; paste image in original size over data point in a bar chart


    I'm using Excel 2016. The PictureType property is available in Excel 2013 and higher. I tried my code without the PictureType line and it works just the same, without any distortion of the image. That line isn't needed because I set the data series fill to 'Stack' at design time.


    I don't why you're getting image distortion because the code simply duplicates the image without changing its size. You could try experimenting manually and see if the image distorts when you place it on top of a rectangle shape.

    Re: Use VBA to copy &amp; paste image in original size over data point in a bar chart


    See if this workaround works for you.


    It creates a temporary rectangle shape and a duplicate of the image on the images sheet. For each single data point in the bar chart, the rectangle is sized to the width and height of the data point, the duplicate image is positioned in the middle of the rectangle, the two shapes are grouped together and the group is copied and pasted to the data point.


    I have modified my working code to suit your situation based on your code (the Set statements near the top of the code), so if it doesn't work it is likely that I've got that part slightly wrong.


    Re: Find correct tab in Internet Explorer and activate it


    Try this code, which uses the CUIAutomation class to activate the required IE tab. This works on IE9 and higher.


    You must set the references noted at the top of the code: Tools -> References in the VBA editor.


    Re: Parsing RS232/Serial/COM port output using modCOMM


    Debug.Print statements output to the Immediate Window in the VBA editor. You can display the Immediate Window via the View -> Immediate Window menu option. It is also a good idea to display the Locals window (View -> Locals Window) in order to examine the values of variables whilst stepping through the code (F8 key) in the VBA debugger.


    For running on 64-bit Windows I think you need to change all the Windows API function declarations in the CLRS232 class module to include the "PtrSafe" keyword. For example, change:


    Code
    Private Declare Function BuildCommDCB Lib "kernel32" Alias "BuildCommDCBA" _
        (ByVal lpDef As String, lpDCB As DCB) As Long


    to:

    Code
    Private Declare PtrSafe Function BuildCommDCB Lib "kernel32" Alias "BuildCommDCBA" _
        (ByVal lpDef As String, lpDCB As DCB) As Long


    To allow the code to run on both 32-bit and 64-bit Windows you can provide both declarations by using the #If VBA7 directive like this:

    Code
    #If VBA7 Then
        Private Declare PtrSafe Function BuildCommDCB Lib "kernel32" Alias "BuildCommDCBA" _
            (ByVal lpDef As String, lpDCB As DCB) As Long
    #Else
        Private Declare Function BuildCommDCB Lib "kernel32" Alias "BuildCommDCBA" _
            (ByVal lpDef As String, lpDCB As DCB) As Long
    #End If

    Re: Vba code to get image title from Div class.


    Try this: