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 -

http://www.windmill.co.uk/excel/excelweb.html

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)

http://datatoolbar.com/Import-…-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 ]

Sub Iterate_Folders()
Dim ctr As Integer
ctr = 1
Path = "C:\StuffTest"
FirstDir = Dir(Path, vbDirectory)
Do Until FirstDir = ""
If (GetAttr(Path & FirstDir) And vbDirectory) = vbDirectory Then
ActiveSheet.Cells(ctr, 1).Value = Path & FirstDir
ctr = ctr + 1
End If
FirstDir = Dir()
Loop
End Sub

Results - pulling out filename, modified date, difference between modified date and previous business date as double , and previous business date as double.
[ATTACH=CONFIG]69665[/ATTACH]

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.

Sub FMT_LghtCells(Rc As Integer, Gc As Integer, Bc As Integer)
    Dim regex As Object, tArr() As Long, i As Long, x, y As Long, r As Range, z As Range
    Application.ScreenUpdating = False
    x = Selection.Address(ReferenceStyle:=xlR1C1)
    With ActiveSheet
        Set r = Selection
        Set regex = CreateObject("VBScript.RegExp")
        With regex
            .Global = True
            .Pattern = "[^\d]?R\d*C"
            x = Split(regex.Replace(x, "|"), "|")
        End With
        ReDim tArr(UBound(x))
        For i = LBound(x) + 1 To UBound(x)
            tArr(i) = CLng(x(i))
        Next i
        y = WorksheetFunction.Max(tArr)
        For Each z In r.Rows.Areas
If Bc < 255 Then
        .Range(.Cells(z.Row, 1), .Cells(z.Offset(z.Rows.Count - 1, 0).Row, y)).Interior.Pattern = xlGray50
        .Range(.Cells(z.Row, 1), .Cells(z.Offset(z.Rows.Count - 1, 0).Row, y)).Interior.PatternColor = RGB(Rc, Gc, Bc)
End If
If Bc = 350 Then
        .Range(.Cells(z.Row, 1), .Cells(z.Offset(z.Rows.Count - 1, 0).Row, y)).Interior.Pattern = xlSolid
        .Range(.Cells(z.Row, 1), .Cells(z.Offset(z.Rows.Count - 1, 0).Row, y)).Interior.PatternTintAndShade = 0
End If




        Next z
    End With
    Application.ScreenUpdating = True
End Sub








Sub Do_Green(control As IRibbonControl)


Call FMT_LghtCells(219, 252, 173)


End Sub




Sub Do_Blue(control As IRibbonControl)


Call FMT_LghtCells(149, 235, 253)


End Sub




Sub Do_Red(control As IRibbonControl)


Call FMT_LghtCells(255, 170, 150)


End Sub








Sub Do_Clr(control As IRibbonControl)




'
'TRIGGER CLEAR METHOD WITH B AT 350
'
Call FMT_LghtCells(255, 170, 350)
    
End Sub

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.

Sub Test_B(control As IRibbonControl)
'
' RESET DATE
'
Dim NonCogRange As Range
Dim c As Range
Dim CellCount As Range
Dim ColCnt, RowCnt As Integer
Dim StrRow As String




Set CellCount = Selection




For Each NonCogRange In CellCount.Areas
    For Each c In NonCogRange.Cells




    
    c.FormulaR1C1 = "Row # :" & c.Row
    
    If c.Row <> CellCountR Then
    CellCountR = c.Row
    StrRow = StrRow & "," & CellCountR
    End If
           
    Next c
Next NonCogRange




Range("a1").FormulaR1C1 = "Rows(" & Right(StrRow, Len(StrRow) - 1) & ")"




End Sub

[ATTACH=CONFIG]69477[/ATTACH]
[ATTACH=CONFIG]69476[/ATTACH]

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.

FindAddressColumn("A1:Z1","TeamIronman")

Function FindAddressColumn(FindAddress As Range, FindString As String) As Range
  Dim RngAddress As Range
  Set RngAddress = FindAddress.Find(FindString)
  If RngAddress Is Nothing Then
    'MsgBox "Address column was not found."
  Set FindAddressColumn = Range("A1")
    Exit Function
  End If
 
 Set FindAddressColumn = RngAddress
  'FindAddressColumn = Columns(1, RngAddress.Column)
 
 
End Function

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