Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Create an Excel CSV File Having More Than 1048576 Rows of Data

We know that the Microsoft Excel application has a row limit of 1,048,576. So we can’t create a CSV file with more than 1048576 rows using Excel. Also if you receive a file with more rows than that, then Excel will only show the rows upto 1048576. So how do we create a CSV file with more than 1048576 rows using Excel? We can do that with the help of a well known text-editing program “Notepad”. Notepad is included in all the Microsoft Windows versions. So if you are using any Microsoft Windows version then you can create the file without installing a new software.

If you already have data in Excel files then you can skip the first few steps.

Sometimes you may require to create large CSV files for testing purposes. Suppose that we need to create a file with 2 million rows of data. It is not practical to type all those rows. So we can use a simple macro to write the 2 million rows of data. In this example I will write data only in 3 columns. First create a new Excel workbook. Then add this macro and run it.

Sub WriteData()

     Dim i As Long

     For i = 1 To 1048576
         Range("A" & i).Value = i
         Range("B" & i).Value = "Name " & i
         Range("C" & i).Value = "Address " & i
     Next i

     MsgBox "Completed!", vbInformation, ""

End Sub

So if you run above macro it will create a file like this. Top of the sheet would look like this.

Sample excel file top 14 rows

And the end of the sheet would look like this.

Sample excel file bottom most rows

So this is a type of simplest sample data we can create using a macro. However you can create more realistic sample data with the use of lists of first names, last names, Address line 1, Address line 2 and City,State. If you can create the above set of lists then you can use the Random function in VBA and combine those lists in many different ways in each row. Longer your lists, lower will be the duplicates.

So now we have created 1048576 rows out of 2000000. Next we can save this file as a CSV file. Let’s name it as “CSV 1.csv”

Select CSV file type from the Save as dialog box

When you click the “Save” button, Excel will show you a message like this.

Warning message we get when save as CSV file

Select “Yes”. Then close the CSV file. Now we need to create the rest of the rows in a different excel file as Excel doesn’t allow us to enter more than 1048576 rows in one sheet. Also CSV files can’t contain multiple sheets like .xlsx and .xls files. So let’s create a new blank workbook to add the remaining rows (rows from 1048577 to 2,000,000.). We can use the macro below to write remaining data to this new file.

Sub WriteRemainingData()

     Dim i As Long
     Dim Counter As Long

     Counter = 1
     For i = 1048577 To 2000000
         Range("A" & Counter).Value = i
         Range("B" & Counter).Value = "Name " & i
         Range("C" & Counter).Value = "Address " & i
         Counter = Counter + 1
     Next i

     MsgBox "Completed!", vbInformation, ""

End Sub

So if you run above macro, the remaining rows of data will be written to this new Excel workbook. So the top rows of the file would look like this.

Top most rows of second sample excel file

And the end of the rows would look like this.

Bottom most rows of second sample excel file

Save this file as a CSV file by following the same steps. Let’s name it as “CSV 2.csv”. Now we have two CSV files. Total rows of the both files equals 2 million. Next, let’s look at how we can create a CSV file with 2 million rows using these 2 files. First open the CSV 1.csv file with the Notepad.

Open first CSV file with Notepad

So it would look like this.

View CSV file in Notepad

Then open the CSV 2.csv file also with the Notepad. Copy all the data from the second file and paste them on the end of the CSV 1.csv file. Now save the CSV 1.csv file. You have 2 millions of rows in your CSV 1.csv file. However you will not be able to view all the data from the Excel application. It will show this message if you open the file in Excel.

Warning message we get when open file with more than 1048576 rows

So if you open it with Excel then it will show the data upto row 1048576. But you will be able to view all the rows from the other applications which are capable of handling higher numbers of rows.

Extract Data From a .msg File to an Excel Sheet

Microsoft outlook is a useful software system from Microsoft. People primarily use it to manage their emails. In addition to managing emails, Outlook software can be used to manage tasks, take notes and for few other functions. Outlook software also gives an option to save emails to local drive. So you can save your emails to your local drive as a .msg file. In this post I will explain to you how to extract information from such a .msg file to an Excel sheet using VBA. This will be really helpful if you want to extract information from lots of emails. Assume you want to get the following information from the email.


You can use the following subroutine to extract the above information.

Sub ExtractInfo()

Dim WS As Worksheet
Dim i As Long
Dim olApp As Outlook.Application
Dim mailDoc As Outlook.MailItem

Set WS = Worksheets("Sheet1")
Set olApp = CreateObject("Outlook.Application")
Set mailDoc = olApp.Session.OpenSharedItem("C:\Users\EVS\Documents\email test\test.msg")
WS.Range("A2").Value = mailDoc.SentOn
WS.Range("B2").Value = mailDoc.Sender
WS.Range("C2").Value = mailDoc.SenderEmailAddress
WS.Range("D2").Value = mailDoc.Body
WS.Range("E2").Value = mailDoc.To
WS.Range("F2").Value = mailDoc.Attachments.Count

