How to Write BASIC Calc Macro using... Environ("UserProfile") ...in Folder Path Like VBA Have the Option to Use ? Is there anything available in Basic Macro ? In Windows Os

sv.thiyagarajan · November 9, 2023, 6:34pm

Hi Friends,
I Want to Write Macro Code in LibreOffice_Calc Macro in BASIC. I My Macro in between I want to Access Folder Path and get the Files From " C " Drive. Under Drive “C”, there is Documents Folder there and In Documents Folder , there are some Sub_Folders are there… and I want to access One Of the Folder “\testing_Folder”.

Condition is
============
There are Different PCs are there and Each PC have Two Users Sharing Same PC.
Different PC have Different User Name and Each PC have Two Different User Name

But, All The PCs have Same Folder Path …\Documents\Files and Folders\testing_Folder Under
C Drive in Windows 10 Os.

Only Difference is UserName .

I want to Write Macro In LibreOffice Calc, How to get or Equal Function or Method Environ(“UserProfile”) to get the User Profile in Windows 10.
Like VBA have that one to Applicable For All the Users To Run the Macro Code and Will work for that macro in all Users.

For Easy Understanding below Two Macro Code give same Result.
But, 1st Code Will Work Only One PC and also Only One User Name “svthi”
2nd Code Will Work All The PC and All The Users.

Sample Code …To Understand My Point : Hard Code

Sub CreatingFolder_UsingVariable_ToStorePath()
    Dim fso As Scripting.FileSystemObject
    Dim NewFolderPath As String
    
    Set fso = New Scripting.FileSystemObject
    NewFolderPath = "C:\Users\svthi\Documents\Files and Folders\testing_Folder"
       
        If Not fso.FolderExists(NewFolderPath) Then
            fso.CreateFolder NewFolderPath
        End If
    Set fso = Nothing
End Sub

Sample Code : Dedecting User Profile

Sub Dedecting_UserProfile()
    Dim fso As Scripting.FileSystemObject
    Dim NewFolderPath As String
    
    Set fso = New Scripting.FileSystemObject
    NewFolder = Environ("UserProfile") & "\Documents\Files and Folders\testing_Folder
            
        If Not fso.FolderExists(NewFolderPath) Then
            fso.CreateFolder NewFolderPath
        End If
    Set fso = Nothing
End Sub

Please, Help Me

karolus · November 9, 2023, 7:02pm

service pathsubstitution

Sub Main
psubst = createUnoService("com.sun.star.util.PathSubstitution")

msgbox psubst.substituteVariables("$(home)/Documents",True)

End Sub

########

from pathlib import Path

test_folder = list(Path.home().glob("**/testing_Folder"))[0]

Zizi64 · November 9, 2023, 7:15pm

Check the capital letters in the names of the Environ variables:

LO_Environ.ods (17.0 KB)

karolus · November 9, 2023, 7:56pm

@Zizi64 on (my) Linux none of them works except: JAVA_HOME

Zizi64 · November 9, 2023, 8:20pm

On my Win 10 x64 Prof.:

Villeroy · November 9, 2023, 8:38pm

	srv = createUnoService("com.sun.star.util.PathSubstitution")
	sUser = srv.getSubstituteVariableValue("user")

karolus · November 9, 2023, 8:40pm

With me (debian bookworm Raspberry Pi4b aarch64)

from os import environ
environ_data = [[key, value] for key, value in environ.items()]
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.CurrentController.ActiveSheet
sheet[:len(environ_data), :2].DataArray = environ_data

JohnSUN · November 10, 2023, 8:02am

I’m not proposing a solution yet, I want to clarify the problem. Several times I encountered a situation where repeatedly tested code stopped working correctly. One of the reasons was not obvious: due to the too large size of the Documents folder, the system drive was full and the Documents folder was completely moved to an additional drive (for example, F:). Perhaps you should not look for the name of the current user, but immediately the correct location of the documents folder? Something like this:

Function getDocFolder(sSubfolderName As String, ByRef sFullPath As String) As Variant 
Dim InitPath As String
Dim oUcb As Object
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
	oUcb = createUnoService("com.sun.star.ucb.SimpleFileAccess")
	InitPath = GetPathSettings("Work")
	If Right(InitPath,1) = "/" Then
		sFullPath = InitPath & sSubfolderName
	Else 
		sFullPath = InitPath & "/" & sSubfolderName
	EndIf 
	If oUcb.Exists(sFullPath) Then
		getDocFolder = ReadDirectories(sFullPath, True,  False, True)
		sFullPath = ConvertFromURL(sFullPath)
	Else
		getDocFolder = Array()
		sFullPath = ""
	EndIf 
