How to import excel sheet automatically from a link into existing file

[dikken20]

Hi,

I've got an excel file with an existing sheets, Let's call this main file as "TASE.xlsx"
There's this specific link (URL specified below), pressing on it will download an other excel file. I would like to have this new file from that link to be imported/inserted into a specific sheet of the main excel file (for simplicity let's say "sheet2" in specific. Meaning, remove all existing data from "sheet2" and import the data from the file from the link).
The data in that file from the link changed all the time and it has a different file name every day.

My purpose is to import this file every 1 minute from 9:30 to 18:00 and deal with its data by other macros.

How do I import that file from the link into the existing main excel file ("sheet2" of "TASE.xlsx")?

Thank you.

URL: https://www.tase.co.il/_layouts/Tase/ManagementPages/ExcelExport.aspx?sn=none&GridId=94&AddCol=1&Lang=he-IL&CurGuid={D3BCD81A-8C9F-4D16-848A-FF76429D70E7}&action=1&RegS=0&SubAction=0&ExportType=1

 

[dougmarkham]

Hi,

I've got an excel file with an existing sheets, Let's call this main file as "TASE.xlsx"
There's this specific link (URL specified below), pressing on it will download an other excel file. I would like to have this new file from that link to be imported/inserted into a specific sheet of the main excel file (for simplicity let's say "sheet2" in specific. Meaning, remove all existing data from "sheet2" and import the data from the file from the link).
The data in that file from the link changed all the time and it has a different file name every day.

My purpose is to import this file every 1 minute from 9:30 to 18:00 and deal with its data by other macros.

How do I import that file from the link into the existing main excel file ("sheet2" of "TASE.xlsx")?

Thank you.

URL: https://www.tase.co.il/_layouts/Tase/ManagementPages/ExcelExport.aspx?sn=none&GridId=94&AddCol=1&Lang=he-IL&CurGuid={D3BCD81A-8C9F-4D16-848A-FF76429D70E7}&action=1&RegS=0&SubAction=0&ExportType=1

Hi Dikken20,

I have a bit of VBA you can use to put a file within a folder. Presumably, you will be downloading that excel file into Downloads (if you're working on a windows PC). If so, you can put this vba code into a new module and change the location to C:\Users\Username\Downloads. So long as Downloads is empty of other excel files, this will work.

Sub ImportExcelFile()
Path = "C:\Users\Username\Downloads\"
Filename = Dir(Path & "")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close False
     Filename = Dir()
  Loop
End Sub

If not, you can SaveAs the file to an empty folder of your choice of course.

Kind regards,

Doug.

 

[dikken20]

Hi Dikken20,

I have a bit of VBA you can use to put a file within a folder. Presumably, you will be downloading that excel file into Downloads (if you're working on a windows PC). If so, you can put this vba code into a new module and change the location to C:\Users\Username\Downloads. So long as Downloads is empty of other excel files, this will work.

Sub ImportExcelFile()
Path = "C:\Users\Username\Downloads\"
Filename = Dir(Path & "")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close False
     Filename = Dir()
  Loop
End Sub

If not, you can SaveAs the file to an empty folder of your choice of course.

Kind regards,

Doug.

Thank you Doug, this could be handy somehow.

However, I'm looking for more automatic solution, a way to download the file automatically and show the data in the main "TASE.xlsx" file's "sheet2" every 1 minute.
I did tried to use somehow the "import from web" option but for some reason it keep asking me for a login when it tries to retrieve the data. Am not sure why as this can be opened without any registration to the website that the file located in, so I'm not sure if that solution is the answer as well I'm afraid.

Any other solutions?

 

[dougmarkham]

a way to download the file automatically and show the data in the main "TASE.xlsx" file's "sheet2" every 1 minute.
I did tried to use somehow the "import from web" option but for some reason it keep asking me for a login when it tries to retrieve the data.
Any other solutions?

Not sure at all how you would do such a thing.

With regard to exporting data from websites, there are data exporters that would probably need to utilize the website API to conduct such a regular export. A web-programmer would be able to do such a thing for you. If there is a cheap workaround (perhaps using SQL), I don't know it sorry.

Kind regards,

Doug.

 

[dougmarkham]

Hi,

I've got an excel file with an existing sheets, Let's call this main file as "TASE.xlsx"
There's this specific link (URL specified below), pressing on it will download an other excel file. I would like to have this new file from that link to be imported/inserted into a specific sheet of the main excel file (for simplicity let's say "sheet2" in specific. Meaning, remove all existing data from "sheet2" and import the data from the file from the link).
The data in that file from the link changed all the time and it has a different file name every day.

My purpose is to import this file every 1 minute from 9:30 to 18:00 and deal with its data by other macros.

How do I import that file from the link into the existing main excel file ("sheet2" of "TASE.xlsx")?

Thank you.

URL: https://www.tase.co.il/_layouts/Tase/ManagementPages/ExcelExport.aspx?sn=none&GridId=94&AddCol=1&Lang=he-IL&CurGuid={D3BCD81A-8C9F-4D16-848A-FF76429D70E7}&action=1&RegS=0&SubAction=0&ExportType=1

Found this which may help for part of the job: https://www.quora.com/How-do-I-auto...rom-a-website-into-excel-at-regular-intervals

 

[dikken20]

Found this which may help for part of the job: https://www.quora.com/How-do-I-auto...rom-a-website-into-excel-at-regular-intervals

Thanks Doug, appreciate your effort to help me out on this one!

When trying to use the "Import from the web" option it is asking for [FONT=&quot]user credentials while there isn't any as this data on that website is open for everyone. So I tried to do the same thing on another Laptop and it succeed importing a data without this [/FONT][FONT=&quot]user credentials thing. Yet, the importing/updating data takes too much time and too much CPU as the data is pretty big. So even if it could be a good solution for my needs.. well.. it isn't due to the technical problem as I need this updated every 1 minute top while CPU remains in normal scale.

So, I think that downloading the file and import it to a second sheet would be the direction for the wanted solution.

Question is how to do that... Anyone ?[/FONT]