Forum Discussion

David89A's avatar
David89A
Copper Contributor
Jan 13, 2020
Solved

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 s...
excel
Excel on Mac
Macros and VBA
office 365
  • PascalKTeam's avatar
    PascalKTeam
    Jan 13, 2020

    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) 

  • David89A's avatar
    David89A
    Copper Contributor
    Jan 13, 2020

    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.

     

     

     

    • PascalKTeam's avatar
      PascalKTeam
      Iron Contributor
      Jan 13, 2020

      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) 

      • David89A's avatar
        David89A
        Copper Contributor
        Jan 13, 2020

        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.

Resources