UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Vba To Output Excel File To Teams Document Library, Access 2016    
 
   
Katty
post May 19 2020, 07:34 AM
Post#1



Posts: 173
Joined: 1-February 10
From: Lincolnshire UK


Hi All,

I am not sure if I am not searching right, or if this simply isn't possible.

I have an application that currently outputs a combination of excel files and text documents to a drive share that the company want to turn off as they want to move entirely to web based storage. Luckily, this can't happen until we solve the below issue.

IS have decided that no one can map any network drives. This is non-negotiable. This includes mapping to Teams SharePoint document folders. Some "power users" can download One drive for Business, but this only connects to their personal one drive folders and can't be shared (I think??).

So the question is: is there any way to code in vba an output directly from the Access database (in most cases it is currently using outputto for an excel output) to a Teams document location, without drive mapping? Or is the only option to save to a local location and the user will need to manually copy the file out to the teams location? I am open to any solution that automates other applications, as long as those are part of the standard Office set or Windows 10. I can't install any 3rd party programs and I lack the utilities to write/compile much code in other languages myself.

I will admit I haven't tried much as I simply couldn't find much. I verified I can save files manually from excel to one of my own team locations. If that is the only solution, I suspect the users will struggle to remember that they need to do this each time they output a file.

It seems in this day an age where the products are all integrated to each other that it should work, but I've tried searching for various things on google and the closest I came was a solution that required mapping the teams location as a drive, which I can't do because of IS...

Thanks in advance for any help anyone can give.

Katty
Go to the top of the page
 
nuclear_nick
post May 19 2020, 09:13 AM
Post#2



Posts: 1,872
Joined: 5-February 06
From: Ohio, USA


How do you view the files in a location without mapping?

(I'm guessing UNC path?)

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
Katty
post May 19 2020, 09:21 AM
Post#3



Posts: 173
Joined: 1-February 10
From: Lincolnshire UK


At the moment, we use either the Teams application itself or the Office 365 via a web browser.

In Excel and Word, when manually choosing save as, you can also browse these locations via "Sites" on the left hand side. I'd basically like to automate using this "save as - sites - team- document - folder structure". It isn't actually a UNC path as far as I can tell, as when I try to copy and past it into a file explorer, it says the location doesn't exist, yet when I save it through Excel it works fine.

(**Edit**)
I just realized you might have been asking about the current files. At the moment, they are on a drive share where the front end is currently. Users are using terminal services to connect to the front end of the database (each have their own FE) and then saving their files on this server. The idea is for the FE to be local on their machines, and the files to be stored on teams/one drive in the future. The back end is in SQL and the performance between local FE and TS FE is actually better believe it or not.


This post has been edited by Katty: May 19 2020, 09:27 AM
Go to the top of the page
 
nuclear_nick
post May 19 2020, 09:33 AM
Post#4



Posts: 1,872
Joined: 5-February 06
From: Ohio, USA


It looks like (even thought I haven't done it this way before...), that when you choose a site during a 'save as' operation, then click the box at the top, it'll show the "path" you might be able to use. In my case, it looked like...
CODE
https://<company name removed for security reasons>.sharepoint.com/

So... see if that helps.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
Katty
post May 19 2020, 10:07 AM
Post#5



Posts: 173
Joined: 1-February 10
From: Lincolnshire UK


Hi Nick,

I'll give it a go. The first time I tried I used the entire path which contained all sorts of random letters in addition to the actual path data and I got a simple path not found error. It appears the path you get from the save as is a bit better formed, although it is still a web address so I am not sure it will work.

Thanks for the help.

Katty
Go to the top of the page
 
Katty
post May 20 2020, 08:03 AM
Post#6



Posts: 173
Joined: 1-February 10
From: Lincolnshire UK


Just in case it helps anyone else.

Nothing I did within Access seemed to work with regards to the path to the Sharepoint/teams location I could get from either the saving, or directly from the web interface. I kept getting errors that it couldn't save to that location or invalid path. I even tried to force it to look like an UNC by changing the https to \\.

What I am going to propose as the solution (not sure IS will go for it but we'll see) is actually something I found by accident.

I have one drive for business installed on my laptop, and within our Sharepoint web interface, there is a button called Sync. This button creates a folder within the users local documents folder that syncs with the folders within the team location. It will also replicate folder structure.

The idea will be for the database to save to this location on the users physical drive, which I can pick up easily with code. The only problem I can see with this is that ultimately as more data is output, the data for the entire team will reside on each users physical computer as well as in the cloud.

If we can get IS approval to get everyone One Drive for Business, then we can work out the logistical process to keep the outputs saved locally to a minimum. It will likely involve powerautomate (flows) or perhaps training for the staff to move the files around, but at least the initial output isn't just to their local machine and others within the team will be able to get to the files if required.

It isn't the best solution, but at least it works.

Thanks for the help.

Katty
This post has been edited by Katty: May 20 2020, 08:04 AM
Go to the top of the page
 
nuclear_nick
post May 21 2020, 06:41 AM
Post#7



Posts: 1,872
Joined: 5-February 06
From: Ohio, USA


Sorry I couldn't help more, but it seems your IS may be run by … /rant on/ idiots?

Files, a way of storing information, no matter the type of file, have to be 'stored' somewhere, so without giving you access to store files somewhere you can share them with those who need it... Seems very counterproductive to 1)getting things done, and 2)understanding how 'storage' works. Even our IT department discovered yesterday that you can't send emails without an email account. (And here I thought that was... well... common knowledge amongst email users...)

/rant off/

smile.gif

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    2nd July 2020 - 07:00 AM