VBA store data from file in web to matrix

A

Axtros

New Member
Joined
Aug 5, 2021
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hello,

I was trying to get data from the web OMIE. The idea is to get data from all the files between to dates, the files are presented with the first line as a header and a character "*" as end of the current file. Data is separated by ";" and it normally has 26 lines (header, 24 lines of relevant data and the end character), but there are files with 25 or 27 (days where hour is change in this country). Also there are files in .zip, that comprise all the files of the days of a past year (2020,2019,2018...), and I do not know if it can be read as it is.

I do not have a lot of experience in this field of VB, and I try different things, but I have the problem that my code is very slow. When I want to get data for 1 month (30/31 files) it last almost 3 minutes! I will like to get it below 30 seconds if possible. These are the versions, that i manage to make work.

Version 1 (Download .zip and read files online):

VBA Code: 
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
    ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Private Sub HTML_VBA_Extract_Data_From_Website_To_Excel()
    Dim oXMLHTTP As Object
    Dim sPageHTML  As String
    Dim sURL As String
    Dim fechaInicio, fechaFin As Date
    srcpath = "https://www.omie.es/en/file-download?parents%5B0%5D=marginalpdbc&filename="
    dlpath = ThisWorkbook.Path & "\"
    fechaInicio = Range("B1")
    fechaFin = Range("B2")
    Diff = DateDiff("d", fechaInicio, fechaFin)
    diain = Format(fechaInicio, "dd")
    mesIn = Format(fechaInicio, "mm")
    añoIn = Format(fechaInicio, "yyyy")
    Dim matriz
    ReDim matriz(24 * (Diff + 3), 2)
    Do While añoIn < Format(Now(), "yyyy") And añoIn <= Format(fechaFin, "yyyy")
        Name = "marginalpdbc_" + añoIn + ".zip"
        txtsrc = srcpath + Name
        URLDownloadToFile 0, txtsrc, dlpath & Name, 0, 0
        fechaInicio = DateAdd("yyyy", 1, fechaInicio)
        diain = "01"
        mesIn = "01"
        añoIn = Format(fechaInicio, "yyyy")
    Loop
    f = 1
    Do While (fechaFin >= fechaInicio)
        Name = "marginalpdbc_" + añoIn + mesIn + diain + ".1"
        txtsrc = srcpath + Name
        Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
        oXMLHTTP.Open "GET", txtsrc, False
        oXMLHTTP.send
        sPageHTML = oXMLHTTP.responseText
        aux = Split(sPageHTML, ";")
        i = f
        For f = f To f + 23
            matriz(f - 1, 1) = aux(6 * (f - i) + 5)
            matriz(f - 1, 2) = aux(6 * (f - i) + 6)
            End If
        Next
        f = f + 1
        fechaInicio = DateAdd("d", 1, fechaInicio)
        diain = Format(fechaInicio, "dd")
        mesIn = Format(fechaInicio, "mm")
        añoIn = Format(fechaInicio, "yyyy")
    Loop
End Sub

Version 2 (Download .zip and files, then read files):
VBA Code: 
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
    ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Private Sub HTML_VBA_Extract_Data_From_Website_To_Excel()
    Dim oXMLHTTP As Object
    Dim sPageHTML  As String
    Dim sURL As String
    Dim fechaInicio, fechaFin As Date
    srcpath = "https://www.omie.es/en/file-download?parents%5B0%5D=marginalpdbc&filename="
    dlpath = ThisWorkbook.Path & "\"
    fechaInicio = Range("B1")
    fechaFin = Range("B2")
    Diff = DateDiff("d", fechaInicio, fechaFin)
    diain = Format(fechaInicio, "dd")
    mesIn = Format(fechaInicio, "mm")
    añoIn = Format(fechaInicio, "yyyy")
    Dim matriz
    ReDim matriz(24 * (Diff + 3), 2)
    Do While añoIn < Format(Now(), "yyyy") And añoIn <= Format(fechaFin, "yyyy")
        Name = "marginalpdbc_" + añoIn + ".zip"
        txtsrc = srcpath + Name
        URLDownloadToFile 0, txtsrc, dlpath & Name, 0, 0
        fechaInicio = DateAdd("yyyy", 1, fechaInicio)
        diain = "01"
        mesIn = "01"
        añoIn = Format(fechaInicio, "yyyy")
    Loop
    f = 1
    Do While (fechaFin >= fechaInicio)
        Name = "marginalpdbc_" + añoIn + mesIn + diain + ".1"
        txtsrc = srcpath + Name
        Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
        oXMLHTTP.Open "GET", txtsrc, False
        oXMLHTTP.send
        sPageHTML = oXMLHTTP.responseText
        aux = Split(sPageHTML, ";")
        i = f
        For f = f To f + 23
            matriz(f - 1, 1) = aux(6 * (f - i) + 5)
            matriz(f - 1, 2) = aux(6 * (f - i) + 6)
            End If
        Next
        f = f + 1
        fechaInicio = DateAdd("d", 1, fechaInicio)
        diain = Format(fechaInicio, "dd")
        mesIn = Format(fechaInicio, "mm")
        añoIn = Format(fechaInicio, "yyyy")
    Loop
End Sub

So, if somebody could help finding a better method or fixing my code I would be very grateful. Thanks for your time
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
I have copy twice the same code by mistake, this will be the true second version.
VBA Code: 
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
    ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Sub descargar2()
