How To Quote All Cells Of A CSV File

This Excel tutorial explains how to quote all cells of a CSV file. CSV stands for “Comma Separated Values”. If a CSV file is represented by a data table then each row of the table is a one record set. And columns of the table equivalent to the data fields. So inside one record set, each field is separated by commas. Then each record set has the same amount of fields. Hence the same number of commas. But the problem is sometimes field data also contains embedded line breaks or commas.

For an example let’s consider the below CSV file.

If you look at the data in the second field, you will see commas also included as the field data. Lot’s of applications use CSV as a data exchanging format. So you can understand what will happen if you exchange the above file between two different applications. To overcome this issue, quotes are used to separate the field data.

In this lesson I will teach you how to quote all the cell values of a CSV file. We are going to do this using a macro. Don’t know how to add macro to a workbook? Follow these easy steps. How to add a macro to Excel file

Now let’s look at how we can create this macro. Let’s name this subroutine as QuoteAllCellValues.

Sub QuoteAllCellValues()

End Sub

First we need to declare a few variables.

Dim WB As Workbook
Dim WS As Worksheet
Dim WS_LastRowNumber As Long
Dim WS_LastColumnNumber As Long
Dim i As Long
Dim j As Long

Next we can open the CSV file and assign it to the WB variable.

Set WB = Workbooks.Open("D:\Work\New Post\Data.csv")

Here we open the file using the full path. But this method may not be suitable for all the circumstances. Because if you use this method users need to access the code and change the path accordingly. I used this method to make this macro simple. But If you need a more user friendly application then use the file browsing method.

Next we can assign the activesheet to the WS variable.

Set WS = ActiveSheet

Then we need to find the boundaries of the CSV file. In other words finding the last row and the last column of the data set.

WS_LastRowNumber = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
WS_LastColumnNumber = WS.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

So now we know how many rows and columns we have in our CSV file. Therefore we can use nested For Next loop statements to loop through each cell of the CSV file.

For i = 1 To WS_LastRowNumber
     For j = 1 To WS_LastColumnNumber

     Next j
Next i

Outer For Next is used to loop through the rows and inner statement for the columns. Then inside the For Next loop, we can use below code to quote the values inside the cell.

WS.Cells(i, j).Value = Chr(34) & WS.Cells(i, j).Value & Chr(34)

So the entire nested For Next loop section should look like this.

For i = 1 To WS_LastRowNumber
     For j = 1 To WS_LastColumnNumber
          WS.Cells(i, j).Value = Chr(34) & WS.Cells(i, j).Value & Chr(34)
     Next j
Next i

And below is the full code of the subroutine.

Sub QuoteAllCellValues()

Dim WB As Workbook
Dim WS As Worksheet
Dim WS_LastRowNumber As Long
Dim WS_LastColumnNumber As Long
Dim i As Long
Dim j As Long

Set WB = Workbooks.Open("D:\Work\New Post\Data.csv")
Set WS = ActiveSheet
WS_LastRowNumber = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
WS_LastColumnNumber = WS.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

For i = 1 To WS_LastRowNumber
     For j = 1 To WS_LastColumnNumber
          WS.Cells(i, j).Value = Chr(34) & WS.Cells(i, j).Value & Chr(34)
     Next j
Next i

MsgBox "Completed!", vbInformation, ""

End Sub

Here we used ASCII code for the character ". However we can use " directly in the code instead of Chr(34). To do that you can use below line instead of “WS.Cells(i, j).Value = Chr(34) & WS.Cells(i, j).Value & Chr(34)”

WS.Cells(i, j).Value = """" & WS.Cells(i, j).Value & """"

Note that you need to use an additional " character than normal string concatenation. I.e. """" instead of """

Save Each Excel Worksheet To Separate CSV File Using VBA (Worksheet Name As File Name)

In the previous post we learnt how to convert an Excel sheet to CSV file. We discussed various techniques we can use when converting to CSV files such as how to change folder path, file name etc.

Convert an Excel File to CSV Using VBA

But there was one issue. If we use that method for an Excel file with multiple sheets, then the program will convert only the activesheet to a CSV file and the rest of the sheets will be deleted. So in this post I will show you how to convert each and every sheet of an Excel file to a separate CSV file. So stay focused.

First we need to give a name to our subroutine. Let’s name it as “ConvertEachSheetToCSV”

Sub ConvertEachSheetToCSV()

End Sub

We need a few variables to do this job. Let’s declare them as follows.

Dim WB As Workbook
Dim WS As Worksheet
Dim FolderPath As String
Dim FileName As String

