RogueTemplat
08-20-2021, 01:32 AM
I am a Microsoft Office developer and have created various solutions for Word, PowerPoint and Excel using VBA, modified ribbons, etc. I have an add-in project for a client part way completed but one aspect of it has been derailled by the client's IT moving some goalposts.
The part that is affected I call the "Content Library" and it is just a folder structure that contains some files. Normally it resides on a network location and clients manage the folder structure and contents themselves. The add-in displays a form that looks a bit like File Explorer (tree on the left, files on the right) and the users pick what they need from that form.
Originally we had talked about a shared OneDrive folder being the location for the "Content Library" and that would have been fine, now the client's IT is saying that OneDrive isn't a good solution after all as it isn't stable for all users and may not sync.
Instead they want me to host the Content Library online "somewhere" and interact with it that way.
The rest of my add-in's functionality is unaffected by this change so I have to stick with a VBA based add-in.
I am not really sure what the best way to implement this is. Here is what needs to happen ...
I need to manually store a folder structure and files in a cloud-based location. Update of these files need not be particularly user-friendly, will be their Admin or me.
Be able to traverse the folder structure in code so I can build a "tree" on my userform.
Be able to list files in any branch of the tree and download those selected
Be able to tell the date/time of files in the structure (there is an auto-update aspect to the system)
It is a one way system, nothing gets uploaded
The client's files need to be secure, not accessible to just anyone. I don't need multiple levels of security though, all users see all files.
I need to stick to VBA and standard Windows API calls. Installing additional DLLs or such may create new IT issues!
Research so far has thrown up ...
FTP (not sure how much I can "interrogate" the structure or file info tho for date/time data)
WebDav / HTTP Post/Get - don't know much about this stuff at all. Perhaps against a to a SharePoint Document Library (which I can make and set permissions for in 365). Gut feel is it will be a slow way to interact?
SOAP - I have done a project in the past that used SOAP calls because that is what the client had/needed. Not sure if it is a "standard" web access method or just something they happened to have available. Was a long time ago but think it did involved interrogating a folder structure. Think this would be a bad way to go, given it must have been depreciated by now
Online database (SQL, MySQL, say). The folder structure would be virtual (held in a table) and the files uploaded as objects. I could also store the file date/times so that info then easy to get. I would need to create an upload interface but I think I could do that later. Initially I could just upload via database management software. I feel like this might be faster than interacting with an actual folder structure? Less sure of how VBA can interact with such a database though. Can it be done without anything "special" on the machine?
So, I guess my actual question has two parts ...
A. Where to store the structure / files
and
B. How to interact with the structure / files using VBA
Just after some guidance / methods to review online.
Hope this all makes sense!
Thanks
Simon
The part that is affected I call the "Content Library" and it is just a folder structure that contains some files. Normally it resides on a network location and clients manage the folder structure and contents themselves. The add-in displays a form that looks a bit like File Explorer (tree on the left, files on the right) and the users pick what they need from that form.
Originally we had talked about a shared OneDrive folder being the location for the "Content Library" and that would have been fine, now the client's IT is saying that OneDrive isn't a good solution after all as it isn't stable for all users and may not sync.
Instead they want me to host the Content Library online "somewhere" and interact with it that way.
The rest of my add-in's functionality is unaffected by this change so I have to stick with a VBA based add-in.
I am not really sure what the best way to implement this is. Here is what needs to happen ...
I need to manually store a folder structure and files in a cloud-based location. Update of these files need not be particularly user-friendly, will be their Admin or me.
Be able to traverse the folder structure in code so I can build a "tree" on my userform.
Be able to list files in any branch of the tree and download those selected
Be able to tell the date/time of files in the structure (there is an auto-update aspect to the system)
It is a one way system, nothing gets uploaded
The client's files need to be secure, not accessible to just anyone. I don't need multiple levels of security though, all users see all files.
I need to stick to VBA and standard Windows API calls. Installing additional DLLs or such may create new IT issues!
Research so far has thrown up ...
FTP (not sure how much I can "interrogate" the structure or file info tho for date/time data)
WebDav / HTTP Post/Get - don't know much about this stuff at all. Perhaps against a to a SharePoint Document Library (which I can make and set permissions for in 365). Gut feel is it will be a slow way to interact?
SOAP - I have done a project in the past that used SOAP calls because that is what the client had/needed. Not sure if it is a "standard" web access method or just something they happened to have available. Was a long time ago but think it did involved interrogating a folder structure. Think this would be a bad way to go, given it must have been depreciated by now
Online database (SQL, MySQL, say). The folder structure would be virtual (held in a table) and the files uploaded as objects. I could also store the file date/times so that info then easy to get. I would need to create an upload interface but I think I could do that later. Initially I could just upload via database management software. I feel like this might be faster than interacting with an actual folder structure? Less sure of how VBA can interact with such a database though. Can it be done without anything "special" on the machine?
So, I guess my actual question has two parts ...
A. Where to store the structure / files
and
B. How to interact with the structure / files using VBA
Just after some guidance / methods to review online.
Hope this all makes sense!
Thanks
Simon