Save RTF document as word

Convert RTF document To Word

Sub Open_n_Save()

sFile = Dir$("C:\ReFormated\*.rtf", vbNormal)
Do Until Len(sFile) = 0
Documents.Open "C:\ReFormated\" & sFile
ActiveDocument.SaveAs "C:\ReFormated\" & sFile & ".doc", wdFormatDocument
ActiveDocument.Close False
sFile = Dir$
Loop


End Sub

See also :

Excel VBA - 1004 -- The file could not be accessed

Save and Reopen all Workbooks (Excel VBA)

Save copy of the workbook

SaveAs Dialog - Controlled Save

Using Excel Functions in VBA

Excel has several useful functions, which can be implemented in VBA code using the Evaluate Method

Evaluate Method converts a Microsoft Excel name to an object or a value


For example , the following method adds the values of the cells A1 to A6.

Sub Evaluate_Usage()

Dim lSum As Long

lSum = Evaluate("=SUM(A1:A6)")

End Sub

USe of evaluate has reduced a roundabout way of looping thru the cells and summing it up.

What next.. use the sum if, count if functions and evaluate!!!

Display text in a multiple lines

To display several lines of text in a text box, using vbcrlf at the end of each line

Set MultiLine property to True either in the Properties window

alternatively this can be set in runtime like:

TextBox1.MultiLine = True

Display Time Dynamically

Display Time Dynamically in Form using Time/Now Function

Add a Timer Control to your form and set the timer interval.

In the Timer event for that timer set (refresh) the time

Private Sub Timer1_Timer()

Label1.Caption = Date & " " & Time

End Sub

In the Form Load Event Intialize the interval
Private Sub Form_Load()
Timer1.Interval = 1000
End Sub

Execute Excel Macro in All Files

You can use the following code to execute the macro in all Excel (.xls) files under a given folder. You can tweek a bit if necessary

Sub Exec_Macro_For_All()

Dim sPath As String
Dim sFile As String
Dim sDir As String
Dim oWB As Workbook
Dim i1 As Long
Dim iMax As Long

On Error GoTo Err_Clk

sPath = "" ' Your Path
If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"

sDir = Dir$(sPath & "*.xls", vbNormal)
Do Until LenB(sDir) = 0
Set oWB = Workbooks.Open(sPath & sDir)
Exec_MyMacro() ' Your MAcro here
oWB.Save
oWB.Close False
sDir = Dir$
Loop

Err_Clk:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub

Cheers
Shasur

Get User Name using Split Function

This is a simple way of using the split function to get the user name

Sub Get_EMail_User_Name()

sEmail = "joe@gmail.com"

arTemp = Split(sEmail, "@")

sDomain = arTemp(LBound(arTemp))
End Sub

Cheers
Shasur

Get Domain Name from eMail (Split Function)

This is a simple way of using the split function to get the domain name

Sub Get_Domain()

sEmail = "joe@gmail.com"

arTemp = Split(sEmail, "@")

sDomain = arTemp(UBound(arTemp))
End Sub

Cheers
Shasur

Disabling Excel events

In some cases, we would have written some code in Worksheet_SelectionChange, Workbook_SheetActivate, etc and do not want them to be fired. This will be used if you have some event to be fired when the user enters a value, but do not want them if the value is from VBA code etc. In that case switch off the Application.EnableEvents and turnit on after the

process.

Sub Donot_Fire_Events()

Application.EnableEvents = False

' Coding to skip these events

Application.EnableEvents = True

End Sub

Excel Not Calculating Formula

If Excel does not re-calculate the formula. That is, if the cell is not refreshed inspite of newvalues, the Calculation might not be set as automatic. To set the calculation as automatic, use the following:

Sub Switch_On_XL_Calculation()

Application.Calculation = xlCalculationAutomatic

End Sub

Transferring array to Excel range

Is there any method to transfer the contents of the array to an Excel Range.. Most often programmers used to loop thru the array and put it to the Excel (of course with another loop). Here is a simple code that will transfer the array contents to Excel. Many thanks to Sharmila Purushotaman for this thoughful article

Sub Sheet_Fill_Array()
Dim myarray As Variant
myarray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Range("A1:A10").Select
Range("A1:A10").Value = Application.WorksheetFunction.Transpose(myarray)
End Sub

' Keywords : ARRAY to Excel, Transferring array to range

Character to ASCII

This function will print the ASCII values for corresponding characters in the first and second column respectively

Sub Convert2_Asc_Values()

For i1 = 1 To 256

Cells(i1 + 1, 3).Value = Asc(Cells(i1 + 1, 2).Value)

Next i1
End Sub



AddMe - Search Engine Optimization

ASCII to Character

There have been many times that we want to know the ASCII values. Ths function will print the character codes for the ASCII values in the first and second column respectively

Sub Print_Asc_Values()
For i1 = 1 To 255
Cells(i1 + 1, 1) = i1 Cells(i1 + 1, 2) = Chr(i1) Next i1
End Sub

Cheers
Shasur