Posts by JanMantins

    Re: Importing data from a website widget

    SkipperC - this might be of use - if you can get (A) the framed 'widget' page to load without the 503 and (B) figure out where in the DOM of the page the selection resides -

    It doesn't help of course that the default browser excel is using is apparently I.E and not something more standards compliant like Edge.

    There's also this which I found - which may help ( 3rd party scraping tool , but might be worth giving it a shot)…-web-page-into-Excel.html

    Re: Importing data from a website widget

    There can be, especially sites that use javascript canvas - as it's text being posted to an image - excel's data tool is effectively blind to it.

    As a rule of thumb, if it's something you can't select with your cursor in the browser - the chances are excel won't be able to get at it either.

    Re: Importing data from a website widget

    The Iframe appears to be using javascript canvas and if you view frame source / open the frame page in a new window , it's locked down (503 forbidden) - so it might be worth looking into other options where they're still using tables.

    Re: Open file where modified date prior to current day and latest time that day?

    Thanks for the replies guys.

    I guess I was just trying to do it as cleanly as possible without having to create items on a worksheet.

    What I did was extract it all to a hidden sheet and use a maxifs on the difference, then an index/match on that value to return the filename for that figure.

    We're all on Excel 365 ( v16 ) so should be okay. :)

    Here's a list of what I've pull out using a modified version of the 3rd example on this page [ link ]

    Results - pulling out filename, modified date, difference between modified date and previous business date as double , and previous business date as double.

    Is there a smarter way to select only the most recent file where the modified date is NOT today, but the MAX date from that range? ( highlighted greeny-yellow in example)

    I've tried loading the dates into an array and sorting, and casting both the modified date and previous business dates as doubles to find the difference , but I think I'm probably doing it in the most long winded way.

    I may be going about this in a more complicated than necessary fashion - since implementing a scripted save, I am saving the date and time within the filename, so I *could* use that instead - it's just trying to get a reliable logic where it'll still capture the most recent reports ( run for prior 2 business days or more ) if the user had forgotten to run it for a week/month.

    Re: Return ROW(S) only selection from non-contiguous range using Areas.

    That's some next level stuff with the RegEx!

    You also answered my next question - which was going to be how to set the rightmost boundary of those lines ( the terminating column ) by picking the furthermost right cell from the various selected areas. :)

    In the interests of sharing. I added in variables for colour - so if anyone wants to do the same thing and finds this thread - it may be of some use.

    I added four buttons to add three colours, and a fourth to clear the colours - highlights are done via pattern 50% grey - so don't interfere with the existing formatting of the sheet.

    Thanks again skywriter. :)

    I think I'm half way there, I've found various rather complex solutions while googling but I'm pretty sure there's an easier way than what I'm doing.

    As shown in the screenshots, I'd like the user to select rows by picking cells, then the macro to return from those selections a series of rows, and colour them.

    I've got as far as getting a string with the row numbers in, but I can't find a good example of the syntax to reference this in a row selection.


    Re: Function to find text in header - and return column range for selection?

    That's marvellous , thankyou! :) The think that was throwing me was how to get out the entire column output as a reference! You're a life saver!

    The plan is to call it from within a VBA subroutine - I've several sheets that feature specific columns, but often are saved in different ordering - so need to be re-ordered before pulling into a larger sheet.

    Hello all! I have a lot of column re-ordering to do on multiple sheets, and rather than repeat code, I thought I'd have a try at a UDF, to select columns based on a string , and then allow me to select that range.

    Having trawled old posts etc, I found a sub I wanted to modify into a function, however it's returning the value of the header cell, instead of the range.

    The ideal output of this would be to return Range("A:A") - if the 'TeamIronman' string was on A1.


    The commented part is the bit that failed. Any pointers would be most welcome!