This repository gives access to various VBA scripts that I use with Excel to improve my productivity. The scripts presented here are in .vb format, and the codes can be easily copied and applied anywhere you would want to apply them. Comments are also present in the codes to aid understanding of the codes. if you want to collaborate on a project in Excel, you can contact me on israelbssy@gmail.com.
Please, feel free to contribute to the codes any way you can. Comment any script you would like for me to provide. Cheers!
For the Badges source
- Check for file existence in a folder
- Check for worksheet existence in a workbook
- Reset imported data
- Obtain file names from a folder
- Import or copy data from an external worksheet
- Obtain a list of all worksheets in a workbook
- Lookup data in a workbook with multiple criteria
- Rename worksheets dynamically
- Save a worksheet as pdf and send with email
- Save all worksheets as stand-alone CSV files
- Save all worsheets as stand-alone XLSX files
- Save a workbook as specific file
- Manage screen view
- Select a file from a folder
- Upload/Add data to a Database in SQL Server
Check for file existence in a folder. (View)
This script provides the user the option to search through a folder to check if the filenames present in a worksheet range are present in teh folder. The code can be applied as a stand-alone to keep track of a personal library, or it can also be used on a larger scale for document control in an organizational setting. The script requires the user to input the folder directory to be scanned through. A function is also present in the script that outputs a true or false. This can be used in simple cases. Click on "View" to access the code. Feel free to modify it for your personal use.
Check for worksheet existence in a workbook.(View)
When working with large files with multiple worksheets, it's easy to lose track of a worksheet especially when all you want to do is to check if the worksheet is present in the workbook before moving on to a new workbook. This script helps the user to quickly scan through the worksheets in a given workbook to confirm the existence of a given worksheet. With this, file searching becomes way easier especially for large files. You can expand this code to achieve more, like opening the specific worksheet with just one line of code. Click on "View" to access the code. Feel free to modify it for your personal use.
Reset imported data (View)
Following a data import from an external source, needs might arise to reset the data, clear contents and reload a new set of data especially when working with batch data or working on a template. Analysts may find this useful when building dashboards and there is need to reload data. Click on "View" to access the code. Feel free to modify it for your personal use.
Obtain file names from a folder (View)
This script gives the user the option to obtain the list if files present in a folder based on a file type. The list obtained from the code is printed on an excel worksheet already specified. This is especially useful when managing a library (large scale or personal). Beyond the management of a library,m this code can also be applied in document control when creating a master list of documents when it is not provided by default by the document management system already in place. Click on "View" to access the code. Feel free to modify it for your personal use.
Import or copy data from an external worksheet (View)
This script gives the user the ability to import data from an external excel source. The script opens a file selection window where the source file can be selected and the data is copied to the new worksheet and pasted as values. A box is also needed for the filename to be printed to. The imported data can be used by ana analyst to perform a form of routine analysis on a template, or create dashboards when working woth batch data. A script is also provided to clear the imported data and it can be accessed here. Click on "View" to access the code. Feel free to modify it for your personal use.
Obtain a list of all worksheets in a workbook (View)
A management technique for handling large files is provided with this script. Here, you can provide a list of all the worksheets present in a workbook. This is mostly applicable in an organizational context for reports logging and access management. The use of this script can help speed up the process of urgent reporting for multiple files. Click on "View" to access the code. Feel free to modify it for your personal use.
Lookup data in a workbook with multiple criteria (View)
Here, a function is provided to perform lookup on data more easily than the excel builtin lookup functions. Arguments are provided in the function and they are easy to use. Use this custom function in your workbook or you can attach iit to a larger block of code as you would like to use it.
Click on "View" to access the code. Feel free to modify it for your personal use.
Rename worksheets dynamically (View)
This script helps one to rename all the worksheets in your workbook based on cell value. When there are several worksheets in a workbook, renaming all of them one after the other could become a herculean task. However, with the extreme likelihood that there is a uniformity in the cell header or a specific defining cell common to all the worksheets, this script can easily attach names to all the worksheets bases on those defining cell values. The code block is expandable to carry more functionalities. Click on "View" to access the code. Feel free to modify it for your personal use.
Save a worksheet as pdf and send with email (View)
In this case, reporting and sending updates is optimized by giving a user the ability to automatically save a reporting sheet as a pdf file into a specified file location and sending an email to a specific recipient with the saved pdf file attached to the email on outlook. A use case of this script is when a routine update on a specific analysis is required by a manager or a set of staff. A window to select the folder is used in this case however, one can simply change the save folder to a constant folder path. A date cell is also advisable to have so it can be easy to track the files that have been saved. In an organizational context, a cell containing the document number can be used in place of "current date" for the filename. Click on "View" to access the code. Feel free to modify it for your personal use.
Save all worksheets as stand-alone CSV files (View)
Still working across multiple worksheets in a workbook, a script is provided to give the user the ability to save all the worksheets available in the workbook as separate csv files. A naming convention is also provided in the script to avoid the hassle of having to manually input the names of all the files as they are saved. Click on "View" to access the code. Feel free to modify it for your personal use.
Save all worksheets as stand-alone XLSX files (View)
Still working across multiple worksheets in a workbook, a script is provided to give the user the ability to save all the worksheets available in the workbook as separate csv files. A naming convention is also provided in the script to avoid the hassle of having to manually input the names of all the files as they are saved. Click on "View" to access the code. Feel free to modify it for your personal use.
Save a workbook as specific file (View)
With this script, one can quickly save a file in any specified file format/extension by the click of a button or an assigned shortcut. This script, though simple, has many applications including when building userforms or building dashboards in excel. This can also be extended to other Microsoft Apps that support VBA. Click on "View" to access the code. Feel free to modify it for your personal use.
Manage screen view (View)
When working with dashboards, it is sometimes desirable to view the worksheet in fulll screen format to reduce distractions and provide real estate for analysis. Two sub-routines are provided here that help the user to toggle full screen in excel. With this, presentations using excel becomes way easier and more space is provided for analysis elements. Click on "View" to access the code. Feel free to modify it for your personal use.
Select a file from a folder (View)
You can upload files in excel or upload files when working with a userform. This script can be attached to other functionalities in excel to achieve tasks that involve interacting with other files. The script opens a folder directory and the file types that are accessible by the user can be defined in the script. Click on "View" to access the code. Feel free to modify it for your personal use.
Upload Data to SQL Server (View)
When working with live data, it might not always be very convenient to do so with MS Excel. For this, you would need to upload your data to a database so a direct query of your data can be performed using any analysis tool. This script creates a connection with a/an SQL Server database, and following an event trigger, the data is uploaded or added to the database on connected table/tables. You can also write simple SQL quaries on your data in the VB- Environment. This is especially useful when working with DirectQuery in Power BI. Click on "View" to access the code. Feel free to modify it for your personal use.