VBA Wizz needed (Java script in VBA Macro?)

[Zonc]

Greetings!

I have been trying to find a solution to a (conceptually) simple problem, but the coding skills necessary for this particular problem are beyond me, so I was hoping someone here could help me out!

So here is what I am trying to do:

Ideally, I would like a macro (in Excel), which when operated, goes to this website:

http://www.transgaz.ro/en/masuratori...ale_iesire.php

And changes the "From" date to last week's date (as the website doesnt allow for periods longer than 7 days anyway). So for example, today is Wednesday, the "From" field should be last Thursday's date (not last Wednesday). The macro knows this automatically for every date in the week, so if today is Thursday 3rd July 2014, the "From" field will be set to Friday 27th June 2014, etc. Then the macro just downloads this file by selecting "Show Data" into the same directory as the Excel worksheet.

This has been really difficult for me to get my head around and I hope someone here can help me out with it! Thank you!

[Sean Thomas]

Hi,
let me get this right,
1.enter the earliest date possible (7 days before)
2.submit
3.then retrieve the data from the table in to excel

If this is correct, let me know and i will take a look later today for you.

[Zonc]

Hi Sean,

Exactly. Though the date technically has to be 6 days before (i.e. if today (to) is Monday, the (from) field has to be Sunday, not last Monday, that'd be 8 days). I'd really appreciate any input. Thank you!

[Sean Thomas]

Hi,
this should do what you want.
Click the button, it will wipe out existing data, open internet explorer, navigate to your web page, enter the start date, refresh to get all the data then it will pass the data back to excel. It is slow but there is a lot of data to process so be patient.

[Zonc]

That's great Sean! However, when I copied that macro into my own excel file (and changes the "Sheet1" to "Sheet12" in both places in the code, as I need), I run into a little problem.

I run the macro and the website launches correctly with the correct dates for the "From" and "To" inputs. However, Excel hangs (the IE browser does not) until I manually close the IE window (it doesn't close by itself anymore), after which I get the error:

"System Error &H80010108 (-2147417848). The object invoked has disconnected from its clients." So I click OK.

Then on the excel file, only roughly one third of the data is imported only for the very first day (i.e. today for the 27th of June). Very weird. Any idea where the error originates?

[Zonc]

Oh also, the website does correctly put out the data for whole week. So it seems that the error has something to do with the part where the data is pulled into excel?

[Zonc]

Update

It seems that the document you provided goes into a crawl for the importing process if my "main" workbook is open. The longer I let it run the more rows it has imported, but after 5 minutes or so it had only imported around 400 rows vs. all 700 if it's open by itself in 20 seconds.

Is it possible that there is an interaction between the two via the macro you wrote if they are both open at the same time, which causes the macro to check every page of the "main" workbook as well (it's a quite large, macro-enabled workbook) when importing the table from the website? I don't see how else the opening of both files could affect the speed at which the data is imported.

[Sean Thomas]

Hi,
As i said it does take some time. You have to wait until the web page is fully loaded before adding the new date. Then you have to wait for the new data to load, yesterday when i tried it there was over 6000 rows of data. Once fully loaded the macro then writes each cell of the table to the excel sheet, that's a lot of information to deal with. At this stage the ie is still open, if you shut it manually it will stall the macro. once the macro has processed all the info from the table it will then close ie.
There is nothing in my macro that will cause any issues with any other workbook open. Is there any formulas or events dependant on the sheet you are adding data to? that would really slow things down as it will try to recalculate everytime you add another cell of data.

As i dont have any idea what you are doing with the procedure i supplied & have not seen your other workbook there is no way of knowing what the problem is.

The code works well for the purpose you asked.

[Sean Thomas]

i just ran it again
6884 rows of data took 68 seconds, the majority of that was the time it takes for the webpage to load.
It can depend on your internet speed as well.

[Sean Thomas]

Hi,
heres another version that is 20 seconds quicker on my pc.
The main time is still taken up by the website loading its data.

Please Login or Register  to view this content.

[Zonc]

Thanks Sean, that's great. Your first macro was very good, it's something between the interaction of this importing measure and the fact that my primary workbook is open that seems to slow it down tremendously, and I have no idea what that is, as there is no referencing done between the two. The problem might arise as a result of me importing "from web" a different list from the same website on my primary workbook, which is itself a really slow process.

Namely it's some really ugly code that I have that aims to pull this data into excel http://www.transgaz.ro/en/transparenta_masuratori.php
What I have currently gives me numerous errors which I just ignore as the result is what I need, so perhaps all those errors are what is causing the process to be so slow because it's from the same website? Maybe some cleaner code to import what is also in the link above (just for the current day, what is on display by default) would really help me out, as I dont know how to import this table aside from the "From Web" option in Excel.

[Sean Thomas]

Glad it helps,
Again if you would like to post an example of how you want the data to show in excel i can do a similar web scrape. It should be quicker as less data to load. Do you need to change dates etc or is that page always todays date?

[Zonc]

Page is always most recent. The way it is displayed on the site is good. Having the data go back 30 days (i.e. From field to be 30 days ago) would be a best case scenario for me, but not critically necessary.

[Sean Thomas]

If you go back that far it goes onto more than one page. Do you usually use the export to excel facility or do you just take the first page?

[Zonc]

I take the first page, I wouldn't know how to make the "Export to Excel" function use anything besides the default dates.

[Sean Thomas]

This should get the data from that page

Please Login or Register  to view this content.

[Zonc]

I rebuilt my entier worksheet around your macros and using some of my old macros, and there seems to be no issue now. Thank you so much for all your help, I very much appreciate it!

[Zonc]

Just figured out what the problem causing the slowdown was.

The list had several VLOOKUP functions attached to it, removing these functions stopped the problem. I assume for every new data that was being pulled into excel, another entire VLOOKUP calculation was happening for every VLOOKUP function aimed at that table, so for each new cell, the system resources required increased exponentially.