Creative Blog

Étiquette : conversion

  • Convert an Excel file into a Word document

    Objectives of the Code:

    • Copy data from Excel (e.g., a range of cells) into Word.
    • Format the content in Word.
    • Generate a Word file from Excel.

    VBA Code to Convert Excel to Word

    Sub ConvertExcelToWord()
    ' Declare necessary objects
    Dim objWord As Object
    Dim objDoc As Object
    Dim ws As Worksheet
    Dim cell As Range
    Dim RangeToCopy As Range
    Dim i As Long, j As Long   
    ' Create a new Word application instance
    On Error Resume Next
    Set objWord = CreateObject("Word.Application")
    On Error GoTo 0   
    ' If Word is not opened, start it
    If objWord Is Nothing Then
        MsgBox "Word could not be launched", vbCritical
        Exit Sub
    End If   
    ' Make Word visible (optional)
    objWord.Visible = True  
    ' Create a new Word document
    Set objDoc = objWord.Documents.Add   
    ' Reference the current Excel worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet's name   
    ' Define the range of cells to copy (e.g., A1:B10)
    Set RangeToCopy = ws.Range("A1:B10") ' Replace with your desired range   
    ' Copy the range of cells into Word
    RangeToCopy.Copy   
    ' Paste the cells as a table in the Word document
    objDoc.Content.Paste   
    ' Optional: Format the table in Word
    With objDoc.Tables(1)
        .AutoFitBehavior (2) ' Automatically adjust column width
        .Style = "Table Grid" ' Apply a predefined table style
        .Rows.Alignment = 1 ' Align the rows to the center
    End With   
    ' Add a title to the Word document
    objDoc.Paragraphs.Add
    objDoc.Paragraphs.Last.Range.Text = "Table Exported from Excel"
    objDoc.Paragraphs.Last.Range.Style = "Heading 1"   
    ' Save the Word document
    Dim filePath As String
    filePath = Application.GetSaveAsFilename("C:\YourFolder\MyDocument.docx", "Word Files (*.docx), *.docx")   
    If filePath <> "False" Then
        objDoc.SaveAs filePath
        MsgBox "Word document saved successfully!", vbInformation
    Else
        MsgBox "Save canceled.", vbExclamation
    End If   
    ' Close Word
    objDoc.Close
    objWord.Quit   
    ' Release the memory
    Set objDoc = Nothing
    Set objWord = Nothing
End Sub

    Detailed Explanation of the Code:

    Creating a Word instance:

    Set objWord = CreateObject("Word.Application")

    This line creates a Word object using OLE Automation. If Word is already open, it uses the existing instance; otherwise, it starts Word.

    Checking if Word is accessible:

    If objWord Is Nothing Then
    MsgBox "Word could not be launched", vbCritical
    Exit Sub
End If

    If Word can’t be started or found, it shows an error message and exits the procedure.

    Creating a new Word document:

    Set objDoc = objWord.Documents.Add

    This line creates a new Word document where the Excel data will be pasted.

    Referencing the Excel worksheet:

    Set ws = ThisWorkbook.Sheets("Sheet1")

    This line refers to the Excel worksheet that contains the data to be copied. Replace « Sheet1 » with the name of your sheet.

    Defining the range of cells to copy:

    Set RangeToCopy = ws.Range("A1:B10")

    This defines the range of cells to be copied (e.g., A1:B10). You can change this to any range of your choice.

    Copying the Excel cells to Word:

    RangeToCopy.Copy
objDoc.Content.Paste

    The Copy method copies the selected range in Excel, and the Paste method pastes this content into the Word document.

    Formatting the table in Word:

    With objDoc.Tables(1)
    .AutoFitBehavior (2) ' Automatically adjust column width
    .Style = "Table Grid" ' Apply a predefined table style
    .Rows.Alignment = 1 ' Align the rows to the center
End With

    This block formats the pasted table. It automatically adjusts the column widths, applies a table style, and centers the rows.

    Adding a title to the Word document:

    objDoc.Paragraphs.Add
