Box and Excel VBA
Hello to Everyone,
My question is: " Is there the possibility to open a file in the Box through an Excel Macro with VBA?"
Can Anyone help me with this issue?
-
I am not sure if this is possible. Another option would be opening a Box file in Excel using the Box web app.
-
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?
-
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!
"
-
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 -
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.
-
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.
-
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.
-
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.
-
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!
Please sign in to leave a comment.
Comments
16 comments