Each sheet will be assigned to the WS variable while we loop through all the sheets of the Excel workbook. WB is to hold each workbook object until saved as a CSV file. FolderPath and FileName variables are to hold the path of the saving folder and name of each CSV file respectively.

We save all the CSV files inside one folder. So as the next step we assign the path of that folder to the FolderPath variable.

FolderPath = "D:\Work\New Post 2\Test"

Now we can loop through each sheet of the Excel file using a For Each ....Next statement.

For Each WS In Worksheets

Next WS

Then inside this For Each….Next statement we can get a copy of each sheet and save it as a CSV file.

WS.Copy

Set WB = ActiveWorkbook

FileName = WS.Name
WB.SaveAs FileName:=FolderPath & "\" & FileName, FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = False
WB.Close
Application.DisplayAlerts = True

We get the name of each sheet from WS.Name property and assign it to a variable “FileName”. Then we save the CSV file with that name. "Application.DisplayAlerts = False" is used to suppress the warning messages which occur when closing the CSV files. If "Application.DisplayAlerts = False" is omitted then Excel will show this warning message before closing each CSV file.

Then the user needs to intervene to close each file. However we can get rid of that by placing Application.DisplayAlerts = False before closing each file. So now we learnt how to save each worksheet as a separate CSV file with the sheet name as file name. Below is the complete code for your reference.

Sub ConvertEachSheetToCSV()

     Dim WB As Workbook
     Dim WS As Worksheet
     Dim FolderPath As String
     Dim FileName As String

     FolderPath = "D:\Work\New Post 2\Test"

     For Each WS In Worksheets

         WS.Copy

         Set WB = ActiveWorkbook

         FileName = WS.Name
         WB.SaveAs FileName:=FolderPath & "\" & FileName, FileFormat:=xlCSV, CreateBackup:=False
         Application.DisplayAlerts = False
         WB.Close
         Application.DisplayAlerts = True

     Next WS

End Sub

Suppose we ran the above macro for an Excel workbook which contains three sheets with names “Sheet1”, “Sheet2” and “Sheet3”.

Then it will create 3 separate CSV files (One file for each sheet) inside the given folder.

Overwriting existing files

But what will happen if there are CSV files with the same names inside the saving folder. For an example assume there is a CSV file called “Sheet1.csv” inside the saving folder already. Then Excel will display a message like this interrupting the running process.

So how do we solve this problem. Sometimes you may need to let the user decide whether he/she wants to overwrite the file or not. However if you need to overwrite the existing files without getting any warning message, change the placement of the “Application.DisplayAlerts = False” as follows.

Application.DisplayAlerts = False
WB.SaveAs FileName:=FolderPath & "\" & FileName, FileFormat:=xlCSV, CreateBackup:=False
WB.Close
Application.DisplayAlerts = True

Now the program will overwrite files without interrupting the running process.

Convert an Excel File to CSV Using VBA

In this post I will show you how to convert an Excel file to a CSV using VBA. This post explains various techniques you can use when converting to CSV. When we convert an Excel file to CSV, we can give a preferred name of our own or we can use the original sheet name as the file name. Also it is possible to save the file to a different directory by changing the folder path. Sometimes we need to overwrite the existing files as well. So in this post you can learn how to overwrite the existing CSV file automatically overcoming the warning message.

First let’s look at the simplest case scenario. Suppose we know the absolute file path of the new file we are saving. Then we can develop the subroutine as follows.

Sub ConvertToCSV()      Dim WB As Workbook      Dim FilePath As String      Set WB = ActiveWorkbook      FilePath = "D:\Work\Project Files\MyFile.csv"      WB.SaveAs Filename:=FilePath, FileFormat:=xlCSV, CreateBackup:=False End Sub

And if you have folder path in one variable and file name in another variable then you can slightly change the above subroutine like this.

Sub ConvertToCSV()      Dim WB As Workbook      Dim FolderPath As String      Dim FileName As String      Set WB = ActiveWorkbook      FolderPath = "D:\Work\New Post 4"      FileName = "Test File.csv"      WB.SaveAs FileName:=FolderPath & "\" & FileName, FileFormat:=xlCSV, CreateBackup:=False End Sub

When you save the file, if the folder contains a file with the same name the Excel will display a message like this.

If you select “Yes” then it will replace the existing file with the new file. Yet sometimes you may want to overwrite the file without the user's involvement. Fortunately there is a solution for that as well. You can use “Application.DisplayAlerts = False” to suppress that message. However, remember to set it to true after saving the file. Check below subroutine to understand how to use Application.DisplayAlerts

