- HubPages»
- Technology»
- Computers & Software»
- Computer Science & Programming»
- Programming Languages
Excel VBA Combine Text and Variable in String
One of the first things you will need find a need to do as you begin writing visual basic code is to combine text and string variables. In the previous module we learned to open workbooks in visual basic.
Imagine the following situation each day you want to open an excel workbook and import the data into a master workbook. The file name changes each day but the location remains the same.
Note: Any visual basic code that accepts string can also accept a combination of text and string.
How to Combine Text and String
Text and String our Combined in Visual Basic using the ampersand sign "&"
Let's take a look at the code for performing the task outline above assuming the file name is in cell "A1" less the extension.
Example Using String Variables
Sub test() Dim FileName As String FileName = Sheets("Sheet1").Range("A1") Workbooks.Open ("C:\Users\Jonathan\" & FileName & ".xlsx") End Sub
Example Without Using String Variables
Sub test() Workbooks.Open ("C:\Users\Jonathan\" & Sheets("Sheet1").Range("A1") & ".xlsx") End Sub
You Can Also Combine Multiple String Variables
Imagine you have the file location in cell A1 and the filename in Cell B1. You could use the code below to open your workbook.
Example Using String Variables
Sub test() Dim FilePath As String Dim FileName As String FilePath = Sheets("Sheet1").Range("A1") FileName = Sheets("Sheet1").Range("B2") Workbooks.Open (FilePath & FileName & ".xlsx") End Sub
Example Without Using String Variables
Sub test() Workbooks.Open (Sheets("Sheet1").Range("A1") & Sheets("Sheet1").Range("B1") & ".xlsx") End Sub