objDoc.Paragraphs.Last.Range.Text = "Table Exported from Excel"
objDoc.Paragraphs.Last.Range.Style = "Heading 1"

    This section adds a title at the beginning of the Word document, indicating that the table was exported from Excel.

    Saving the Word document:

    filePath = Application.GetSaveAsFilename("C:\YourFolder\MyDocument.docx", "Word Files (*.docx), *.docx")
If filePath <> "False" Then
    objDoc.SaveAs filePath
    MsgBox "Word document saved successfully!", vbInformation
Else
    MsgBox "Save canceled.", vbExclamation
End If

    This part opens a Save As dialog, allowing the user to choose where to save the Word document. If the user provides a valid location and filename, the document is saved.

    Closing Word:

    objDoc.Close
objWord.Quit

    This closes the Word document and exits Word.

    Releasing the objects:

    Set objDoc = Nothing
Set objWord = Nothing

    These lines release the memory occupied by the Word objects to avoid memory leaks.

    Conclusion:

    This VBA code allows you to copy a range of cells from Excel into a Word document, format the table in Word, and save the document. You can customize the code for different ranges of data or further tailor the Word document formatting as per your needs.

     

  • Converting an Excel table to an HTML table.

    Steps:

    1. Select the range of the table you want to convert.
    2. Loop through the rows and columns of the selected range to build the HTML table structure.
    3. Create an HTML file and write the generated table to this file.
    4. Save or open the HTML file so you can use or view it in a browser.

    VBA Code to Convert an Excel Table to HTML

    Sub ConvertTableToHTML()
    ' Declare variables
    Dim ws As Worksheet
    Dim tableRange As Range
    Dim cell As Range
    Dim html As String
    Dim i As Long, j As Long
    Dim htmlFilePath As String
    Dim outputFile As Integer   
    ' Define the worksheet and the range containing the table
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with the name of your sheet
    Set tableRange = ws.Range("A1:C5") ' Replace "A1:C5" with the range of your table   
    ' Initialize the HTML string
    html = "<html>" & vbCrLf
    html = html & "<head><title>Excel Table to HTML</title></head>" & vbCrLf
    html = html & "<body>" & vbCrLf
    html = html & "<table border='1' cellpadding='5' cellspacing='0'>" & vbCrLf   
    ' Add the table headers (row 1)
    html = html & "<tr>"
    For j = 1 To tableRange.Columns.Count
        html = html & "<th>" & tableRange.Cells(1, j).Value & "</th>"
    Next j
    html = html & "</tr>" & vbCrLf   
    ' Add the data rows
    For i = 2 To tableRange.Rows.Count
        html = html & "<tr>"
        For j = 1 To tableRange.Columns.Count
            html = html & "<td>" & tableRange.Cells(i, j).Value & "</td>"
        Next j
        html = html & "</tr>" & vbCrLf
    Next i   
    ' Close the table and HTML tags
    html = html & "</table>" & vbCrLf
    html = html & "</body>" & vbCrLf
    html = html & "</html>"   
    ' Prompt the user to specify the file path to save the HTML file
    htmlFilePath = Application.GetSaveAsFilename(FileFilter:="HTML Files (*.html), *.html")   
    ' Check if the user selected a file location
    If htmlFilePath <> "False" Then
        ' Open the file for writing
        outputFile = FreeFile
        Open htmlFilePath For Output As outputFile       
        ' Write the HTML content to the file
        Print #outputFile, html       
        ' Close the file
        Close outputFile       
        ' Show confirmation message
        MsgBox "The table has been successfully converted to HTML!", vbInformation
    End If
