VBA command Workbooks.open no longer works

[David89A]

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=...

Any ideas would be gratefully received.

Thanks.

David

[#2]

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=...

Do you always want to download the last 365 days?

[#3]

Seems to work just fine for me. Have you tried Data, Get Data, From Workbook, From Text/CSV?

[#4]

Works fine with data import @David89A 

[#5]

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.

[#6 · Accepted solution]

@David89A 

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) 

[#7]

Dear @PascalKTeam ,

I see.  My Excel for Mac ribbon offers far fewer options than yours in the videos you sent.  However, I'm sure there must be a way to do it.  I'll have a bit of web-browse and see if I can find it.

I noticed I'd missed a question you asked earlier; namely about whether it has ever worked in 2020.  Yes, it worked fine last week on the 4th of January and accurately downloaded data covering the year end.  Something has changed since then: I know it's not my macro but I can't find out what it is.

Your suggestion about the automatic update looks like a better solution to the problem, however, so I'll pursue that.  Thank you again.

[#8]

You might try this then, I expect that is available to MAC Excel: https://jkp-ads.com/articles/importtext.asp

[#9]

Dear @Jan Karel Pieterse ,

Thanks for the link.  Unfortunately, I can't get it to work for me for the same old reason - the icons on the ribbon are missing from my Excel version (for Mac).  Also, this article appears to cover imports from an existing file, rather than a web page.  If you find anything on how to import from a website's database into Excel 365 for Mac, then I'd be grateful if you'd let me know.

Thanks for your help so far.

[#10]

@David89A If you open the attached, are you able to refresh the data?

[#11]

Dear @Jan Karel Pieterse ,

I opened the attachment you sent.  It opened as an Excel spreadsheet with the six column headings but no data.  It also came with a "Security Warning" which said, "External Data Connections have been disabled" and a button labelled "Enable Content".  I clicked the button but nothing changed on the spreadsheet.

Does this help to diagnose the original problem?

[#12]

@Jan Karel Pieterse 

I clicked the "Refresh All" icon in my Data ribbon and received the message in the attached.  I don't understand it.  Can you decipher it?

[#13]

Apparently this doesn't work yet. Excel on Mac is supposed to be able to refresh PowerQuery queries (this is one), but apparently fails for some reason.

[#14]

Dear @Jan Karel Pieterse 

Thanks for your info.  I suppose we'll just have to wait until Microsoft gets around to repairing its software.