use strict;
use warnings;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;
my $wb = $xl->Workbooks->Add;
if ($wb->Sheets->{Count} > 1) {
    for (2..$wb->Sheets->{Count}) {
        $wb->Sheets(2)->Delete;
    }
}

$xl->VBE->ActiveVBProject->VBComponents->Add(1);

my $cm = $xl->VBE->ActiveVBProject->VBComponents(3)->CodeModule;
my $line = int($cm->CountOfLines);
$cm->InsertLines(++$line, "Function CHARW(code As Variant) As String");
$cm->InsertLines(++$line, "'Use a Leading \"U\" or \"u\" to indicate Unicode values");
$cm->InsertLines(++$line, "   code = VBA.Replace(code, \"+\", \"\", 1, 1, vbTextCompare)");
$cm->InsertLines(++$line, "   If UCase(Left\$(code, 1)) = \"U\" Then code = VBA.Replace(code, \"U\", \"&H\", 1, 1, vbTextCompare)");
$cm->InsertLines(++$line, "   CHARW = ChrW(code)");
$cm->InsertLines(++$line, "End Function");
$cm->InsertLines(++$line, "");
$cm->InsertLines(++$line, "Function AscDec(char As Variant) As String");
$cm->InsertLines(++$line, "    Dim i As Long");
$cm->InsertLines(++$line, "    For i = 1 To Len(char)");
$cm->InsertLines(++$line, "        AscDec = AscDec & AscW(Mid(char, i, 1))");
$cm->InsertLines(++$line, "        If i < Len(char) Then AscDec = AscDec & \":\"");
$cm->InsertLines(++$line, "    Next i");
$cm->InsertLines(++$line, "End Function");

my $sht = $wb->Sheets(1);
$sht->Cells(1, 1)->{Formula} = '=charw(1576)&charw(1744)&charw(1610)&charw(1580)&charw(1609)&charw(1709)';
$sht->Cells(1, 2)->{Formula} = '=charw(21271)&charw(20140)';
$sht->Cells(1, 3)->{Value}   = 'Beijing';

$sht->Range("A1:C1")->Copy;
$sht->Range("A1:C1")->PasteSpecial(-4163); #xlPasteValues

$sht->Cells(2, 1)->{Formula} = "=ascdec(A1)";
$sht->Range("A2")->Copy ($sht->Range("B2:C2"));
print $sht->Cells(2, 1)->{Value} . "\n";
print $sht->Cells(2, 2)->{Value} . "\n";
print $sht->Cells(2, 3)->{Value} . "\n";

$xl->{DisplayAlerts} = 0;
$xl->Quit;