XLib


Download
Installation
X-VBA Home

Array
=AverageHigh
=AverageLow
=CountUnique
=IsInArray
=Large
=Reverse
=Small
=Sort
=SumHigh
=SumLow

Color
=Hex2Hsl
=Hex2Rgb
=Hsl2Hex
=Hsl2Rgb
=Rgb2Hex
=Rgb2Hsl
=Rgb2Hsv

DateTime
=DaysOfMonth
=MonthName2
=Quarter
=TimeConverter
=WeekOfMonth
=WeekdayName2

Environment
=ComputerName
=OS
=UserDomain
=UserName

File
=CountFiles
=CountFilesAndFolders
=CountFolders
=CurrentFilePath
=FileCreationTime
=FileDrive
=FileExtension
=FileFolder
=FileLastModifiedTime
=FileName
=FileSize
=FileType
=GetActivePath
=GetActivePathAndName
=GetFileNameByNumber
=PathJoin
=PathSeparator
=ReadFile
=WriteFile

Math
=Ceil
=Floor
=InterpolateNumber
=InterpolatePercent
=Max
=Min
=ModFloat

Meta
=XlibCredits
=XlibDocumentation
=XlibVersion

Network
=Http
=ParseHtmlString
=SimpleHttp

Random
=BigRandBetween
=RandBetween
=RandBetweens
=RandBool
=RandomRange
=RandomSample

Regex
=RegexReplace
=RegexSearch
=RegexTest

StringManipulation
=CamelCase
=Capitalize
=CompanyCase
=CountLowercaseCharacters
=CountUppercaseCharacters
=CountWords
=DedentText
=EliteCase
=Formatter
=InSplit
=IndentText
=KebabCase
=LeftFind
=LeftSearch
=LeftSplit
=RemoveCharacters
=Repeat
=ReverseText
=ReverseWords
=RightFind
=RightSearch
=RightSplit
=ScrambleCase
=ShortenText
=SplitText
=Substr
=SubstrFind
=SubstrSearch
=TextJoin
=TrimChar
=TrimLeft
=TrimRight
=Zfill

StringMetrics
=Damerau
=Hamming
=Levenshtein

Utilities
=BigDec2Hex
=BigHex
=Dec2Hex
=Hex2Dec
=HideText
=HtmlEscape
=HtmlUnescape
=JavaScript
=Jsonify
=Len2
=SpeakText
=UuidFour

Validators
=CreditCardName
=FormatCreditCard
=IsCreditCard
=IsEmail
=IsIPFour
=IsMacAddress
=IsPhone
=IsUrl

File Module

This module contains a set of functions for gathering info on files. It includes functions for gathering file info on the current workbook presentation, document, or database, as well as functions for reading and writing to files, and functions for manipulating file path strings.


=CountFiles([filePath])

This function returns the number of files at the specified folder path. If no path is given, the current workbook path will be used.

Arguments

[(Optional) {String}: filePath]   ➔   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

{Integer}: Returns the number of files in the folder


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer
Warning!
This function includes the count for hidden files as well. For example, when a workbook is open, a hidden file for the workbook is created, so if you run this function in the same folder as the workbook and notice the file count is one higher than expected, it is likely due to the hidden file.

Examples

  • =CountFiles() -> 6
  • =CountFiles("C:\hello") -> 10

=CountFilesAndFolders([filePath])

This function returns the number of files and folders at the specified folder path. If no path is given, the current workbook path will be used.

Arguments

[(Optional) {String}: filePath]   ➔   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

{Integer}: Returns the number of files and folders in the folder


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer
Warning!
This function includes the count for hidden files and folders as well

Examples

  • =CountFilesAndFolders() -> 8
  • =CountFilesAndFolders("C:\hello") -> 30

=CountFolders([filePath])

This function returns the number of folders at the specified folder path. If no path is given, the current workbook path will be used.

Arguments

[(Optional) {String}: filePath]   ➔   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

{Integer}: Returns the number of folders in the folder


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer
Warning!
This function includes the count for hidden folders as well. Hidden folders are often prefixed with a . character at the beginning

Examples

  • =CountFolders() -> 2
  • =CountFolders("C:\hello") -> 20

=CurrentFilePath([filePath])

This function returns the path of the file specified in the file path argument. If no file path is specified, the current workbook, document, presentation or database is used. Also, if a full path isn't used, a path relative to the folder the workbook, document, presentation or database resides in will be used.

Arguments

[(Optional) {String}: filePath]   ➔   is a string path of the file on the system, such as "C:\hello\world.txt"


Returns

{String}: Returns the path of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =CurrentFilePath() -> "C:\my_excel_files\MyWorkbook.xlsx"
  • =CurrentFilePath("C:\hello\world.txt") -> "C:\hello\world.txt"
  • =CurrentFilePath("vba.txt") -> "C:\hello\world.txt"; Where "vba.txt" resides in the same folder as the workbook, document, presentation, or database this function resides in

=FileCreationTime([filePath])