End Sub

    Explanation of the Code

    1. Declaring Variables:
      • ws: Represents the worksheet containing the data.
      • tableRange: Represents the range of the table (you can adjust this range as needed).
      • html: Holds the structure of the HTML table.
      • htmlFilePath: Stores the path where the HTML file will be saved.
      • outputFile: Used to open and write to the HTML file.
    2. Building the HTML Structure:
      • Headers: The code generates a <th> (table header) for each cell in the first row of the selected range.
      • Data Rows: Each data row in the table is converted into an HTML <tr> (table row), with each cell becoming a <td> (table cell).
    3. Saving the HTML File:
      • GetSaveAsFilename prompts the user to choose a file location and name for the HTML file.
      • The HTML content is written to the file using the Print statement.
      • Once saved, the file is closed, and a confirmation message is displayed.

    Customization

    • Table Range: Change the range Set tableRange = ws.Range(« A1:C5 ») to match the range of your table.
    • Worksheet Name: Replace « Sheet1 » with the actual name of your worksheet.
    • HTML Table Attributes: You can customize the appearance of the HTML table (e.g., adding colors, borders, etc.) by modifying the HTML code in the html variable.

    How to Use

    1. Open the VBA editor in Excel (Press ALT + F11).
    2. In the Insert menu, choose Module to create a new module.
    3. Paste the code into the module.
    4. Run the ConvertTableToHTML macro to convert your Excel table into an HTML file.

  • Converting a decimal number into a fraction in Excel VBA

    This code takes a decimal number and tries to convert it into its simplest fractional form.

    Steps to create the macro:

    1. Open the VBA editor:
      • In Excel, press Alt + F11 to open the VBA editor.
    2. Add a new module:
      • In the VBA editor, go to Insert > Module to create a new module.
    3. Paste the following code into the module:

    VBA Code to Convert Decimal to Fraction:

    Function DecimalToFraction(ByVal decimalValue As Double) As String
    Dim tolerance As Double
    Dim maxDenominator As Long
    Dim numerator As Long
    Dim denominator As Long
    Dim fraction As String   
    ' Define a tolerance for the conversion (fraction precision)
    tolerance = 0.0001
    maxDenominator = 10000  ' Limit on the denominator (you can adjust this)
    ' If the number is already an integer, return it directly
    If decimalValue = Int(decimalValue) Then
        DecimalToFraction = CStr(Int(decimalValue))
        Exit Function
    End If   
    ' Initialize numerator and denominator
    numerator = 1
    denominator = 1
    Do
        ' Approximate the fraction using continued fractions
        denominator = denominator + 1
        numerator = Round(decimalValue * denominator)       
        ' Check if the fraction is precise enough
        If Abs(decimalValue - numerator / denominator) < tolerance Or denominator > maxDenominator Then
            Exit Do
        End If
    Loop  
    ' Create the fraction as a string
    If numerator Mod denominator = 0 Then
        ' If it's a whole number, just return the numerator
        fraction = CStr(numerator / denominator)
    Else
        fraction = CStr(numerator) & "/" & CStr(denominator)
    End If
    ' Return the fraction as a string
    DecimalToFraction = fraction
End Function

    Explanation of the code:

    1. The DecimalToFraction Function:
      • The function accepts a parameter, decimalValue, which is the decimal number you want to convert into a fraction.
    2. Setting Tolerance and Maximum Denominator:
      • The tolerance defines the precision with which you want the fraction to be approximated. You can adjust this value as per your needs.
      • The maxDenominator is a limit on the size of the denominator to avoid an infinite loop or too complex fractions. You can modify this value as needed.
    3. Check if the number is already an integer:
      • If the decimal number is already an integer (i.e., the integer part is equal to the number), the function will simply return that integer.
    4. Loop for Conversion:
      • The Do While loop tries to approximate the decimal number as a fraction by increasing the denominator and calculating the numerator as the product of the decimal value and the denominator.
      • If the error (difference between the decimal number and the fraction approximation) is less than the specified tolerance, the loop stops.
    5. Building the Fraction:
      • The fraction is formed as a string. If the numerator is divisible by the denominator (i.e., it’s a whole number), it is simplified.
    6. Returning the Fraction:
      • The function returns the fraction as a string in the format numerator/denominator.

    How to use it in Excel:

    1. In your Excel sheet, enter a decimal number into a cell (e.g., 0.75).
    2. In another cell, use the formula:
    =DecimalToFraction(A1)

    (If A1 contains the decimal number).

    1. You will see the corresponding fraction (for example, 3/4 for 0.75).

    Possible Improvements:

    • You can adjust the tolerance or the limit on the denominator to get simpler or more complex fractions as needed.
    • The code can be enhanced to handle special cases (e.g., repeating fractions).

     

  • Currency conversion in Excel VBA

    Objective:

    Create a function that converts an amount from one currency to another (e.g., from Euro to USD).

    1. Add a VBA Module

    Open Excel and press Alt + F11 to open the VBA editor.

    Go to Insert > Module to create a new module.

    Copy and paste the code below into this module.

    VBA Code for Currency Conversion:

    Option Explicit