Sub ConvertToCSV()      Dim WB As Workbook      Dim FolderPath As String      Dim FileName As String      Set WB = ActiveWorkbook      FolderPath = "D:\Work\New Post 4"      FileName = "Test File"      Application.DisplayAlerts = False      WB.SaveAs FileName:=FolderPath & "\" & FileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False      Application.DisplayAlerts = True End Sub

In the above examples we gave our own name to the CSV file. Next let’s look at how to save the CSV file, giving sheet name as file name. To do that we need to get the file name using Activesheet.Name property. Then we can save the file using the same method.

Sub ConvertToCSV()      Dim WB As Workbook      Dim FolderPath As String      Dim FileName As String      Set WB = ActiveWorkbook      FolderPath = "D:\Work\New Post 4"      FileName = ActiveSheet.Name      WB.SaveAs FileName:=FolderPath & "\" & FileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False End Sub

Sometimes you may want to save the CSV file inside the same folder where the original Excel file is in. This also can be easily done with help of Workbook.Path property.

Sub ConvertToCSV_Ex5()      Dim WB As Workbook      Dim FolderPath As String      Dim FileName As String      Set WB = ActiveWorkbook      FolderPath = WB.Path      FileName = ActiveSheet.Name      WB.SaveAs FileName:=FolderPath & "\" & FileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False End Sub

Now we learnt how to convert an Excel file to a CSV file using VBA. But what if we have more than one sheet in the Excel file. If you run above macros for an Excel file with multiple sheets, macro will convert activesheet to the CSV file. Other sheets will be deleted automatically.

Also see

How To Quote All Cells Of A CSV File
Save Each Excel Worksheet To Separate CSV File Using VBA (Worksheet Name As File Name)

How to Format Label Text in VBA Forms (To Give Professional Look)

Labels are an inevitable control type used in VBA userforms. But when you create a label inside a form, default formatting of the labels are not appealing. So in this post I will show you how to change the formatting of the label text to get a more professional look. We can change various aspects of label text such as font type, font size and font style. Also few effects like underline are also available for label text. So now let’s look at how to change the text format of the labels. Let’s consider this sample userform.

As you can see these labels don't have an appealing look in the running mode.

I have 7 labels in this sample form. One for title and rest for the other fields. So I will apply two different text formats for these labels. I will add one format for the title label and a different format for the other six labels. To change the formatting of the text, first you need to select the label. So first let’s select the title label.

Next go to the “Font” field of the “Properties” window.

Click on the font field. Small grey color box with three dots will appear like this.

Next, click on that box. “Font” window will appear like this.

Now you can change the type, style and size of fonts from this window. Also you can add two effects to the text. Those two effects are strikeout and underline effects. Furthermore a sample text is displayed alongside with these options. So the format of that sample text will change in realtime to give you an idea about the formatting of the final result.

For the title label, I will choose the following options.

Also I will use capital letters for the title to give a better appearance. So the title label will look like this in the running mode.

Next step is to apply a suitable format for the six other labels. As we are going to apply the same format for all the labels, we can select all of them and apply the format once. There are two ways to select multiple controls inside a VBA form. First method is clicking on each control while pressing down the Ctrl key. Or else you can left click on the suitable place in the form and drag the mouse while pressing down the left mouse button to form a rectangle. This rectangle should overlap with all the controls you want to select. Once all the required controls are covered by the rectangle release the left mouse button. So I will follow the second method in this example.

Now we can use the same steps to open the Font window like we did for the Title label. For these six labels, I will select the following options.

This is how the form looks like in the running mode.

How to Add Textboxes to VBA Userforms

In the last post I showed how to add labels to a VBA userform. Click on the link below to read that post.

How to add labels to VBA Userforms

In this post I will show you how to add textbox controls to a VBA userform. Textboxes are also a very important control type used in the user interfaces. It is the control type used to get free text input from the users. Therefore textboxes are also a widely used control type in VBA userforms.

So now let’s look at how to add textboxes to a form. Showing below is the form we created in our last post.

To insert the textboxes, first we need to click on somewhere in the userform. When clicked on the userform, you should see the toolbox appear like this.

If you can’t see the Toolbox, then click on the “View” menu and select “Toolbox”.

Next, click on the TextBox icon in the Toolbox.

We have selected the TextBox control now.

So now we can create textboxes inside the userform. Take your mouse pointer to the Userform where you want to create the Textbox. You will notice that the shape of the mouse pointer has changed to something like this.

Left click on the mouse and drag it to form a small rectangle like below while pressing the left mouse button.

Release the left mouse button. A TextBox will be created like this.