This function returns the file creation time of the file specified in the file path argument. If no file path is specified, the current workbook, document, presentation or database is used. Also, if a full path isn't used, a path relative to the folder the workbook, document, presentation or database resides in will be used.

Arguments

[(Optional) {String}: filePath]   ➔   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

{String}: Returns the file creation time of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FileCreationTime() -> "1/1/2020 1:23:45 PM"
  • =FileCreationTime("C:\hello\world.txt") -> "1/1/2020 5:55:55 PM"
  • =FileCreationTime("vba.txt") -> "12/25/2000 1:00:00 PM"; Where "vba.txt" resides in the same folder as the workbook, document, presentation, or database this function resides in

=FileDrive([filePath])

This function returns the drive of the file specified in the file path argument. If no file path is specified, the current workbook, document, presentation or database is used. Also, if a full path isn't used, a path relative to the folder the workbook, document, presentation or database resides in will be used.

Arguments

[(Optional) {String}: filePath]   ➔   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

{String}: Returns the file drive of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FileDrive() -> "A:"; Where the current workbook resides on the A: drive
  • =FileDrive("C:\hello\world.txt") -> "C:"
  • =FileDrive("vba.txt") -> "B:"; Where "vba.txt" resides in the same folder as the workbook, document, presentation, or database this function resides in, and where the workbook resides in the B: drive

=FileExtension([filePath])

This function returns the extension of the file specified in the file path argument. If no file path is specified, the current workbook, document, presentation or database is used. Also, if a full path isn't used, a path relative to the folder the workbook, document, presentation or database resides in will be used.

Arguments

[(Optional) {String}: filePath]   ➔   is a string path of the file on the system, such as "C:\hello\world.txt"


Returns

{String}: Returns the extension of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FileExtension() = "xlsx"
  • =FileExtension("C:\hello\world.txt") -> "txt"
  • =FileExtension("vba.txt") -> "txt"; Where "vba.txt" resides in the same folder as the workbook, document, presentation, or database this function resides in

=FileFolder([filePath])

This function returns the path of the folder of the file specified in the file path argument. If no file path is specified, the current workbook, document, presentation or database is used. Also, if a full path isn't used, a path relative to the folder the workbook, document, presentation or database resides in will be used.

Arguments

[(Optional) {String}: filePath]   ➔   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

{String}: Returns the path of the folder where the file resides in as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FileFolder() -> "C:\my_excel_files"
  • =FileFolder("C:\hello\world.txt") -> "C:\hello"
  • =FileFolder("vba.txt") -> "C:\my_excel_files"; Where "vba.txt" resides in the same folder as the workbook, document, presentation, or database this function resides in

=FileLastModifiedTime([filePath])

This function returns the file last modified time of the file specified in the file path argument. If no file path is specified, the current workbook, document, presentation or database is used. Also, if a full path isn't used, a path relative to the folder the workbook, document, presentation or database resides in will be used.

Arguments

[(Optional) {String}: filePath]   ➔   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

{String}: Returns the file last modified time of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FileLastModifiedTime() -> "1/1/2020 2:23:45 PM"
  • =FileLastModifiedTime("C:\hello\world.txt") -> "1/1/2020 7:55:55 PM"
  • =FileLastModifiedTime("vba.txt") -> "12/25/2000 3:00:00 PM"; Where "vba.txt" resides in the same folder as the workbook, document, presentation, or database this function resides in

=FileName([filePath])

This function returns the name of the file specified in the file path argument. If no file path is specified, the current workbook, document, presentation or database is used. Also, if a full path isn't used, a path relative to the folder the workbook, document, presentation or database resides in will be used.

Arguments

[(Optional) {String}: filePath]   ➔   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

{String}: Returns the name of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FileName() -> "MyWorkbook.xlsm"
  • =FileName("C:\hello\world.txt") -> "world.txt"
  • =FileName("vba.txt") -> "vba.txt"; Where "vba.txt" resides in the same folder as the workbook, document, presentation, or database this function resides in

=FileSize([filePath], [byteSize])

This function returns the file size of the file specified in the file path argument, with the option to set if the file size is returned in Bytes, Kilobytes, Megabytes, or Gigabytes. If no file path is specified, the current workbook, document, presentation or database is used. Also, if a full path isn't used, a path relative to the folder the workbook, document, presentation or database resides in will be used.

Arguments

[(Optional) {String}: filePath]   ➔   is a string path of the file on the system, such as "C:\hello\world.txt"

[(Optional) {String}: byteSize]   ➔   is a string of value "KB", "MB", or "GB"


Returns

{Double}: Returns the size of the file as a Double


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • =FileSize() -> 1024
  • =FileSize(,"KB") -> 1
  • =FileSize("vba.txt", "KB") -> 0.25; Where "vba.txt" resides in the same folder as the workbook, document, presentation, or database this function resides in

=FileType([filePath])

This function returns the file type of the file specified in the file path argument. If no file path is specified, the current workbook, document, presentation or database is used.

Arguments

[(Optional) {String}: filePath]   ➔   is a string path to the file on the system, such as "C:\hello\world.txt"


Returns

{String}: Returns the file type of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer

Examples

  • FileType() -> "Microsoft Excel Macro-Enabled Worksheet"
  • FileType("C:\hello\world.txt") -> "Text Document"
  • FileType("vba.txt") -> "Text Document"; Where "vba.txt" resides in the same folder as the workbook, document, presentation, or database this function resides in

=GetActivePath()

This function returns the path of the folder of the office program that is calling this function. It currently supports Excel, Word, PowerPoint, Access, and Publisher.

Arguments

None


Returns

{String}: Returns a string of the current folder path


Examples

  • =GetActivePath() -> "C:\Users\UserName\Documents\"; Where the file resides in the Documents folder

=GetActivePathAndName()

This function returns the path of the file of the office program that is calling this function. It currently supports Excel, Word, PowerPoint, Access, and Publisher.

Arguments

None


Returns

{String}: Returns a string of the current path


Examples

  • =GetActivePathAndName() -> "C:\Users\UserName\Documents\XLib.xlsm"

=GetFileNameByNumber([filePath], [fileNumber])

This function returns the name of a file in a folder given the number of the file in the list of all files

Arguments

[(Optional) {String}: filePath]   ➔   is a string path to the file on the system, such as "C:\hello\world.txt"

[(Optional) {Integer = -1}: fileNumber]   ➔   is the number of the file in the folder. For example, if there are 3 files in a folder, this should be a number between 1 and 3


Returns

{String}: Returns the name of the specified file


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer
Warning!
This function includes hidden files as well. For example, when a workbook is open, a hidden file for the workbook is created, so if you run this function in the same folder as the workbook and notice the file count is one higher than expected, it is likely due to the hidden file.

Examples

  • =GetFileName(,1) -> "hello.txt"
  • =GetFileName(,1) -> "world.txt"
  • =GetFileName("C:\hello", 1) -> "one.txt"
  • =GetFileName("C:\hello", 1) -> "two.txt"
  • =GetFileName("C:\hello", 1) -> "three.txt"

=PathJoin(pathArray)

This function combines multiple strings into a file path by placing the path separator character between the arguments

Arguments

{Variant}: pathArray()   ➔   is an array of strings that will be combined into a path


Returns

{Variant}: Returns a string with the combined file path


Examples

  • =PathJoin("C:", "hello", "world.txt") -> "C:\hello\world.txt"; On Windows
  • =PathJoin("hello", "world.txt") -> "/hello/world.txt"; On Mac

=PathSeparator()

This function returns the path separator character of the OS running this function

Arguments

None


Returns

{String}:


Examples

  • =PathSeparator() -> "\"; When running this code on Windows
  • =PathSeparator() -> "/"; When running this code on Mac

=ReadFile(filePath, [lineNumber])

This function reads the file specified in the file path argument and returns it's contents. Optionally, a line number can be specified so that only a single line is read. If a full path isn't used, a path relative to the folder the workbook, document, presentation or database resides in will be used.

Arguments

{String}: filePath   ➔   is a string path of the file on the system, such as "C:\hello\world.txt"

[(Optional) {Integer}: lineNumber]   ➔   is the number of the line that will be read, and if left blank all the file contents will be read. Note that the first line starts at line number 1.


Returns

{String}: Returns the contents of the file as a string


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer
Warning!
This function may run very slowly when running it on large files. Also, for files that are not in text format (such as compressed zip files) this file contents returned will not be in a usable format.

Examples

  • =ReadFile("C:\hello\world.txt") -> "Hello" World
  • =ReadFile("vba.txt") -> "This is my VBA text file"; Where "vba.txt" resides in the same folder as the workbook, document, presentation, or database this function resides in
  • =ReadFile("multline.txt", 1) -> "This is line 1";
  • =ReadFile("multline.txt", 2) -> "This is line 2";

=WriteFile(filePath, fileText, [appendModeFlag])

This function creates and writes to the file specified in the file path argument. If no file path is specified, the current workbook, document, presentation or database is used. Also, if a full path isn't used, a path relative to the folder the workbook, document, presentation or database resides in will be used.

Arguments

{String}: filePath   ➔   is a string path of the file on the system, such as "C:\hello\world.txt"

{String}: fileText   ➔   is the text that will be written to the file

[(Optional) {Boolean}: appendModeFlag]   ➔   is a Boolean value that if set to TRUE will append to the existing file instead of creating a new file and writing over the contents.


Returns

{Boolean}: Returns a message stating the file written to successfully


Note
You can find the path of a file via Shift+RightClick -> Copy as Path; on a file in the Windows Explorer
Warning!
Be careful when writing files, as misuse of this function can results in files being overwritten accidently as well as creating large numbers of files accidently.

Examples

  • =WriteFile("C:\MyWorkbookFolder\hello.txt", "Hello World") -> "Successfully wrote to: C:\MyWorkbookFolder\hello.txt"
  • =WriteFile("hello.txt", "Hello World") -> "Successfully wrote to: C:\MyWorkbookFolder\hello.txt"; Where the Workbook resides in "C:\MyWorkbookFolder\"