"Environ" like function for MAC users

Greetings all!

I am sharing an excel document with other users. The AppleScript (attached to a button) references my hard drive name. Is there a way to change the name to whomever uses it? So that they can utilize the scripts on their Macs?? They share the Dropbox folder. I’ve researched the web and found no solid answers. Script is below.

Sub Button1_Click()
Dim scriptToRun As String

scriptToRun = “set myScript to ““Macintosh HD:Users:MyUserName:Dropbox:Mac Excel Scripts:NewWorksheet-2008.scpt”” as alias”
scriptToRun = scriptToRun & vbCr & “run script myScript”
MacScript (scriptToRun)

End Sub

For windows this is accomplised by using the Environ:
"\Users" & Environ(“Username”) & "\Dropbox\Mac Excel Scripts\NewWorksheet-2008.scpt"

I want to replace myUserName with theirUsername. If there’s a better way to do this, please advise. Thanks again for the help!! This forum has been awesome!

-E

Hello.

Getting a path you can use everywhere, is quite simple.

set pth to (path to home folder as text) & "Dropbox:Mac Excel Scripts:NewWorksheet-2008.scpt" as alias

But it will fail, if something is amiss, it is better to test that the conditions are like they should before you run your script. Yes, you may be sure that they still have the Drobox folder, for instance, but shit can happen, and then the script gets a run time error because it can’t create the alias, resolve it to a diskfile. :slight_smile:

 set pth to (path to home folder as text) --  & "Dropbox:Mac Excel Scripts:NewWorksheet-2008.scpt" as alias

tell application id "sevs" to set pass1 to exists item (pth & "Dropbox")

if not pass1 then
	tell application (path to frontmost application)
		display alert "Checking folders: Pass1: Dropbox not found, correct it and try again."
	end tell
	return
else
	tell application id "sevs" to set pass2 to exists item (pth & "Dropbox:Mac Excel Scripts:")
	
	if not pass2 then
		tell application (path to frontmost application)
			display alert "Checking folders: Pass2: Mac Excel Scripts folder of Dropbox not found, correct it and try again."
		end tell
		return
	else
		
		tell application id "sevs" to set pass3 to exists item  (pth & "Dropbox:Mac Excel Scripts:NewWorksheet-2008.scpt")
		if not pass3 then
			tell application (path to frontmost application)
				display alert "Checking folders: Pass3:NewWorksheet-2008.scpt of folder  Mac Excel Scripts folder of Dropbox not found, correct it and try again."
			end tell
			return
		else
			set workbookpath to (pth & "Dropbox:Mac Excel Scripts:NewWorksheet-2008.scpt") as alias
		end if
	end if
end if

Edit I corrected an error in addressing the files, for the record, “sevs” is SystemEvents, but using application id “sevs”, instead of System Events, should bypass some lookup by launchd. It is shorter to write anyways. :stuck_out_tongue:

I’m still new to AppleScript. How do I implement it into the VBscript for reference? I’ve tried:

Sub Button1_Click()
Dim scriptToRun As String

scriptToRun = "set myScript to ““set pth to (path to home folder as text) & “:Dropbox:Mac Excel Scripts:NewWorksheet-2008.scpt”” as alias”
scriptToRun = scriptToRun & vbCr & “run script myScript”
MacScript (scriptToRun)

End Sub

I get the Runtime 5 error.

-I’ve tried without quotes but get a compile error as well. Thanks for the help!!

Hello.

I have no idea how quotation works in VBA, so I have quoted the string like it should be in AppleScript-

That is the only way I can think of to make this work, but it may of course break in VBA, then you’ll have to google it.

I actually think it is a much better approach, to get the path to your home folder from VBA, and then concatenate that and the string that contains the dropbox path, with the script in VBA

this should be a valid hfs path, and you can verify that by running a display alert or something, so that you are sure you have end up with an with a hfs path.

If you get that far, then you make a new string, adding the run script piece.

When you have built the myScript string, then you can proceed with the lines below.

I’m sorry I can’t help you any better, but I havent used VBA for some soon 20 years now. :slight_smile:

PS. I remembered Ron De Bruin, who is a member here, maybe you can learn how to quote commands from this page of his

AndI have some vague memories, that Ron De Bruin, fixed a similiar problem, by inserting quotes by using the vba function chr(34), wich returns a quote. (").

HTH