If mailDoc.Attachments.Count > 0 Then
     For j = 1 To mailDoc.Attachments.Count
         AttachmentNames = AttachmentNames & ", " & mailDoc.Attachments.Item(j).DisplayName
     Next j
End If

AttachmentNames = Replace(AttachmentNames, ",", "", 1, 1)
WS.Range("G2").Value = AttachmentNames

mailDoc.Close False
olApp.Quit
Set mailDoc = Nothing
Set olApp = Nothing

End Sub

Remember to add reference to “Microsoft outlook object library”. Otherwise you will get an error like this.

Also replace the file path of the .msg file with your file path. Here is a sample result of the above macro.

In the above code, I have given the path and file name inside the code like this.

Set mailDoc = olApp.Session.OpenSharedItem("C:\Users\EVS\Documents\email test\test.msg")

However if you want you can input the path and file name from the Excel sheet as well.

Then we can use this code instead of the above line.

Dim SPathAndFileName As String

SPathAndFileName = WS.Range("B2").Value
Set mailDoc = olApp.Session.OpenSharedItem(SPathAndFileName)

Next I will explain about the code related to the attachment names.

First we check whether there is at least one attachment.

If mailDoc.Attachments.Count > 0 Then

If there are attachments then we use the “For Next” loop to go through each attachment and get their names.

For j = 1 To mailDoc.Attachments.Count
     AttachmentNames = AttachmentNames & ", " & mailDoc.Attachments.Item(j).DisplayName
Next j

Above For Next loop generates an additional comma at the beginning. So we can remove that using the following line.

AttachmentNames = Replace(AttachmentNames, ",", "", 1, 1)

In the above code we have used the “Early binding” method. That’s why we need to add reference to “Microsoft outlook object library”. However if you don’t like to add reference to the type library then you can develop the code using the “Late binding” technique as follows.

Sub ExtractInfo()

Dim WS As Worksheet
Dim i As Long
Dim olApp As Object
Dim mailDoc As Object

Set WS = Worksheets("Sheet1")
Set olApp = CreateObject("Outlook.Application")
Set mailDoc = olApp.Session.OpenSharedItem("C:\Users\EVS\Documents\email test\test.msg")
WS.Range("A2").Value = mailDoc.SentOn
WS.Range("B2").Value = mailDoc.Sender
WS.Range("C2").Value = mailDoc.SenderEmailAddress
WS.Range("D2").Value = mailDoc.Body
WS.Range("E2").Value = mailDoc.To
WS.Range("F2").Value = mailDoc.Attachments.Count

If mailDoc.Attachments.Count > 0 Then
     For j = 1 To mailDoc.Attachments.Count
         AttachmentNames = AttachmentNames & ", " & mailDoc.Attachments.Item(j).DisplayName
     Next j
End If

AttachmentNames = Replace(AttachmentNames, ",", "", 1, 1)
WS.Range("G2").Value = AttachmentNames

mailDoc.Close False
olApp.Quit
Set mailDoc = Nothing
Set olApp = Nothing

End Sub

So now you don’t need to add reference to “Microsoft outlook object library”.

.frx File

.frx file is a file type which can be opened with softwares like FoxPro. So what’s the relation of .frx file with Excel VBA? You should know the relationship if you have ever exported or imported a form from or to your VBA projects. When we export forms from VBA projects it creates two files of two different types. One file with .frm extension and another with the .frx extension. .frm file contains the codes of the form. The .frx file includes the elements of the form file. Especially the graphical elements. Suppose we have a form named “frmMain” in our VBA project. So if we export this form, two files will be created. One file would be frmMain.frm and other file would be frmMain.frx

VBA project with a form

As you can see in the properties window, the name of the form is frmMenu.

.frm and .frx files are created inside the folder

In the above example I have named the form as frmMenu. You may think why I have named it as “frmMenu” instead of “Menu”. That’s because, in conventional naming method we use “frm” word in front of the sensible name. If you have a userform with name “Userform1'' then two files would be Userform1.frm and Userform1.frx

Now let’s look at the importance of the .frx file in the form importing. Assume I want to import that exported form to this new VBA project.

New VBA project

Do we need to import both .frm and .frx files? We can’t select both the files and import them together. You can only select .frm file and import it. However, the .frx file needs to stay in that same folder when you import the .frm file. This is what will happen if the .frx file is missing when you import the form.

Error message due to absence of .frx file
Second error message
Third error message due to absence of .frx file

Then a new file will be created with .log extension in the same folder. In this case frmMain.log

.log file is created inside the form
Content of a .log file

So if you want to import a form to your VBA project, you should have both .frm and .frx file in the same folder.

Contact Form

Name

Email *

Message *