Spare parts, but no spare time but i puttered away at this anyways. So there are a couple kinks to work out, but I wanted something remote for my Time Machine so it could work on the hour every hour in the background. My wireless on my Apple Extreme went but the main router is still in action. So what now? I need somewhere to plug an external USB drive into. So due to Covid19 – like many people I snapped and cleaned my office drawers and dusted off an old Rasperry PI 3 B+. Hey – I could try that cause my time machine perpetually says preparing backup and all the online help doesn’t work.

I had a spare drive (not sure why) so I cobbled the whole thing together and after > 24 hours of it backing up – I think it works. Here are links and quick tips to keep it going

You can do all of this without being an admin

List Id is easy: Go to a list, switch to classic mode, Click Library->Edit Library – your id is in the URL between %7B abd %7D (%7D is at the end of the url most likely). Those % items represent { and } respectively

Site ID: If your site is https//me.sharepoint.com/sites/MYSITE then add _api/site/id into a new tab in your browser and press enter. Your answer should be there. (Your browser will do a GET to the REST import will do a simple GET to the sharepoint get the site ID using REST)

WebID: Kinda lame once you know the site ID trick. just add _api/web/id.

This has become less exciting now that I know how. The reason I wrote this post is tehre are TONNES of resources on how to get these if your an admin or if you sharepoint powershell works – but adding the words to google “not as an administrator” got me these answers quick.

References: this guy, this chrome extension and this guy are my heros for this.

Seems simple enough – well I fought a bit – and rather than re-write an article, I will put a link to a sub-section of another article. Here is the link to help jump you there.

I have a file that is an .xlsx file that has NO code that loads a .xlam file with ease. BUT in the last article, as you can see the .xlam file is on sharepoint. Oh no – what now? It seems .xlam files do load properly whereas sometimes (security/trust issues) the .xlam file needs a helper file to get it kick started/loaded.

So I placed a massively large button on the whole spreadsheet (my sense of humour) but it was clear what needed to be done. PRESS THE BUTTON. Then the macro it loaded closed the workbook. Worked perfect. Oh oh – there was a problem – crap it can’t be changed to open a share point file. oh – wait – if you do this little trick – it works.

Ummm. Oh – here is the sweetest part – CORPORATE SYSTEMS WILL NOT STRIP THIS .xlsx attachment cause ‘ain’t no macros here boss!’. Boo-ya.

It seems you CAN run .xlam files from Excel as a central library whereas a year ago (might be more – time flies) this was not allowed. If you have a company that has SharePoint – it is finally ready. Using techniques you ALREADY know – here is how I did it and how credentials etc. fit into things. What a ride – and it works.

• on-prem on on-premises means the file’s url is like https://sharepoint.mycompany.com
• whereas hosted by microsoft would look like https://mycompany.sharepoint.com or https://mycompany-my.sharepoint.com is what I have noticed.

It is easy to enable the checkbox in the options screen. The option can also be set via a registry entry (or two). To register a change in the registry, Excel must be restarted, but ALSO it is important to note that when Excel exits, it resets this flag. Excel cannot be running therefore. This means we need a few ingredients when we are programming a solution.

First check if Excel is running

• to do this we have to know the latest Office version,

• is it 64 or 32bit as it affects what registry entries could exist.

Next set the registry entries

If the user does not need ton know if a setting will be successful , then use the “set it and hope for the best” strategy.

has been reset and it will fail anyways – then just set the 2 registry entries. Set the DWORD ‘AccessVBOM’ = 1 to trust.

Here are the two registry entries. In short set this one to 1 and the 2nd reg entry item if it exists.

• HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Security\AccessVBOM
• HKEY_LOCAL_MACHINE\Software\Microsoft\Office\16.0\Excel\Security\AccessVBOM

This article is simply a super great place to find links to the most used articles

• imageMso codes for customUI images
• [MS-CUSTOMUI]: Custom UI XML Markup Specification
• Absolute Path example of onAction
  • onAction=”‘L:\Directory1\Directory2\Spreadsheet name.xlam\’!MyFavFunctionInExcelsName”
  • and then you need to add a handler called MyFavFunctionInExcelsName(control As iRibbonControl)

There are some pretty cool “on the fly” programmatic things we can do with the VBA references like deleting “MISSING” references (which break code that is referenced UNDER these missing references- that is why they need to be fixed). But to do so requires that the user checks the box ‘Trust access to the VBA project object model’ which is buried deep (in typical Microsoft fashion). The Ribbon to check this manually is in the popup window accessed by File->Options->Trust Center->Macro Settings. Instead of writing code to tell the user what to do – why not get a registry change on all the target computers first. This article gives the *hint* on how to do that. I have 32bit Office (on purpose – so I don’t forget about those users) but everyone should have 64bit. So – there are a few registry keys that should get set.

Here’s a couple links that explain how to unzip and modify the OpenXML file hierarchy of .xlsx and .xlsm files and how to create custom ribbonUI. This should make it possible to access and modify the customUI ribbon via customXMLParts at runtime.

Opening and modifying excel files as zips: https://www.jkp-ads.com/articles/excel2007fileformat02.asp

VBA customUI editor: https://yoursumbuddy.com/ribbon-customui-xml-editor/

First, we need to learn where the Cadworx settings are stored.If we can manipulate the constants with variable manipulation – then we have moved one step closer to not needing a mouse. Placing components seems to be quite easy without a mouse.

• get a list of system variables (nope – not it)
  • spitting command output to the log – where is the log?
• Complete list of sys vars
  • SYSVDLG (System Var Dialog) (nope – not it)
• AutoCAD SysReg Vars (who knew!?)
• Webinar of Cadworx Sys Vars – this is a facebook site – I didn’t go past the home page
• SETSIZE, SETSPEC and the prj file (no link – I am looking at the CADWorx Plant Help chm file (windows help file)
• SETUP command – this is where you can set it
• Stored in DXF File!? YES – YES IT IS
  • DXF Structure, DXF Codes
  • DXF Specification
  • R-Click Quickselect Type=”Block Reference”- look for Block Name=”CPM”, then type LIST – it is in the XData though
    • – it is on Model Space Layer 0 at 0,0,0
  • I THINK I HAVE IT (a friend old me to look at LispVariables)
• a buddy of mine found a tool that was already in the sub-folders of AutoCAD called acadinfo.lsp . You type acadinfo at the command prompt and it spits out a text file with all sorts of goodies many of which are mentioned above