Multiple data import from text files

[Nils_Junker]

Hi everybody,

I got the following problem:

I need to get data from round about 500 text files.
Problem one is that I just need a part of each text file, (the part I need always stays the same)
Problem two is that I dont have enough time to manually convert all the text files one by one.

The following link shows all the files I want to include in my excel

Index of /climate_environment/CDC/observations_germany/climate/10_minutes/air_temperature/recent/

Also how many lines can i insert in Excel?

If you need further Information, please send me an info!

Thanks for your help!

 

[Herakles]

Add this first line of code where indicated and then run the code selecting one file to download.

This should tell you where and what it is trying to save.

        MsgBox strDownloadFolder & "\" & strFilename, vbOKOnly, "Error Line"
    
        If objXmlHttpReq.Status = 200 Then
            Set objStream = CreateObject("ADODB.Stream")
            objStream.Open
            objStream.Type = 1
            objStream.Write objXmlHttpReq.responseBody
            objStream.SaveToFile strDownloadFolder & "\" & strFilename, 2
            objStream.Close
        End If

 

[Nils_Junker]

Add this first line of code where indicated and then run the code selecting one file to download.

This should tell you where and what it is trying to save.

        MsgBox strDownloadFolder & "\" & strFilename, vbOKOnly, "Error Line"
  
        If objXmlHttpReq.Status = 200 Then
            Set objStream = CreateObject("ADODB.Stream")
            objStream.Open
            objStream.Type = 1
            objStream.Write objXmlHttpReq.responseBody
            objStream.SaveToFile strDownloadFolder & "\" & strFilename, 2
            objStream.Close
        End If

this is the message i am now getting

 

[Herakles]

View attachment 93850
this is the message i am now getting

What value have you got assigned to the strDownloadFolder variable as the folder into which the downloaded
zip file is to be saved?

This is what it should be:
strDownloadFolder= ThisWorkbook.Path & "\Downloads\"

You should have a 'Downloads' folder in the folder containing the workbook.
You should have an 'Unzipped' folder in the 'Downloads' folder.

 

[Nils_Junker]

What value have you got assigned to the strDownloadFolder variable as the folder into which the downloaded
zip file is to be saved?

This is what it should be:
strDownloadFolder= ThisWorkbook.Path & "\Downloads\"

You should have a 'Downloads' folder in the folder containing the workbook.
You should have an 'Unzipped' folder in the 'Downloads' folder.

My folder organisation looks like this:

where can i see what value there is assigned (at which line, can't find it)

this here:

 

[Herakles]

My folder organisation looks like this:

View attachment 93853

where can i see what value there is assigned (at which line, can't find it)

Search for this

strDownloadFolder=

 

[Nils_Junker]

Search for this

strDownloadFolder=

did that, it is exactly like you mentioned, see the answer before this post

 

[Herakles]

Does this appear anywhere else in the module?

strDownloadFolder=

What drive are you on?

I'm on my C drive.

 

[Nils_Junker]

Does this appear anywhere else in the module?

strDownloadFolder=

What drive are you on?

I'm on my C drive.

so i converted my workbook folder also to the c drive.
now it looks like this

and no there is no other line containing strDownloadFolder =

 

[Herakles]

so i converted my workbook folder also to the c drive.
now it looks like this
View attachment 93860

and no there is no other line containing strDownloadFolder =

So has this file downloaded and extracted?

 

[Nils_Junker]

So has this file downloaded and extracted?

yes in the folder downloads -> unzipped there is now the textfile