Now we need five more textboxes for other labels.You can use the same steps above to create those other textboxes. Or else you can create five copies of the textbox you created. To create the copies, right click on the textbox and then select “Copy”.

Then right click again and select paste.

Do this pasting step consecutively for five times. Note that all the textboxes will be pasted in the same spot overlapping each other. Therefore you may won’t see five textboxes. So you can click on the textboxes and move each of them to where you want. Also you can use shortcut keys to Copy and Paste TextBoxes too. Use Ctrl + c to copy and Ctrl + v to paste. I moved the textboxes to infront of each label.

And this is how the form would look like in the running mode.

How to add labels to VBA Userforms

Labels are a very important control type available in VBA. Can’t imagine a VBA form which doesn’t have a label. Because labels are essential when designing user interfaces. So today let’s look at how to add labels to a VBA userforms.

Suppose that we need to create a userform with the following fields.

Order Number
Project Number
Starting Date
Attending Surveyor
Date of Pressure Test
Comments

So this is our VBA userform.

Now let’s see how to add our first label to this form. First click somewhere on the userform. You will see a Toolbox appear like this.

If your Toolbox is missing then follow the below steps.

Click on the “View” menu.

Then select Toolbox.

Now we have the Toolbox. Next we can insert a label to our VBA form. To do that, select the label icon on the Toolbox

Now left click on the form where you want to create the label and drag the mouse to create a small rectangle while pressing down the left mouse button.

Now release the left mouse button.

We have inserted a label to our form. Next we need to add 5 more labels to the form. There are two ways to add those 5 labels. You can either follow the same steps again for five times or create five copies of this inserted label. If you want to create a copy, then right click on the inserted label. And select “Copy”.

Next right click on the form and select “Paste”

Also you can use shortcut keys Ctrl + c to copy and Ctrl + v to paste instead of using the mouse. Usually, you may want to move the labels inside the form to format it correctly. If you want to move the label then left click on the label and drag it to the place where you want while clicking down the left mouse button.

So now we have added six labels to the form. Next we need to change the text of each label. To do that we need to go to the properties window. Select the first label and go to the caption field of the properties window.

Now replace the text “Label1” with the first field “Order Number”.

We can follow the same process for all the labels. So the final result would look like this.

Select the form and press the F5 key on the keyboard. This is how it will look like in the running mode.

Return a Result From a Custom VBA Function

In this post I will teach you how to return a result from a custom VBA function. You should have used in-built Excel functions such as Sum function, CountIf function etc. These functions can be used either in the Excel sheets or in the VBA projects. Like that we can also create our own custom VBA functions according to various requirements. These functions also can be used either in Excel sheets or VBA projects. So now let’s look at how to return a result from such a custom VBA function.

Let’s create a simple VBA function which doesn’t have any parameters. Suppose that we need to output a certain name when we call a function. We can create a very simple function for that as follows.

Function MyName() As String      MyName = "John Hurt" End Function

In the first line we have used the term“As String”. It is the data type of the result returned by the MyName function. However the data type of the value returned by the function is optional. So we can rewrite the above function like this as well.

Function MyName()      MyName = "John Hurt" End Function

In the above examples we used the name of the function again inside the same function to return the value. So that’s how you can output the result from the function. When you obtain the final result in the function then you need to write the name of the function again and assign that final result to it. In the above examples you see only one line inside the function. But in real life it will be very different. You will have lots of lines inside the function. But no matter how long your code is, you need to use the same technique to return the result from the function. So a real life function will look more like this.

Function MyName() As String      -----------------------------------      Lots of lines here      -----------------------------------      MyName = "John Hurt" End Function

Now we learnt how to output a result from a custom VBA function. But do you know how to use this kind of function inside a subroutine/macro? It is simple. You can call the function in one line. Following example subroutine shows you how to use the above created function inside a VBA Project.

Sub Test()      Dim x As String      x = MyName()      MsgBox x End Sub

This is what we get if we run the above code.

Also we can use this custom VBA function inside the Excel sheets as well.

Custom VBA function we created above has no parameters. Now let’s create another simple function which has some parameters. Assume that we want to input two values to a function and want to get the product of those two numbers. Yes we don’t need to create a function to do this. But I selected this example because our goal here is to understand the concept of outputting results from the functions. Once you understand it then you can apply it to any complex situation. So this is how you can create the function for the above requirement.

Function Multiplication(x As Double, y As Double) As Double      Multiplication = x * y End Function

Then we can use this function in subroutines as follows.

Sub Example()      Dim x As Double      x = Multiplication(2, 42)      MsgBox x End Sub

Program will show the result in a message box.