Forum Discussion
VBA command Workbooks.open no longer works
I have an Excel spreadsheet which contains macros, three of which access files on the digitallook website. I run the macros every weekend. They have worked well for years but, last weekend, Excel started to tell me it couldn't access the file. However, if I paste the filename manually into my browser (Safari), the file opens as normal.
Is there some setting I need to change to get my macros working again?
The offending line in the macro is:
Workbooks.Open FileName:=strFilename
where strFilename contains the string:
http://www.digitallook.com/cgi-bin/dlmedia/price_download.cgi/download.csv?start_day=13&start_month=01&start_year=2019&end_day=13&end_month=01&end_year=2020&type=html&action=download&csi=50032
Any ideas would be gratefully received.
Thanks.
David
The thing is that if you use data import from web, you don't need the VBA to load the data anymore. you would simply refresh the data you load....
I don't know Excel for Mac but maybe you can find the option like this (see video)
13 Replies
Works fine with data import David89A
Hello PascalKTeam ,
Thanks for your quick response. You asked if it is necessary always to extract the last 365 days. In fact, I only need to cover the time since the last update which is usually a week. However, it isn't always a week (I often miss a week or two if I'm away from home) so I made it longer than I am ever likely to need. Also, making it one year means I only have to change the year part of the date which, unlike the month, is always less than the current date. The macro later works out which data I have already got and discards the rest. However, determining the optimum amount of data to extract isn't really the problem, it's getting the file to open in the first place.
Your second response is interesting. It would be relatively easy for me to reprogramme the macro to use Get Data instead of Open File. However, I couldn't get it to work on my computer because I don't have the "Data from web" icon in my ribbon. Is that because I'm using Excel 365 for Mac? If not, how do I find it?
Thanks again for your help.
The thing is that if you use data import from web, you don't need the VBA to load the data anymore. you would simply refresh the data you load....
I don't know Excel for Mac but maybe you can find the option like this (see video)
- Seems to work just fine for me. Have you tried Data, Get Data, From Workbook, From Text/CSV?
- Has it ever worked in 2020?
The URL contains the year, maybe it has something to do with that?
http://www.digitallook.com/cgi-bin/dlmedia/price_download.cgi/download.csv?start_day=13&start_month=01&start_year=2019&end_day=13&end_month=01&end_year=2020&type=html&action=download&csi=50032
Do you always want to download the last 365 days?