End Function

Sub tstGetDocFolder()
Dim sFolderPath As String, aFiles() As Variant 
	aFiles = getDocFolder("testing_Folder", sFolderPath) 
	If sFolderPath = "" Then
		MsgBox("Subfolder not found",16,"Error")
	Else 
		MsgBox("There are " & (UBound(aFiles)+1) & " files in folder " & sFolderPath,0,"Result")
	EndIf  
End Sub

mikekaganski · November 10, 2023, 8:31am

On Windows, you may also find the user’s relocated Documents directory using something like

sub MyDocuments_Win
  wshShell = CreateObject("WScript.Shell")
  MsgBox wshShell.SpecialFolders.Item("MyDocuments")
end sub

JohnSUN · November 10, 2023, 8:53am

Yes, you are right. But I would like to have a solution that will work not only in Windows.

mikekaganski · November 10, 2023, 8:57am

… and your code does that nicely. The only gotcha there is knowing that the GetPathSettings("Work") works not on system level, but on LibreOffice configuration level; so there might possibly be differences between LibreOffice-configured “Work” directory, and user’s shell’s Documents.

JohnSUN · November 10, 2023, 9:05am

Yes, that’s exactly what I’m thinking - where should the testing_Folder subfolder be found? Perhaps it makes sense to try to find them all throughout the computer?

sv.thiyagarajan · November 10, 2023, 9:22am

Hi @Zizi64 Thanks Your Calc Sheets Macro and Screen Shots …It Works Very Simple in my Code.
Using Environ Function

Environ Function in Calc Help File.

The Below Code Works All The PC and any User Name in Windows 10 Os

Sample Code : Detecting User Profile In VBA

Sub Detecting_UserProfile()
    Dim fso As Scripting.FileSystemObject
    Dim NewFolderPath As String
    
    Set fso = New Scripting.FileSystemObject
    NewFolderPath = Environ("UserProfile") & "\Documents\Files and Folders\testing_Folder"
      
        If Not fso.FolderExists(NewFolderPath) Then
            fso.CreateFolder NewFolderPath
        End If
    Set fso = Nothing
End Sub

Below LibreOffice_Calc Basic Macro …Will Work all the Windows 10 PC in any User Name

In LibreOffice Calc _ BASIC Macro - Detecting User Profile in Windows 10

Sub Directory_Creation_SFA
	Dim oSFA
	Dim NewFolderPath As String
		
		oSFA = createUnoService("com.sun.star.ucb.SimpleFileAccess")
			
			REM in My Windows 10 Os UserName is   svthi
			NewFolderPath = Environ ("USERPROFILE") & "\Documents\Files and Folders\testing_Folder" 
			MsgBox NewFolderPath         ' Here, NewFolderPath returns  C:\Users\svthi\Documents\Files and Folders\testing_Folder
			
			If NOT oSFA.Exists(NewFolderPath) then
				oSimpleFileAccess.createFolder(NewFolderPath)
				             '    Write Code here, What ever doing.....
			else
				MsgBox(NewFolderPath & Chr$(13) &  " !!!..testing_Folder.... Already Exist", 0, "Caution !!")
				Exit Sub
			End If
			                 '     Write Code here, What ever doing.....               
End Sub

For Learning Purpose … Some of The Codes.

Sub Environ_HomeDrive_HomePath
		    Dim sHomeDrive As String, sHomePath As String, combinePath1 As String, combinePath2 As String
		    
		    sHomeDrive = Environ ("HomeDrive")     ' C:
		    MsgBox sHomeDrive
		    
		    sHomePath = Environ ("HOMEPATH")       ' \Users\svthi
		    MsgBox sHomePath
		    
		    combinePath1 = sHomeDrive & sHomePath
		    MsgBox combinePath1    ' C:\Users\svthi
		    
		    ' ShortCut To get The Result  C:\Users\svthi  , We Can Combine them in a Single Line
		    combinePath2 = Environ ("HomeDrive") & Environ ("HOMEPATH")       '  C:\Users\svthi
		    MsgBox combinePath2
End Sub


Sub Environ_ComputerName
		Dim compName As String
		
	    compName=Environ ("COMPUTERNAME")
	    If compName = "" Then compName = Environ("COMPUTERNAME")
	    MsgBox compName , 64, "COMPUTERNAME : "      ' KUMAR-PC
End Sub


Sub Environ_UserProfile
		Dim userProfile As String
		
	    userProfile = Environ ("USERPROFILE") 
	    MsgBox userProfile , 64, "USERPROFILE : "     ' C:\Users\svthi
End Sub



