ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Excel VBA Combine Text and Variable in String

Updated on August 30, 2012

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

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article