Become an Σxpert at Σxcel.com

Standard VBA/Excel (99 of 1182)

	•	\\ ' network file prefix
	•	a filename can't use certain characters line "/" so change it if creating it from data
	•	A01 CopyToDocumentumSweep - copies files to a network directory
	•	ActiveSheet.PivotTables("pt1").PivotFields("coper_id").PivotFilters.Add Type:=xlTopCount, DataFiled:=... ' recorded
	•	ActiveWorkbook after opening an excel file
	•	ActiveWorkbook.SaveAs sFile
	•	ActiveWorkbook.SaveCopyAs sFile
	•	ActiveWorkbook.Worksheets("....").Autofilter.sort.sortfileds.clear ' clears a filters sorting ' recorded
	•	ActiveWorkbook.worksheets("sfile1").sort.sortfields.Add Key:=Range("N2"), SortOn:=xlSortOnValues, order:=xlAscending, DataOption=xlSortTextAsNumbers
	•	AddFinalSlash - udf - files
	•	AreTheyTheSame - internet function udf to check if 2 files are the same
	•	Attach a file to an email
	•	bOpenSeqFile(sFile,"A") ' "O" output "I" input
	•	ChDir - Files
	•	Close #x ' Close a seq file
	•	CompareTwoFiles() - udf
	•	CountFiles(sDir, sFilter)
	•	Create HTML files
	•	CreateDirs
	•	Creating a folder
	•	CSV file
	•	Dim FileSystemObject As New FileSystemObject
	•	Dim sFile$, sZipfile, test as string
	•	Dir - files - process a directory or check if a file exists
	•	dir(filename) = ""
	•	DownloadFileFromWeb(strURL, savetoPath) - udf using api
	•	DownLoadFileFromWeb(to,from) - udf
	•	Err = 53 ' file not found
	•	Error File in use
	•	File suffix - zip, xlsb, txt, xls, xlsx, xlsm, pdf, htm, html
	•	File wildcard character * or ?
	•	FileCopy from, to
	•	FileDateTime(sFileName)
	•	FileLen(sFile) - gets the length of the file
	•	FilesSystemObject.CopyFile sfrom, sTo
	•	FileToMD5Hex - internet - security
	•	FileToSHA1Hex - internet - security
	•	Freefile
	•	GenerateExcelFiles from a db - udf
	•	Get ini file - gsINIFile = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & ".ini"
	•	GetDirName(sFile)
	•	GetFileBytes - internet
	•	GetFileCreator - internet - used shell to get the name
	•	GetFileName(fullpath) - returns the file name without directory
	•	GetLastSlash(s) - File
	•	GetLatestVersion(sfilename) - get the file with the latest version
	•	GetNextVersion - udf - passed file and returns the next version of the file
	•	GetVersionForECMTemplates - goes thru a directory and get the last mod and size of each file
	•	How to write a function to open a zip file when the file sometimes is zip and sometimes xlsb
	•	If input file format changes
	•	Import module file and there is a duplicate routine (I named the routine with a suffix "_dbs" for the module
	•	Input file format is changed
	•	IsAutorun() - udf looks at the filename if has the name autorun then set flag to true
	•	kill - delete a file
	•	LoadData(sFile) - udf - loads a file's data to a string
	•	LoadFileToString(sFile) - udf
	•	Log2File udf
	•	logit2(s) ' writes data to sequential file ' udf
	•	Mkdir - files - creates a new directory
	•	Monthly Processes (MoM files)
	•	Open iniFile For Input As 1
	•	OpenDQFile - udf to open the Data Quality file from web/sharepoint and unzip it
	•	OpenExcelWorkbook(sFile)
	•	OpenFile() udf
	•	PPT.ActivePresentation.SaveAs sFile
	•	PPT.Presentations.Open sFile, ReadOnly:=msoTrue
	•	Print #x,string
	•	RenameFiles
	•	Run SQL and save output to a sequential file
	•	Run SQL from a file
	•	Run('c:\filename.xlsm'!Button2_click")
	•	RunProgram "http.." (File Associations need to set up)
	•	RunSQLFromFile - udf - db 🔝
	•	SaveAllAsPDFFile - Internet - https://howto-outlook.com/howto/saveaspdf.htm - Robert Sparnaaij
	•	SaveCopyAs
	•	SaveStringToFile - udf 🔝
	•	SaveStringtoFile string, "c:\xyz.txt" - saves a string to a file
	•	Scripting.FileSystemObject"
	•	SECDataAvailable - udf - db - checks for a condition (cob date from db > month-end data) and send email, generates a file
	•	SeqIO.bas
	•	set fso = CreateObject("scripting.filesystemobject")
	•	Set ppttemplate = PPT.Presentations.Open(sFile, ReadOnly = msoTrue
	•	SetAttr
	•	SetAttr sFilename, vbReadOnly
	•	Show directory via File Explorer - RunProgram "C:\directory_name"
	•	tab delimited file
	•	txt files
	•	udf - CreateMultipleDirectorys from a list (files)
	•	UnzipAFile(file,directory)
	•	Use file name to denote something like "-quick" isn't the full result
	•	VBA on 2 or more Excel files opened at the same time
	•	vFileLen(sFile) ' udf to create an excel function to get the filelength
	•	vlookup to external file - '[file name.xlsm]sheetname'!$C:$F
	•	Wait until a file appears
	•	Walk a file - get all files in a directory
	•	WorkBooks.Open sFileout, UpdateLinks:=False
	•	xla files
	•	xlt Files
	•	XML File format