Sub Environ_SystemRoot_Drive
		Dim sysRoot As String, sysDrive As String
		
		' Here, the Os is Window 10 and Drive  C
	    sysRoot = Environ ("SystemRoot") 
	    MsgBox sysRoot, 64, "SystemRoot : "      ' C:\WINDOWS
	    
	    sysDrive = Environ("SystemDrive")
	    MsgBox sysDrive, 64, "System Drive : "     ' C:
	    
End Sub

Thanks To @mikekaganski Yes,…“WScript.Shell” Works… Now, i am learning New Thinks…

Sub MyDocuments_Win
        wshShell = CreateObject("WScript.Shell")
        MsgBox wshShell.SpecialFolders.Item("MyDocuments")
End Sub

Thanks To @JohnSUN , @karolus , @Villeroy …
Thanks To ALL…

sv.thiyagarajan · November 10, 2023, 6:40pm

Hi @JohnSUN ,
In All The PCs or Computers are Windows 10 Os and Each PC have Sharing Two Users . So, Two User Name in a Single PC. say for exam John is one User and Erick is another User.
The Path is
C:\Users\John
C:\Users\Erick\

They are working and save the reports in ..\Documents\Files and Folders\testing_Folder
So, User John have the Path 
C:\Users\John\Documents\Files and Folders\testing_Folder

and User Erick have the Path
C:\Users\Erick\Documents\Files and Folders\testing_Folder

Same Like All The User in the Winodws PC have Same Path … Except UserName…

So, To Run the Macro in  C Drive   C:\Users\...\Documents\Files and Folders\testing_Folder
to All The Users.....in All The PC  getting Results with out any issue..
This is Query or Doubt , So, I asked in Help Community ... and i Got the answers...

In VBA , We Can use Simply Environ(“UserProfile”)
and the Code is Environ(“UserProfile”) & "\Documents\Files and Folders\testing_Folder

and got Answers…to Use In BASIC - Calc Macro
We Can Use the Same Environ Function
and the Code is Environ (“USERPROFILE”) & “\Documents\Files and Folders\testing_Folder”

We can use the Environ("...") 
With Following Names
"USERNAME", "COMPUTERNAME", "USERDOMAIN", "TMP", "ALLUSERSPROFILE",_
   	"CI_HOLOS_CLI", "CLIENTNAME", "windir", "USERPROFILE", "TEMP", "SystemRoot",_
   	"SystemDrive", "SESSIONNAME", "ProgramFiles", "PROCESSOR_REVISION",_
   	"PROCESSOR_LEVEL", "PROCESSOR_IDENTIFIER", "PROCESSOR_ARCHITECTURE", "PATHEXT",_
   	"Path", "OS", "NUMBER_OF_PROCESSORS", "MSDevDir", "LOGONSERVER", "lib", "JAVA_HOME",_
   	"include", "HOMEPATH", "HOMEDRIVE", "FP_NO_HOST_CHECK2", "ComSpec",_
   	"COMPUTERNAME", "CommonProgramFiles"

mikekaganski · November 10, 2023, 8:48pm

But simply `Environ(“UserProfile”) works fine in LibreOffice. And so, both

Environ("UserProfile") & "\Documents\Files and Folders\testing_Folder"

and

Environ("USERPROFILE") & "\Documents\Files and Folders\testing_Folder"

produce exactly the same result - both in VBA, and in LibreOffice Basic. Environ is case insensitive on Windows. It uses getenv, and the latter is case insensitive on Windows…

Zizi64 · November 10, 2023, 9:54pm

Here are names with full capital letters an there are other types:

gist.github.com

https://gist.github.com/RebeccaWhit3/5dad8627b8227142e1bea432db3f8824

Complete List of Environment Variables in Windows 10.md

Full List of Environment Variables in Windows 10
================================================

# ![information][6]   Information

> [**Environment variables][7]** are a set of dynamic named values that can affect the way running processes will behave on a computer. The variables can be used both in scripts and on the command line. Environment variables makes it easy when certain standard directories and parameters need to be referenced but where the actual locations or names can vary from computer to computer.

This tutorial will show you a complete list of **environment variables** that can be used to reference standard directories and parameters in **Windows 10**.

## ![Note][8] Note

This file has been truncated. show original

My sample code was created in the past based on a similar page.
(I know these are not official MS pages…)

mikekaganski · November 10, 2023, 10:48pm

:⁠-⁠) I don’t quite see what you want to show here. I can create an alternative gist with all lower case, or CamelCase.

Zizi64 · November 11, 2023, 6:17am

As I recall it, some of them worked with another format of names, but a few of them isn’t. It happened with years ago with Win XP or with Win 7. I not tried now (when I uploaded my sample here).