Loading several html files into seperate sheets of one Excel Workbook using Delphi and OLE

Question

I have 3 html files test1, test2 and test3. I want to load these 3 files into one Excel Workbook and each HTML file on seperate sheets test1, test2 and test3. I want to use Delphi and OLE automation. Perhaps more of this in VB but I cannot find a Delphi code

Thanks

Answer 1

As I mentioned in a comment, if you attempt to load an .Html file into a WorkBook, Excel will remove the existing WorkSheets from the WorkBook and then load the .Html into a new, single, sheet, so if you tried to open multiple .Html files in the same WorkBook, you'd end up with only the lasted one loaded. Not all .Html files seem to be digestible by Excel, btw, and it often complains about things such as being unable to access necessary .Css files.

So, the code below does what I think you want by loading the .Html files into separate WorkBooks and then copies the single worksheet of the second to Nth .Html files into the first one, which it saves in C:\Temp. Hopefully the code should be reasonably self-explanatory, but if in doubt, ask. I've only shown processing the 1st & 2nd .Html files because you'd just repeat the steps for the 2nd one for the 3rd to Nth.

uses
  Variants, ComObj;

procedure TForm1.btnLoadClick(Sender: TObject);
var
  vExcel,
  vWorkbook1,
  vWorkBook2,
  vWorkSheet,
  vWorkSheet2,
  vSourceRange,
  vDestRange : OleVariant;
const
  xlOpenXMLWorkbook = $00000033;  // borrowed from Excel 2010 type lib import
begin
  vExcel := CreateOleObject('Excel.Application');
  vExcel.Visible := True;
  vExcel.DisplayAlerts := False;

  try
    //   The following starts a new workbook and loads the first .Html file
    vWorkBook1 := vExcel.WorkBooks.Open('d:\aaad7\html\Inputs.html');

    //   Next, save it in C:\Temp in XLSX format
    vWorkBook1.SaveAs(FileName := 'C:\temp\' + 'Output.Xlsx', FileFormat:= xlOpenXMLWorkbook);

    //  Next, open another wokkbook with the 2nd .Html file
    vWorkBook2 := vExcel.WorkBooks.Open('d:\aaad7\html\Test.Html');

    //  The next bit uses Excel Range objects to make a copy of the worksheet from the 2nd
    //  workbook into a new worksheet in

    vSourceRange := vWorkBook2.WorkSheets[1].UsedRange;

    vWorkSheet := vWorkBook1.WorkSheets.Add;
    vDestRange := vWorkSheet.Range['A1', 'A1'];

    vSourceRange.Copy(vDestRange);
    vWorkBook2.Close;
    vWorkBook1.SaveAs(FileName := 'C:\temp\' + 'Output.Xlsx', FileFormat:= xlOpenXMLWorkbook);

    //   for the  3rd to Nth .Html files, repeat the steps starting with
    //       vWorkBook2 := vExcel.WorkBooks.Open([...])


  finally
    vWorkBook1 := UnAssigned;
    vWorkBook2 := UnAssigned;
    vExcel := UnAssigned;
  end;

end;