autofilter

[alex_peng]

Hello, I am new to this site and I have a question regarding autofilter. Any help would be appreciated.

I have a table which I have imported from a csv file on a website. "http://reports.ieso.ca/public/PriceHOEPPredispOR/PUB_PriceHOEPPredispOR_2020.csv"

I use table to text and It comes in fine as a table. DATE(YYYY-MM-DD) I
(Date'Hour'Price) Header -
I deleted some rows and columns that were not needed and redefined the date cell.
.Columns("A:B").ColumnWidth = 12
.Range("d:i").EntireColumn.Delete
.Range("1:3").EntireRow.Delete
.Range("A500000").NumberFormat = "yyy-mm-dd"

I am only looking for data for the current month (Day 1-31)

am using the following criteria

With ActiveSheet
.Range("a1").AutoFilter Field:=1, _
Criteria1:=XlDynamicFilterCriteria.xlFilterThisMonth, _
Operator:=xlFilterDynamic
End With

When I apply this filter it appears to work however it appears that all the row heights have been set to 0. Due to this fact, I cant use the "xltypevisisble" to copy because all the rows are equal to 0. If I try to change row height manually in the worksheet or in vba it does nothing. However, I can stretch each row one by one to reveal the filtered data.
I went into excel and selected the autofilter and it brings up option boxes for each day. If I select each box for the month it works fine.
Hopefully someone can decipher my gibberish. Any help would be appreciated.
Thank you.
Alex

 

[alex_peng]

do you know what would cause my "return to excel" button in my vba to be grayed out?

 

[Logit]

No.

Are you speaking of the X in the upper right corner ?

 

[Logit]

The workbook here is failing to filter for this month's dates also.

I am at a loss why this is occurring.

Sorry.

 

[alex_peng]

I just realized that they dont publish this months till the end of the month,, so it should actually be last month on the filter? Another angle is can you just download the excel to a file then do a local import of the file. This might solve the connection issue. When I went and download the report directly, it gave me a proxy error. Not sure if there is some sort of retry timer for the document to be downloaded.

 

[Logit]

.
The following works here without issue. You can repeatedly download the .CSV file as many times as you desire ... no errors here.

However ... you cannot delete the data from SHEET 2 and then run the macro. It will error out. You also cannot change, edit or
move any of the existing data from SHEET 1. The data must remain in the cell locations as is.

I tried adding more to the macro to auto process the data on SHEET 2 after download but that creates errors.

So, if you are willing to use the workbook to download your data file, then manually process the data .... ???

Option Explicit

Sub ImportFromWeb()
Dim FullName As String, ImpName As String, DataRange As Variant, NumRows As Long, NumCols As Long

FullName = Range("fullname").Value

    Workbooks.Open Filename:=FullName
    DataRange = ActiveCell.CurrentRegion.Value
    NumRows = UBound(DataRange)
    NumCols = UBound(DataRange, 2)
    ImpName = Application.ActiveWorkbook.Name
    Workbooks(ImpName).Close SaveChanges:=False
      
    With Range("importrange")
    .ClearContents
    .Resize(NumRows, NumCols).Name = "importrange"
    End With
    
    Range("importrange").Value = DataRange
    
End Sub

Download workbook : web-download1.xlsb

 

[alex_peng]

thanks for all your efforts. The application really needs to run as a front end type application. Pushbuttons from one form do a bunch of things. I have a couple of other things that happen as well locally. This app is to be used by people who have very limited knowledge of Excel. Just menu driven with some options. Its nice to not even show anything in the background just forms for navigation and pb's.

The idea is just to compile and run as standalone. On opening of the workbook it displays a form. I can send you my base file with all the forms. If you need coverage for your time I totally understand that. Let me know either way we can figure something out.
I figured out the grayed out button in vba.If I am on my third monitor it shows grayed out, the moment I drag vba over to either of my other monitors it shows green.....must be some funky burial ground under my office.

 

[Logit]

" must be some funky burial ground under my office. "

I don't care who you are ... now THAT's funny !

It's also a Microsoft amenity that comes free with every copy. Ha !

Sent you a private message with my email where you can send the file. I'm certain you don't want to share it with the world.

 

[alex_peng]

Thanks for all your time and efforts Logit. It was definitely invaluable. Application works perfect.
Cheers,
Alex

 

[Logit]

You are welcome.

 

[alex_peng]

Got a quick question. I have another report that needs to be downloaded from the same site as the HOEP. What do you think the easiest way to duplicated the code with making a mess of the macros? How are the web addresses tied to the macro for webimport where does the string fullname come from?