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
> Accessing Xlstart In Win Explorer    
post Apr 14 2018, 01:58 PM

Posts: 5,186
Joined: 2-November 04
From: Downey, CA

I recently wrote procedures for my department where people will be running macros - my plan is to e-mail them (or put on the network) my Personal.xlsm with the macros and have them save it to the XLSTART directory. It's been my experience that you have to accomplish this using DOS - you can't just File/Save, and navigate to the XLSTART folder. If you've saved to that folder recently, you can, but these people have never had anything in their XLSTART folder.

I just want someone to confirm my statement. My game plan is to have them save it to C:\Temp, show them how to open DOS, and how to copy from C:\TEMP to the XLSTART directory

I wanted to use add-ins, which I have NO experience with, but this proved very dangerous - I have files opening, code running in endless loops - so I'm leaving that for the experts.

Thanks !

Go to the top of the page
post Apr 15 2018, 03:38 PM

UtterAccess Editor
Posts: 9,926
Joined: 7-December 09
From: Staten Island, NY, USA

Why not give them a little bat file to do it?

DOS isn't really Disk Operating System anymore... it's just a commandline tool, and .bat files run the same commands (or .cmd files).

The trick will be locating the XLSTART folder, which could be done by consulting the registry (at least I'm pretty sure, but I haven't had to do that specific pathfinding before).

Go to the top of the page
post Apr 16 2018, 03:24 AM

Posts: 199
Joined: 2-April 18

Yesterday I did not answer because I prefered to wait someone else more expert than me.
jleach focused perfectly:
The trick will be locating the XLSTART folder, which could be done by consulting the registry

Without passing from the registry I would suggest another way. For sure it is not the most performant but it is quite easy: with a vbs file lauch an Excel instance, read StartupPath property and then use FileSystemObject to copy the xlsm file. You may put this vbs file in the same network location where you have xlsm file (for semplicity) and comunicate this path to users, who have only do run the vbs file.
Option Explicit
   Const UNCPath = "\\networkpath\folder\subfolder\"
   Const FileName = "YourFile.xlsm"
   Dim objFSO, fsoFldr, fsoFile
   Dim appExcel, strXLStart
   Set appExcel = CreateObject("Excel.application")
   strXLStart = appExcel.StartupPath
   Set appExcel = Nothing
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set fsoFile = objFSO.GetFile(UNCPath & FileName)
   fsoFile.Copy strXLStart & "\" & FileName
   'fsoFile.Copy strXLStart & "\" & FileName, True ' if you want to overwrite an existing file
   Set fsoFile = Nothing
   Set objFSO = Nothing

This worked in my test, with a simple double click on vbs file, but my user is an Administrator, perhaps all users are not, in your distribution.
It can be do better, for sure, but I am not so expert with vbs.

Please forgive in advance my horrible English.
Go to the top of the page
post Apr 16 2018, 11:01 AM

Posts: 5,186
Joined: 2-November 04
From: Downey, CA

Thanks everyone for their input - I know what a .bat file is, but not sure how to implement it - I will try the suggestions here - I FREQUENTLY refer back to my posts, but I came up with a very logical work-around - the user has to have a "log" excel workbook open, so just attach the macros to those - DUH!

I didn't know that about DOS - I'm kind of proud that I know several really basic DOS commands, haha - besides the XLSTART thing, I like to use it to print contents of a directory, although I know there's other ways

So... can you access the XLSTART via "DOS" (which is no longer a disk operating system)
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    15th August 2018 - 11:31 AM