KrishTalk.com

How to create MS Word document automatically with Text file

This VBA code will help you to import data from a text file and insert it into the MS word document. This is useful when you have to create a report or formats in MS Word format, reading the data from a text file. The VBA code can be extended import extra data from the text file, and also you can add additional format as per your needs.

Please note that VBA macros can cause harm to your computer. First, open them in a disabled mode. To disable macros, to Developer tab, select macro security and then select Macro settings on the left side pane. In the right pane, select the second radio button, 'Disable all macros with notification'. The second option is better because it will notify you if there are any macros associated with your file. So you will not miss any document features that may be available after running a macro. But you can decide whether to allow the macros to run or not.

Please click on the below link to download the MS Word Template file. You can also download the text file used to import the data. I remind you that this is a sample file. You may have to modify the VBA code according to your requirement. You may have to rename the input file.

Please leave a comment below if you have any concerns.
MS word template

The VBA Code
-------------------
Sub Document_Open()
If (ActiveDocument.Name = "Template.docm") Then
With ActiveDocument

On Error Resume Next
.Variables.Add Name:="1", Value:="1"
.Variables.Add Name:="2", Value:="2"
.Variables.Add Name:="3", Value:="3"
.Variables.Add Name:="4", Value:="4"
.Variables.Add Name:="5", Value:="5"
.Variables.Add Name:="6", Value:="6"

Dim ReadData As String
Dim myarray() As String

Open ActiveDocument.Path & "\text.txt" For Input As #1

Do Until EOF(1)
Line Input #1, ReadData
If Not Left(ReadData, 1) = "*" Then
myarray = Split(ReadData, ",")
End If

Loop

Close #1

i = 1
For Each f In myarray
.Variables(i).Value = f
i = i + 1
Next f
.Fields.Update
End With

With ActiveDocument
strFileName = "Agreement Between " & myarray(5) & " and " & myarray(0)
strPath = .Path