Dim fechaInicio, fechaFin As Date
srcpath = "https://www.omie.es/en/file-download?parents%5B0%5D=marginalpdbc&filename="
dlpath = ThisWorkbook.Path & "\"
fechaInicio = Range("B1")
fechaFin = Range("B2")
Diff = DateDiff("d", fechaInicio, fechaFin)
diain = Format(fechaInicio, "dd")
mesIn = Format(fechaInicio, "mm")
añoIn = Format(fechaInicio, "yyyy")

Dim cadena As String
ReDim matriz(24 * (Diff + 1))
Dim f As Integer

Do While añoIn < Format(Now(), "yyyy") And añoIn <= Format(fechaFin, "yyyy")
    Name = "marginalpdbc_" + añoIn + ".zip"
    txtsrc = srcpath + Name
    URLDownloadToFile 0, txtsrc, dlpath & Name, 0, 0
    fechaInicio = DateAdd("yyyy", 1, fechaInicio)
    diain = "01"
    mesIn = "01"
    añoIn = Format(fechaInicio, "yyyy")
Loop
f = 0
Do While (fechaFin >= fechaInicio)

        Name = "marginalpdbc_" + añoIn + mesIn + diain + ".1"
        txtsrc = srcpath + Name
        URLDownloadToFile 0, txtsrc, dlpath & Name, 0, 0
        archivo = dlpath & Name
        Open archivo For Input As #1
        Line Input #1, cadena
        Line Input #1, cadena
        While StrComp(cadena, "*") = 1
            matriz(f) = Split(cadena, ";", Len(cadena) - 1)
            f = f + 1
            Line Input #1, cadena
        Wend
        Close #1
        fechaInicio = DateAdd("d", 1, fechaInicio)
        diain = Format(fechaInicio, "dd")
        mesIn = Format(fechaInicio, "mm")
        añoIn = Format(fechaInicio, "yyyy")
Loop
End Sub
 
Upvote 0
Hi,​
​
your code is nothing as you forgot to mention an example of the content of your variables​
or just attaching at least both source and expected result worksheets​
so as we do not have any experience in guessing, mind reading, …​
 
Upvote 0
Marc L said:
Hi,​
​
your code is nothing as you forgot to mention an example of the content of your variables​
or just attaching at least both source and expected result worksheets​
so as we do not have any experience in guessing, mind reading, …​
Click to expand...

Thanks for your answer Marc L. It is the fist time I write in this type of forum, and I did not know very much, how to explain the topic, sorry. In my code I request for two dates via excel sheet. then i calculate how many days I need to store, to make the sizes of the matrix. Most of the variable (as diaIn,mesIn,añoIn...) is only for storing the day, month and year to create de filename as you can find in the hyperlink I provide in my first post.
So, example:
date 1: 03/08/2021
date 2: 05/08/2021

matrix size (diff(date 1, date 2)+1)
and the create the filename as said, make the request of each individual file. scrpath is where the individual files are store in that webside. I have explain the source files in the first post. the data on it are 6 colums containing, year, month, day, hour, and 2 Prices.
Example:

MARGINALPDBC;
2021;08;05;1;110.71;110.71;
2021;08;05;2;107.75;107.75;
2021;08;05;3;105.27;105.27;
2021;08;05;4;103.63;103.63;
2021;08;05;5;102.85;102.85;
2021;08;05;6;104;104;
2021;08;05;7;106.37;106.37;
2021;08;05;8;107.12;107.12;
2021;08;05;9;112.66;112.66;
2021;08;05;10;112.1;112.1;
2021;08;05;11;108.14;108.14;
2021;08;05;12;102.85;102.85;
2021;08;05;13;101;101;
2021;08;05;14;97.8;97.8;
2021;08;05;15;83;83;
2021;08;05;16;77.95;77.95;
2021;08;05;17;77.75;77.75;
2021;08;05;18;77.95;77.95;
2021;08;05;19;85;85;
2021;08;05;20;100.5;100.5;
2021;08;05;21;111.91;111.91;
2021;08;05;22;115.27;115.27;
2021;08;05;23;115.02;115.02;
2021;08;05;24;111.91;111.91;
*

And the final output is to store in the matrix in colums: 1) Date 2) Hour 3) Price 1) Price 2 . Example

(data since date 1)
04/08/2021 23 109 109
05/08/2021 1 110.71 110.71
05/08/2021 2 107.75 107.75
05/08/2021 3 105.27 105.27
(data until date 2)
My problem is not in how to read the data from file, It is to make it in a different way to acess to infomation more quickly. Is it well explained now? Do I need to send more information for asking for help? I will be waiting for your response :)
 
Upvote 0
​
For some reason after the download the source zip file appears to be 'damaged' on my side so I can't help further …​
 
Upvote 0
Marc L said:
​
For some reason after the download the source zip file appears to be 'damaged' on my side so I can't help further …​
Click to expand...
Oh, so strange. In my case, the file has no problem, and i can unzip it without problem. I tried in other computers, to see if it was a common problem, and it work as well. So thank you for trying, I do not know what could be happening for damaging the file in your case.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Go through subfolders, select matching files and copy a worksheet from each into a master file
2
Replies
17
Views
944
BatTodor
B
M
  • Question Question
Data Transfer from Excel to PowerPoint and getting PDF file of PPT
Replies
1
Views
126
Sequoyah
Sequoyah
I
  • Question Question
Modify VBA script to count rows with data in multiple sheets
Replies
0
Views
419
irishjsk
I
Y
  • Solved
Wrong Copy Data from Several Excel file
Replies
8
Views
348
yamixas
Y
J
  • Question Question
Open last modified file from relative path
Replies
2
Views
572
Josu
J

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top