I have an excel workbook that I am currently using to scrape large amount of data from web on a daily basis and my friend has his own excel workbook in his computer which he uses as an analyzing tool to analyze the data that I am importing from the web and I am trying to figure out the best way to share my data with him on a daily basis (all automated).
I have come up with the following two ideas and I would like to see how other people think of these two approaches to go about it.
Approach 1: Once my workbook scrapes data from the web, I will use either an Add-in tool or a web service to convert the data into an API file then share this file with my friend via an icloud solution (i.e. one drive). Then I will write a VBA code on my friend's workbook which will automatically (on a set time of a day) download the file from the one drive then update the data to his workbook. Not even sure if this is 100% achievable via VBA though. Found few codes online that seems to do the job but haven't tried it yet.
Approach 2: I can use a google spreadsheet. Once my workbook scrapes all the data from the web, it will push the data to the google spreadsheet (using VBA). And once this is updated, my friend's workbook will (of course with the VBA code) pull the data from the google sheet to his excel workbook (I guess once a day). In this case, I will need a code on my workbook and a separate code on his workbook.
What I am trying to achieve here is to find out if these are the best solutions out there or if anyone knows any better way to achieve what I am trying to achieve.
Excel online isn't the right solution for me as my file is bigger than 50MB which is beyond their maximum file size limit.
Any idea will be highly appreciated.
Thank you.
Jin
Bookmarks