Welcome to the new Box Support website. Check out all the details here on what’s changed.

Box and Excel VBA

New post

Comments

16 comments

  • Murtza

    I am not sure if this is possible. Another option would be opening a Box file in Excel using the Box web app.

     

     

    0
    Comment actions Permalink
  • Arok

    hello murtza i know this option but i am trying to create a tool that is editing other file on the box automatically , so i am trying to use both excel macro and box.

    Unluckily no solution has been provided yet .

    0
    Comment actions Permalink
  • Box Product Support

    I have a macro excel file. If I save this file on box.net and open it from box.net it does not run the macro. Is there a way on box.net to allow macros to run?

    0
    Comment actions Permalink
  • LyNeaTomas

    Have you received feedback in regards to this question?

    0
    Comment actions Permalink
  • Box Product Support

    I also would like updating a file stored at BOX through a VBscript/Excel VBA or any other script.

    Is it possible?

    0
    Comment actions Permalink
  • hhggzz

    Same question - I would like to navigate a folder, find the most recent file and open it in excel.  I've started to look at the api documentation and it looks like it will take me a while to figure out.  This is especially true since all the code examples are in .net and other languages.  Anyone with a working vba program for this?

    0
    Comment actions Permalink
  • JSP_JSP

    Hi,

     

    Any luck on this question?

    Do we have any VBA code to upload the files to box ?

     

    Regards

    JSP

    0
    Comment actions Permalink
  • bascobill

    We are new Box users. I have several VBA macros in an Excel workbook that select templates based on certain criteria. They currently use an absolute path on the file server. Is there a way I can get it to pick and open files on Box?

    Thanks.. Bill

    0
    Comment actions Permalink
  • Howard

    Hi

    Thank you for your post, but there is not a current way to use Excel VBA to access Box files. Could you tell us more about how you would use this at your company?

     

    The Box Community team shares selected conversations on the community back to other Box teams from time to time, including product feedback. However, if you'd like to provide direct feedback you can always submit a case to Box support.

    Thanks for your help and time in the Community!

    "

    0
    Comment actions Permalink
  • bascobill

    Hello Howard

    We’re an organic food ingredient (industrial pack size) company, primarily sugar. We use VBA modules in Office to transform Vendor’s Certificates of Analysis to our format for our customers. The function decides which template to load based on the sugar type, makes the necessary calculations and data transforms, then writes to the working copy of the template and saves to s designated area (programatically determined also) on our fileserver. We do thousands of these in a year. What I was hoping was the VBA could get the correct template from a Box location and save the finished Excel COA to a Box location.

    Bill

    0
    Comment actions Permalink
  • Marek1

    This is possible, but you need to have the Box Sync function installed. Then you navigate in folders as ususally in windows as all synced files are in the Box Sync folder.

    0
    Comment actions Permalink
  • aciechomski11

    Here you have my modules on gitHub, it is sample code how you can work with BOX API using VBA:

    https://github.com/adciechomski/BOX_API_VBA

     

    Just import 3 modules to your project and follow readMe file.

     

    to get client_id and secret_id open Dev account on BOX and create application using BOXdev UI.

     

    Have fun.

    0
    Comment actions Permalink
  • MikeDBMan

    This is exactly what I am looking for.  However I am not an expert when it comes to reference libraries.  I am having trouble figuring out exactly which references I should add to my VBA References.  Your readme file says

    ScriptingRuntime,

    Microsoft HTTP Object Library

    Microsoft Internet Controls.

     

    Could you possibly share with me the exact names of the ones you use?  So far I have not had much luck. Thanks for the help!  And some live examples of your use of this code would be helpful as well.  Thanks so much.

    0
    Comment actions Permalink
  • MikeDBMan

    I found these items references and loaded them:

    Microsoft Scripting Runtime
    Microsoft Internet Controls
    Microsoft HTML Object Library

     

    I cannot find anything called "Microsoft HTTP Object Library" however.  Is the HTML one the right one or what?

     

    Also a couple more questions about data on the BoxTransactionExamples module: 

    1.  How do I "pull in" the client_id?

    2.  How do I pull in the client secret?  Or even how to do I determine what this is?

    3.  Where do I get the security_token string?

    4.  What is the redirect_uri string as well?  Where do I get it? 

     

    Thanks for any help.

    0
    Comment actions Permalink
  • jses

    Hey,

     

    Super late to the party, but I was asking this same question and found my own wrap-around way to update an excel file through Box Drive. In my case, I needed to update product statuses in a shared file on Box.com. While not strictly through Box.com, the end result is the same. You just need more tools. Here's how I did it:

     

    1. In a separate workbook I track the changes that need to be made

    2. In this separate workbook, I added a button macro that opens the file I want to change via the Box Drive path

        (e.g. C:\Users\your_username\Box\main_folder\sub_folder\file_name.xslx)

    3. A second macro checks to make sure that the file is in the unlocked state by checking the Read Only property:

        If Not ((GetAttr(path) And vbReadOnly) = 1) Then Call UpdateMacro

    4. The updates queued are executed only if the offline copy can save to the Box servers

    5. At the end of the update macro, I suppress alerts, save the workbook, and close.

     

    This has worked consistently for my team even with heavy traffic both through Box.com and my helper Excel file. Generally, Box can't handle files with macros so by creating a separate document outside of Box that can make these calls you've got yourself a template for updating any excel file you store on Box. Way easier that dealing with OAuth, but only as secure as your computer is.

     

    Cheers

     

    P.S. Previously, the file attribute didn't show up as Read Only so instead I wrote a stopcheck macro to see if any duplicate files were created, delete them, and notify the user that the file was locked.

    0
    Comment actions Permalink
  • MikeDBMan

    That seems too simple. I will ABSOLUTELY try it, and soon!  That is basically how I currently do my “processing” on SharePoint.  My VBA code is in a “master” file that reads the data into an array that I use to populate a series of up to 30 or so other files that are on SharePoint.  Then I test to see if I can check it out from SharePoint I do so and update, it save it back to SharePoint and check it in.  You are doing the functional equivalent of that exact same process.  Thanks for the input!

    0
    Comment actions Permalink

Please sign in to leave a comment.