' Declare global variables for exchange rates
Dim rateEuroUSD As Double
Dim rateEuroGBP As Double
Dim rateEuroJPY As Double
Sub ConvertCurrency()
    ' Initialize the exchange rates (example values)
    rateEuroUSD = 1.1   ' Example: 1 EUR = 1.1 USD
    rateEuroGBP = 0.85  ' Example: 1 EUR = 0.85 GBP
    rateEuroJPY = 150   ' Example: 1 EUR = 150 JPY   
    ' Local variables for currencies and amounts
    Dim amount As Double
    Dim sourceCurrency As String
    Dim targetCurrency As String
    Dim result As Double
    ' Prompt user to enter the source currency, target currency, and amount
    sourceCurrency = InputBox("Enter the source currency (EUR, USD, GBP, JPY):")
    targetCurrency = InputBox("Enter the target currency (EUR, USD, GBP, JPY):")
    amount = InputBox("Enter the amount to convert:")
    ' Perform conversion based on selected currencies
    If sourceCurrency = "EUR" Then
        If targetCurrency = "USD" Then
            result = amount * rateEuroUSD
            MsgBox amount & " EUR = " & result & " USD"
        ElseIf targetCurrency = "GBP" Then
            result = amount * rateEuroGBP
            MsgBox amount & " EUR = " & result & " GBP"
        ElseIf targetCurrency = "JPY" Then
            result = amount * rateEuroJPY
            MsgBox amount & " EUR = " & result & " JPY"
        Else
            MsgBox "Target currency not recognized"
        End If
    ElseIf sourceCurrency = "USD" Then
        If targetCurrency = "EUR" Then
            result = amount / rateEuroUSD
            MsgBox amount & " USD = " & result & " EUR"
        ElseIf targetCurrency = "GBP" Then
            result = (amount / rateEuroUSD) * rateEuroGBP
            MsgBox amount & " USD = " & result & " GBP"
        ElseIf targetCurrency = "JPY" Then
            result = (amount / rateEuroUSD) * rateEuroJPY
            MsgBox amount & " USD = " & result & " JPY"
        Else
            MsgBox "Target currency not recognized"
        End If
    Else
        MsgBox "Source currency not recognized"
    End If
End Sub

    Explanation of the Code:

    Global Variables:

    • rateEuroUSD, rateEuroGBP, rateEuroJPY: These are the exchange rates you define for each currency against the Euro. For example, 1 EUR = 1.1 USD, 1 EUR = 0.85 GBP, and 1 EUR = 150 JPY.

    ConvertCurrency Function:

    • User Inputs: The three InputBox prompts ask the user to enter:
      • The source currency (e.g., EUR, USD).
      • The target currency (e.g., EUR, USD).
      • The amount to convert.
    • Conversion Conditions:
      • The code checks the source currency (EUR, USD, etc.) and the target currency selected.
      • Then, it applies the appropriate exchange rate to perform the conversion by multiplying the amount of the source currency by the exchange rate.
      • A MsgBox displays the result of the conversion.

    Running the Code:

    1. You can run this code by pressing F5 in the VBA editor or by linking it to a button on your Excel sheet.
    2. The program will ask the user for the currencies and the amount to convert, then display the result in a message box.

    Example of Usage:

    If you enter the following values:

    • Source Currency: EUR
    • Target Currency: USD
    • Amount: 100

    The message box displayed will be:

    100 EUR = 110 USD

    This is based on the conversion rate 1 EUR = 1.1 USD.

    Extending with Dynamic Exchange Rates:

    You can extend this further by fetching real-time exchange rates via an API like Fixer.io or OpenExchangeRates. For this, you’ll need to make HTTP requests in VBA to get the live rates and modify the code accordingly.