.SaveAs2 (strPath & "\" & strFileName)
'.Close SaveChanges:=wdDoNotSaveChanges
Application.Quit SaveChanges:=wdDoNotSaveChanges
'.Application.
End With
End If
End Sub
-------------------

MS Excel Currency converter VBA code

To convert numbers into text, there is no builtin function in MS Excel as of now. Using the below VBA code you can overcome this issue. There are situations like writing a cheque leaf, printing a rent agreement, or writing contract documents that may require printing the transaction amount in the text. Make use of this code to convert numbers into text automatically.

Copy and paste this VBA code into your Excel VBA editor module. Or Insert an empty module and paste this code.

Attribute VB_Name = "Module1"
'Attribute VB_Name = "Module2"
' **** Author : Krishna S
' **** Tittle : Converting Hindu Arabic Currency(Indian System) to Words
' **** Copyright Owner : Krishna S
' **** Description : This utility converts currencies in Indian numbering system to words.
' **** Limitations : Converts only upto 10,00,00,000( Ten Crores)
Function ConvertCurrencyToEnglish(ByVal MyNumber)
Dim Temp
Dim Rupees, Paise
Dim DecimalPlace, Count
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Lac "
Place(4) = " Core "
' Place(5) = " Hundred Core "
' Convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumber))
' Find decimal place.
DecimalPlace = InStr(MyNumber, ".")
' If we find decimal place...
If DecimalPlace > 0 Then
' Convert Paise
Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
Paise = ConvertTens(Temp)
' Strip off Paise from remainder to convert.
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
If Count = 1 Then
Temp = ConvertHundreds(Right(MyNumber, 3))
If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) > 3 Then
' Remove last 3 converted digits from MyNumber.
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Else
' Convert last 3 digits of MyNumber to English Rupees.
If Len(MyNumber) = 1 Then
Temp = ConvertDigit(MyNumber)
Else
Temp = ConvertTens(Right(MyNumber, 2))
End If
If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) >= 3 Then
' Remove last 3 converted digits from MyNumber.
MyNumber = Left(MyNumber, Len(MyNumber) - 2)
Else
MyNumber = ""
End If
Count = Count + 1
End If
Loop
' Clean up Rupees.
Select Case Rupees
Case ""
Rupees = ""
Case "One"
Rupees = "One Rupee"
Case Else
Rupees = Rupees & " Rupees"
End Select
' Clean up Paise.
Select Case Paise
Case ""
Paise = ""
Case "One"
Paise = " And One Cent"
Case Else
Paise = " And " & Paise & " Paise"
End Select
ConvertCurrencyToEnglish = Rupees & Paise
End Function
Private Function ConvertHundreds(ByVal MyNumber)
Dim Result As String
' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then Exit Function
' Append leading zeros to number.
MyNumber = Right("000" & MyNumber, 3)
' Do we have a hundreds place digit to convert?
If Left(MyNumber, 1) <> "0" Then
Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
End If
' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & ConvertTens(Mid(MyNumber, 2))
Else
' If not, then convert the ones place digit.
Result = Result & ConvertDigit(Mid(MyNumber, 3))
End If
ConvertHundreds = Trim(Result)
End Function
Private Function ConvertTens(ByVal MyTens)
Dim Result As String
' Is value between 10 and 19?
If Val(Left(MyTens, 1)) = 1 Then
Select Case Val(MyTens)
Case 1: Result = "One"
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
' .. otherwise it's between 20 and 99.
Select Case Val(Left(MyTens, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
' Convert ones place digit.
Result = Result & ConvertDigit(Right(MyTens, 1))
End If
ConvertTens = Result
End Function
Private Function ConvertDigit(ByVal MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select
End Function

How to merge 2 or more MS word documents

How to merge 2 or more MS word documents. You can do this by manually copying the first file and pasting on to the second file. But if you have too many files to merge and if you have to do it repeatedly, it's better to have automated setup.

Below macro will help you to merge 2 documents. You can edit the VBA code and add more files if you want. After merging, it will save as a new document. By default, it will save the new merged file under My Documents. If you want the new files to be saved under a specific location, you can mention that the file name section

Sub Merge_2_files()

' Merge 2 files and save as a new document

NewFileName = "NewFile_" & Minute(Now) & "_" & Second(Now) & ".docx"

Selection.EndKey Unit:=wdStory

Selection.TypeParagraph

Selection.InsertFile FileName:="C:\Users\inksanjeev\Desktop\A folder\Specifications.docx", Range:="", _

ConfirmConversions:=False, Link:=False, Attachment:=False

ActiveDocument.SaveAs2 FileName:=NewFileName, FileFormat:= _

wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _

:=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _

:=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _

SaveAsAOCELetter:=False, CompatibilityMode:=15

End Sub

Download the files
File 1

File 2

How to get Unlimited Jio preview offer with Redmi Note 3

If you follow these steps carefully you will get the unlimited offer with Jio on your Redmi note 3.

Get the old apk of MyJio app from below link

Install MyJio and then all 11 applications from the app store do not open any application

Turn off your data/wifi

Open the MyJio app, then click on MyJio again.

You will see 3 options Get Jio SIM, Sign In and Sign Up.

Select Sign in

Enter your Jio ID and Password. You can generate your Jio ID by linking your Jio number with email ID on Jio.com.

Do not click on Sign In. Just enter the ID and Password.

Turn on the data/wifi

Click on Sign In.

You have to try a couple of times to get to the next screen where it will ask you to submit.

Once you get the submit button click that and that's all. Your unlimited offer is credited.

Enjoy unlimited Digital life for 3 months. Please your story on the comments below.

https://drive.google.com/open?id=0B4UB3eOhhY26eFdzVWViZU5EWGM

How to get Jio Unlimited Preview offer with Samsung Galaxy mobiles

Last week, Reliance had extended its Jio preview offer for Samsung galaxy users. A lot of people are itching to get one with a preview offer. This offer gives you unlimited 4g data, unlimited voice, and unlimited SMS for 90 days. I am one of the lucky guys to get a new Jio SIM with my Samsung Galaxy A5 2016. I got it on Saturday evening at 7 O’clock and the guy at Reliance digital said it will be activated by Monday afternoon. But I got it activated on Sunday, 2 O’ clock itself. That was very quick.

How to get the preview offer with Samsung galaxy mobiles

First, you need to install the Jio app called MyJio. As soon as you open the app, on the very first screen, you will see Get Jio Preview Offer on top of the screen. Click this and proceed with the other steps.

In the first screen after clicking Get Jio Preview Offer, you will see the details for the offer and terms and conditions. Click "Agree and get Jio Offer".

Next, you will have to check the eligibility. This is nothing but a network availability in your area. Select your state and city. Click Next.

In this step, you have to generate the bar code. Show this bar code to the Reliance Digital salesperson. He will take this and issue a new SIM to you. You have to give him a passport size photo and an address proof and you have to sign the CAF - Customer Application Form at some 2-3 places. Also, you need to give your existing mobile number, to which you will get an activation message. That’s all from your end.

Wait for the message from Jio on your registered mobile number. Once you get the message, call customer care from your registered mobile number, not from your Jio number. Your Jio number may not be activated yet. Talk to the customer care executive, he will guide you with what you need to do. Once activated, wait for some time. Your Jio SIM will get registered to the Jio 4G network.

Once all the activation process is complete you will get 2GB of 4g data, 100 minutes of voice, and 100 SMS. This is part of your New Prepaid FRC. A lot of people think that this is what we get from Jio and we won't get the unlimited offer. This is wrong. To get the unlimited offers you have one more step to do.

How to get unlimited offer

To get an unlimited offer, you have to install some application like Jio Play, Jio On Demand, Jio Beats and Jio Money. This is mandatory for Samsung users. Not sure about LYF mobile users. You can do this by clicking on Get Unlimited Offer or manually installing the above said apps. After installing these apps, you will see a Submit button at the bottom of the screen. Click it and refresh the MyJio App. Or just re-launch the app. By now you will see a message called "Congratulations, you have activated your unlimited offer".

One important warning, You have to use the SIM with the same mobile you have activated it with. Otherwise, you may lose your unlimited offer.

Enjoy the unlimited Digital life for 90 days uninterrupted.

How to create MS Word document automatically by inserting text file content to the word document

There are many situations you may want to automate the MS Word document creation. In my previous posts, I have explained how to automate document generation using VBA. It was to create a new document. Every time you run the VBA code, it will create a new MS Word file.

How to create a template in MS Word

Now, I will explain how to insert a text file into the document as is at a specified location into an existing document. This is very useful if you are modifying an existing document. The main feature of this VBA is, it can insert new content at a specific location. That means you can insert new content into any part of the existing document, not just append the new content at the end of the file. The first step is to create a bookmark in the word document where the text file content will be pasted. Then, open the text file using VBA code and paste the content at the bookmark. Save the file with a different name. Before saving optionally you can remove the bookmark.

Below video shows details steps to insert a text file into MS word document

How to make a copy of current document MS Word automation with dynamic fields

How to make a copy of the current document using VBA in MS Word

It makes use of the dynamic fields to create a new document with different values. The MS word document can be used as a template file. Using this template you can create a new document without much effort. Click on the button and fill the necessary information. And the document will be created at the specified location. Edit the macro and modify the hardcoded values as per your needs. Change the location of the target file. The new file will be created and stored at this location

This video explains the detailed steps to create and use this template.

Part 1: how to use dynamic fields in ms word
https://www.youtube.com/watch?v=MlzyKniRckQ

you can download the ms word file here

https://drive.google.com/open?id=0B4UB3eOhhY26ZWhySlpROWlidEU

Contact Form