Hello,
I am not a SharePoint admin. Our company has one, but I would prefer to come up with a good plan for him to examine prior to engaging him on this project need.
I am well-versed in Access, and a little VBA...
The Current: <Caveat - I did NOT design this process, but I am very interested in improving it.
A department stores its documents in a SP site which serves as a queue of inventory for jobs for them to process.
Each document is a job, for instance.
There are 33 separate document libraries/folders, each needing to be inventoried every day.
Currently, we use one excel spreadsheet per library(33), and something called an "iqy" file to run some XML and retrieve a listing of the documents in a single library.
The method for refreshing these each day is to open each Excel spreadsheet, it then uses the IQY associated with it (1 of 33) to retrieve a listing of documents in that single folder/library. After that connection refreshes, we save and close.
The spreadsheets are then linked as tables in MS Access, where it combines them and puts them into a Crosstab query for examining how those documents are "aging".
The Need:
My frustration is related to the opening/closing of each of these documents. We have done some VBA within Excel to automate the open/close, but the refreshes are not occuring consistently enough to use this auto-open/close code. Not withstanding any fix for said VBA, the person who is preparing this data each day has a slow machine, where opening/closing Excel repeatedly takes longer than it should. Ideally, we'd be able to move this entirely into Access.
My thoughts...
Could a singular Iqy file be written to grab inventory from all of these libraries, and simply indicate a category for each one so that it can be split out again later?
OR, could a SQL query within Access perform the same operations as one or all of these iqy files?
Here is a snipet of what the iqy code looks like: [Identifiable data redacted]
Code:
WEB1
https://sp2013.companyname.com/teams/Dept/_vti_bin/owssvr.dll?XMLDATA=1&List={62EAB694-A0A3-4318-B4B5-13AE383B8C6C}&View={0E7EA891-4C32-4072-BA23-1E05CE4A6EE9}&RowLimit=0&RootFolder=%2fteams%2fIPC%2fClientName%20Name1%20Name2%20%20Name3
Selection={62EAB694-A0A3-4318-B4B5-13AE383B8C6C}-{0E7EA891-4C32-4072-BA23-1E05CE4A6EE9}
EditWebPage=
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=https://sp2013.companyname.com/teams/Dept/_vti_bin
SharePointListView={0E7EA891-4C32-4072-BA23-1E05CE4A6EE9}
SharePointListName={62EAB694-A0A3-4318-B4B5-13AE383B8C6C}
RootFolder=/teams/Dept/Client Name - Program Name
Can this be done with either one iqy that grabs from 33 libraries, or can we do this with SQL and accomplish the same? Seems like there should be a way!