Excel Data Access via VBA

Once proficient in basic Excel operations like adding rows and functions, explore VBA. Excel spreadsheet inputs often entail extensive datasets, requiring efficient management and precise manipulation with VBA code. Expedite data access and computation support with the following code snippets.

Guide: Excel Data Access via VBA

Step 1: To enable the Developer feature in Excel, navigate to File > Options:

Next, go to the Customize Ribbon tab and check the Developer option, then click OK:

Step 2: Go to Developer > Visual Basic to open the code editor:

Step 3: When the editor opens, right-click on the current folder in the Project and select Insert > Module:

Now, double-click on module 1. Then press the Ctrl + G key combination to open the Immediate editor.

Step 4: Specific ways to access data in Excel spreadsheets:

Accessing the Workbook (Excel File)

To display the name of the currently open Excel file, enter the code Debug.Print ActiveWorkbook.Name into Immediate and press the Enter key:

To display the directory path of the folder containing the open Excel file, enter the code Debug.Print ActiveWorkbook.Path into Immediate and press the Enter key:

To display both the folder path and the name of the open Excel file, enter the code Debug.Print ActiveWorkbook.FullName into Immediate and press the Enter key:

Accessing the Worksheet (Spreadsheet)

To display the name of the open worksheet, enter the code Debug.Print ActiveWorkbook.ActiveSheet.Name into Immediate and press the Enter key:

To display the name of a specific worksheet in the currently opened Excel file, you can enter the following code snippet into the Immediate window: Debug.Print ActiveWorkbook.Worksheets(1).Name . Here, I want to retrieve the name of the first worksheet, which is named Mytour. So, the number within the parentheses in the code snippet will be 1. You can input the corresponding index number if you want to get the names of other worksheets.

Accessing Range (Data Range) in an Excel File

To select the Excel cell A5 within a worksheet, you can enter the code Range('A5').select into the Immediate window and then press Enter:

To select multiple Excel cells A5 and A8 within a worksheet, you can enter the code Range('A5, A8').select into the Immediate window and then press Enter:

To select the range containing Excel cells with vertices at A5 and B6 within a worksheet, you can enter the code Range('A5:B6').select into the Immediate window and then press Enter:

To select the range containing Excel cells with vertices at A5 and B6, and the range containing Excel cells with vertices at A8 and B10 within a worksheet, you can enter the code Range('A5:B6, A8:B10').select into the Immediate window and then press Enter:

Accessing Cells in Excel

Suppose in your worksheet, there are Excel cells from A5 to A10 with values as shown. To retrieve the value of Excel cell A7 within the worksheet, you can enter the code Debug.Print Cells(7,1).value into the Immediate window and then press Enter:

To select all Excel cells within a worksheet, you can enter the code Cells.Select into the Immediate window and then press Enter:

Entering Data into Excel Cells

To fill all Excel cells from A5 to A10 within a worksheet with the character A, you can enter the code Range('A5:A10').Value = 'A'into the Immediate window and then press Enter:

To fill all cells in column C within a worksheet with the character C, you can enter the code Columns('C').Value = 'C' into the Immediate window and then press Enter:

To assign the name of the opened Excel file to cell B5 within a worksheet, enter the code Range('B5').Value = Activeworkbook.Name into the Immediate window and press Enter:

To assign the directory path of the folder containing the opened Excel file to cell B6 within a worksheet, enter the code Cells(6,2).Value = Activeworkbook.Path into the Immediate window and press Enter:

To assign both the directory path and the name of the opened Excel file to cell B7 within a worksheet, enter the code Cells(7,2).Value = Activeworkbook.FullName into the Immediate window and press Enter:

To assign the name of the active worksheet to cell B8 within a worksheet, enter the code Cells(8,2) = Activeworkbook.ActiveSheet.name into the Immediate window and press Enter: