Change Worksheet in exiting Excel-File

Martin_Förster · 2006-12-06

Hallo,

my Problem is really specific. All Topica wich I found to deal with Export Data to a new Excel File.

My Problems are that I Have to export Data in a exiting Ecxel File with more than 1 Worksheet.

I can open the file an write data in the first Worksheet but when i try to change the worksheet and write Data, Navision write this data in the first worksheet.

Can somebody help me.

Here are my code:

CREATE(EXCLAPP);
ExclWorkbook := ExclApp.Workbooks.OPEN('C:\schnittstelle.xls');
//ExclApp.ActiveWorkbook.Sheets.Select(1);
Exclworksheet := ExclApp.ActiveSheet;
ExclWorksheet.Select(1);

  ExcelZeile := '2';
  ExclWorksheet.Range('C' + ExcelZeile).Value := '000001';
  ExcelZeile := '3';
  ExclWorksheet.Range('C' + ExcelZeile).Value := FORMAT(TODAY);
  ExcelZeile := '4';
  ExclWorksheet.Range('C' + ExcelZeile).Value := 'xxxxxxx';
  ExcelZeile := '5';
  ExclWorksheet.Range('C' + ExcelZeile).Value := '';
  ExcelZeile := '6';
  ExclWorksheet.Range('C' + ExcelZeile).Value := 'xxxxxxx';
  ExcelZeile := '7';
  ExclWorksheet.Range('C' + ExcelZeile).Value := 'xxxxx';
  ExcelZeile := '8';
  ExclWorksheet.Range('C' + ExcelZeile).Value := 'xxxxxxxxxx';
  ExcelZeile := '9';
  ExclWorksheet.Range('C' + ExcelZeile).Value := '123123123';

ExclWorksheet.Select(2);

  ExcelZeile := '3';
  ExclWorksheet.Range('C' + ExcelZeile).Value := 'test';
ExclApp.Visible := TRUE;

[/b]

thanks fpor your help.

Martin

zeninoleg · 2006-12-06

To add a sheet:

XlWrkSht := XlWrkBk.Worksheets.Add;
XlWrkSht.Name(SheetName);

To Delete:

XlWrkSht := XlWrkBk.Worksheets.Item(SheetNumber);
XlWrkSht.Delete;

SheetNumber - integer

I hope taht helps

Waldo · 2006-12-06

When you record a macro, you get something like:

Sheets("Sheet1").Select

So, maybe it's something like:

ExclApp.ActiveWorkbook.Sheets("Sheet1").Select;

Hope this helps ... I did not test it

Martin_Förster · 2006-12-07

Okay that's right!!!!

IT workS!!!! :-)

For these People who have the Same Problems here Are the Code:

CREATE(ExclApp);
ExclWorkbook := ExclApp.Workbooks.Open('C:\schnittstelle.xls');
ExclWorksheet := ExclWorkbook.Worksheets.ITEM('Kopfdaten');

  ExcelZeile := '2';
  ExclWorksheet.Range('C' + ExcelZeile).Value := '000001';
  ExcelZeile := '3';
  ExclWorksheet.Range('C' + ExcelZeile).Value := FORMAT(TODAY);
  ExcelZeile := '4';
  ExclWorksheet.Range('C' + ExcelZeile).Value := 'xxx';
  ExcelZeile := '5';
  ExclWorksheet.Range('C' + ExcelZeile).Value := '';
  ExcelZeile := '6';
  ExclWorksheet.Range('C' + ExcelZeile).Value := 'xxx';
  ExcelZeile := '7';
  ExclWorksheet.Range('C' + ExcelZeile).Value := 'xxx';
  ExcelZeile := '8';
  ExclWorksheet.Range('C' + ExcelZeile).Value := 'xxx';
  ExcelZeile := '9';
  ExclWorksheet.Range('C' + ExcelZeile).Value := 'xxxx';


ExclWorksheet := ExclWorkbook.Worksheets.ITEM('Debitorenstammdaten');


  ExcelZeile := '3';
  ExclWorksheet.Range('C' + ExcelZeile).Value := 'test';
ExclApp.Visible := TRUE;

Explain:

First the report open the exiting Excel File, then it looks for the Worksheet called 'Kopfdaten'. Next I fill these cells which want to get the Information :-).

Atfer That I Called the next Worksheet named 'Debitorstammdaten'.

And so one...

Really nice these Procedure....

THX

MArtin

Waldo · 2006-12-07

Can you put [Solved] in the title?

[-o<

Change Worksheet in exiting Excel-File

Comments

Categories