导出Excel VBA

最新推荐文章于 2024-10-09 14:22:34 发布

jerrywei

最新推荐文章于 2024-10-09 14:22:34 发布

阅读量905

点赞数

分类专栏： Perl VBA 文章标签： excel vba application module perl

本文链接：https://blog.csdn.net/jerrywei/article/details/3648196

版权

Perl 同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

VBA

1 篇文章 0 订阅

订阅专栏

用Perl程序导出Excel VBA.

my $isAppLoaded = 0;

# Export Excel VBA modules
sub ExportExcelVBA
{
my ($excelFile,$destFolder)= @_;
my $excelApp = "";

# Get the excel application
GetApp(/$excelApp, 'Excel.Application');

my $oWorkBooks = $excelApp->WorkBooks;

my $oCurrentWorkBook = GetExistObject($oWorkBooks,$excelFile);

   if($oCurrentWorkBook eq "")
   {
      $oCurrentWorkBook = $excelApp->WorkBooks->Open($excelFile);
   }

my $oVBProject = $oCurrentWorkBook->VBProject;

   if (not(-e $destFolder ))
   {
       `mkdir $destFolder`;
   }

   ExportVBAModules(/$oVBProject,$destFolder );
   # Close the workbook without saving
   if ($isAppLoaded == 0)
   {
       $oCurrentWorkBook->Close(0, 0, 0);
   }

ExitApp(/$excelApp);
}

# Getthe exist opened object
sub GetExistObject
{
   my ($objects, $objName) = @_;
   my $count = $objects->Count;
   my $oCurrentObject ="";
   if ($count >0 )
   {
      for (my $i =1; $i<=$count; $i++)
      {
          my $oTempObject = $objects->Item($i);
          my $objectName =$oTempObject->FullName;
          if ("/L$objName" eq "/L$objectName")
          {
             $oCurrentObject = $oTempObject;
             last;
          }
      }
   }

return $oCurrentObject;
}

# Get the Application
sub GetApp
{
   my ($app, $progID) = @_;
   eval
   {
       $$app = Win32::OLE->GetActiveObject($progID);
   };

   if ($@ or $$app eq "")
   {
       $$app= Win32::OLE->new($progID);
       $isAppLoaded = 0;
   }
   else
   {
       $isAppLoaded = 1;
   }
}

# Export the moduels of the VBA project
sub ExportVBAModules
{
   my ($oVBProject, $destFolder) = @_;
   my $oVBComponents = $$oVBProject->VBComponents;
   my $count = $oVBComponents->Count;

   for (my $i = 1; $i <=$count; $i++)
   {
      my $module = $oVBComponents->Item($i);
      my $moduleName = $module->Name;
      my $type = $module->Type;
      my $fileType = "";

      if ($type == vbext_ct_StdModule || $type == vbext_ct_Document )
      {
          $fileType = ".cls";
      }
      elsif ($type == vbext_ct_MSForm)
      {
          $fileType = ".frm";
      }
      elsif ($type == vbext_ct_ClassModule)
      {
          $fileType = ".bas";
      }

      if ($fileType ne "")
      {
          my $fullPath = $destFolder."//".$moduleName.$fileType;
          print "Export $fullPath/n";
          $module->Export($destFolder."//".$moduleName.$fileType);
      }

}
}

# Exit the Applicatin
sub ExitApp
{
   if ($isAppLoaded == 0)
   {
      my $app = shift;
      $$app->Quit();
   }
}