documentation on export report to excel

noun_mim

hi,

Is someone can indicate me where can i find some documentations

on export data to excel.

thanks you ...

Luc_VanDyck

On this forum. Search and you shall find.

albertodfa

for the little that I can help I send you you this:

make 1 report to export Sales inv. Line and Sales Cr. memo Line to Excel.
I create new workbook and enter Sales inv. line data to first worksheet. How do I enter Sales. Credit memo Line data to next worksheet ?
Try this:

ExcelSheet := xlSheet.Item(1);
ExcelSheet.Activate;

Where ExcelSheet is the WorkSheet object and xlSheet is the sheets object. 1 is the worksheet index.

In the Excel Buffer table, there is a function Create Sheet that writes the contents of the table to an Excel sheet; I wrote another function that first adds a new sheet:



CreateNewSheet(SheetName,ReportHeader,CompanyName,UserID2)

XlWrkSht := XlWrkBk.Worksheets.Add;
CreateSheet(SheetName,ReportHeader,CompanyName,UserID2);


(The parameters I use when calling it are usually:
SheetName, '', COMPANYNAME, USERID)


_________________________________________________________________________

Could anyone give me an example how to set a certain Row Height and Column Width using the Excel automation? - Also something that needs to be accomplished will be Merging cells.....any samples would be GREATLY appreciated!

Code:
xlSheet.Range('A1').EntireRow.RowHeight(25);
xlSheet.Range('A1').EntireColumn.ColumnWidth(25);
xlSheet.Range('A1:B1').Merge;

_______________________________________________________________________

Does anybody know how I can turn the page orientation around in Excel?
From landscape to portrait and vice versa.
I've already found the solution:

xlSheet.PageSetup.Orientation := 2; //Landscape

____________________________________________________________________
This is some VB Code:
Code:
With Worksheets("Sheet1").Range("B2").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 3
End With

xlRange := xlWorksheet.Range('B2');
xlRange.Borders.Item(10).LineStyle(1); //Const xlEdgeRight = 10 //Const xlContinuous = 1
xlRange.Borders.Item(10).Color := 255; //red

______________________________________________________________________


Sort Method
Sorts a PivotTable report, a range, or the active region if the specified range contains only one cell.

expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)

expression Required. An expression that returns one of the objects in the Applies To list.

Key1 Optional Variant. The first sort field, as either text (a PivotTable field or range name) or a Range object ("Dept" or Cells(1, 1), for example).

Order1 Optional XlSortOrder . The sort order for the field or range specified in Key1.

XlSortOrder can be one of these XlSortOrder constants.
xlDescending. Sorts Key1 in descending order.
xlAscending default. Sorts Key1 in ascending order.

Key2 Optional Variant. The second sort field, as either text (a PivotTable field or range name) or a Range object. If you omit this argument, there's no second sort field. Cannot be used when sorting Pivot Table reports.

Type Optional Variant . Specifies which elements are to be sorted. Use this argument only when sorting PivotTable reports.

XlSortType can be one of these XlSortType constants.
xlSortLabels. Sorts the PivotTable report by labels.
xlSortValues. Sorts the PivotTable report by values.

Order2 Optional XlSortOrder . The sort order for the field or range specified in Key2. Cannot be used when sorting PivotTable reports.

XlSortOrder can be one of these XlSortOrder constants.
xlDescending. Sorts Key2 in descending order.
xlAscending default. Sorts Key2 in ascending order.

Key3 Optional Variant. The third sort field, as either text (a range name) or a Range object. If you omit this argument, there's no third sort field. Cannot be used when sorting PivotTable reports.

Order3 Optional XlSortOrder . The sort order for the field or range specified in Key3. Cannot be used when sorting PivotTable reports.

XlSortOrder can be one of these XlSortOrder constants.
xlDescending. Sorts Key3 in descending order.
xlAscending default. Sorts Key3 in ascending order.

Header Optional XlYesNoGuess . Specifies whether or not the first row contains headers. Cannot be used when sorting PivotTable reports.

XlYesNoGuess can be one of these XlYesNoGuess constants.
xlGuess. Let Microsoft Excel determine whether there's a header, and to determine where it is, if there is one.
xlNo default. (The entire range should be sorted).
xlYes. (The entire range should not be sorted).

OrderCustom Optional Variant. This argument is a one-based integer offset to the list of custom sort orders. If you omit OrderCustom , a normal sort is used.

MatchCase Optional Variant. True to do a case-sensitive sort; False to do a sort that's not case sensitive. Cannot be used when sorting PivotTable reports.

Orientation Optional XlSortOrientation . The sort orientation.

XlSortOrientation can be one of these XlSortOrientation constants.
xlSortRows default. Sorts by row.
xlSortColumns. Sorts by column.

SortMethod Optional XlSortMethod . The type of sort. Some of these constants may not be available to you, depending on the language support (U.S. English, for example) that you've selected or installed.

XlSortMethod can be one of these XlSortMethod constants.
xlStroke Sorting by the quantity of strokes in each character.
xlPinYin default. Phonetic Chinese sort order for characters.

DataOption1 Optional XlSortDataOption . Specifies how to sort text in key 1. Cannot be used when sorting PivotTable reports.

XlSortDataOption can be one of these XlSortDataOption constants.
xlSortTextAsNumbers. Treat text as numeric data for the sort.
xlSortNormal default. Sorts numeric and text data separately.

DataOption2 Optional XlSortDataOption . Specifies how to sort text in key 2. Cannot be used when sorting PivotTable reports.

XlSortDataOption can be one of these XlSortDataOption constants.
xlSortTextAsNumbers. Treats text as numeric data for the sort.
xlSortNormal default. Sorts numeric and text data separately.

DataOption3 Optional XlSortDataOption . Specifies how to sort text in key 3. Cannot be used when sorting PivotTable reports.

XlSortDataOption can be one of these XlSortDataOption constants.
xlSortTextAsNumbers. Treats text as numeric data for the sort.
xlSortNormal default. Sorts numeric and text data separately.

Remarks
The settings for Header, Order1, Order2, Order3, OrderCustom, and Orientation are saved, for the particular worksheet, each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. Set these arguments explicitly each time you use Sort method, if you choose not to use the saved values.

Text strings which are not convertible to numeric data are sorted normally.

Note If no arguments are defined with the Sort method, Microsoft Excel will sort the selection, chosen to be sorted, in ascending order.

Example
This example sorts the range A1:C20 on Sheet1, using cell A1 as the first sort key and cell B1 as the second sort key. The sort is done in ascending order by row, and there are no headers. This example assumes there is data in the range A1:C20.

Sub SortRange1()
Worksheets("Sheet1").Range("A1:C20").Sort _
Key1:=Worksheets("Sheet1").Range("A1"), _
Key2:=Worksheets("Sheet1").Range("B1")
End Sub

This example sorts the region that contains cell A1 (the active region) on Sheet1, sorting by the data in the first column and automatically using a header row if one exists. This example assumes there is data in the active region, which includes cell A1. The Sort method determines the active region automatically.

Sub SortRange2()
Worksheets("Sheet1").Range("A1").Sort _
Key1:=Worksheets("Sheet1").Columns("A"), _
Header:=xlGuess
End Sub

_________________________________________________________________________________________________________

CAUSE
According to the Excel type library and the Excel VBA Help file, the Sort method has the following syntax: Sort ([Key1], [Order1], [Key2], [Type], [Order2], [Key3], [Order3],
[Header], [OrderCustom], [MatchCase], [Orientation], [SortMethod])
The documentation is incorrect: the Key2 and Type arguments are reversed.

The syntax of the Sort method should read as follows: Sort ([Key1], [Order1], [Type], [Key2], [Order2], [Key3], [Order3],
[Header], [OrderCustom], [MatchCase], [Orientation], [SortMethod])

_________________________________________________________________________________________________________

In your example then, you would do the following:Code:
xlSheet.Range('a6:ad600').Sort(xlSheet.Range('a6'));
xlSheet.Range('a6:ad600').Sort(xlSheet.Range('b6'));

__________________________________________________________________________________________________________

First, sort by column A (only)
Then, sort by column B (notice that you have to do the 'separate' sorts in the reverse order)
That is why I recommended the following code:Code:
xlSheet.Range('a6:ad600').Sort(xlSheet.Range('a6'));
xlSheet.Range('a6:ad600').Sort(xlSheet.Range('b6'));

_________________________________________________________________________________________

mohasin

albertodfa wrote:

for the little that I can help I send you you this:

make 1 report to export Sales inv. Line and Sales Cr. memo Line to Excel.
I create new workbook and enter Sales inv. line data to first worksheet. How do I enter Sales. Credit memo Line data to next worksheet ?
Try this:

ExcelSheet := xlSheet.Item(1);
ExcelSheet.Activate;

Where ExcelSheet is the WorkSheet object and xlSheet is the sheets object. 1 is the worksheet index.

In the Excel Buffer table, there is a function Create Sheet that writes the contents of the table to an Excel sheet; I wrote another function that first adds a new sheet:



CreateNewSheet(SheetName,ReportHeader,CompanyName,UserID2)

XlWrkSht := XlWrkBk.Worksheets.Add;
CreateSheet(SheetName,ReportHeader,CompanyName,UserID2);


(The parameters I use when calling it are usually:
SheetName, '', COMPANYNAME, USERID)


_________________________________________________________________________

Could anyone give me an example how to set a certain Row Height and Column Width using the Excel automation? - Also something that needs to be accomplished will be Merging cells.....any samples would be GREATLY appreciated!

Code:
xlSheet.Range('A1').EntireRow.RowHeight(25);
xlSheet.Range('A1').EntireColumn.ColumnWidth(25);
xlSheet.Range('A1:B1').Merge;

_______________________________________________________________________

Does anybody know how I can turn the page orientation around in Excel?
From landscape to portrait and vice versa.
I've already found the solution:

xlSheet.PageSetup.Orientation := 2; //Landscape

____________________________________________________________________
This is some VB Code:
Code:
With Worksheets("Sheet1").Range("B2").Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 3
End With

xlRange := xlWorksheet.Range('B2');
xlRange.Borders.Item(10).LineStyle(1); //Const xlEdgeRight = 10 //Const xlContinuous = 1
xlRange.Borders.Item(10).Color := 255; //red

______________________________________________________________________


Sort Method
Sorts a PivotTable report, a range, or the active region if the specified range contains only one cell.

expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)

expression Required. An expression that returns one of the objects in the Applies To list.

Key1 Optional Variant. The first sort field, as either text (a PivotTable field or range name) or a Range object ("Dept" or Cells(1, 1), for example).

Order1 Optional XlSortOrder . The sort order for the field or range specified in Key1.

XlSortOrder can be one of these XlSortOrder constants.
xlDescending. Sorts Key1 in descending order.
xlAscending default. Sorts Key1 in ascending order.

Key2 Optional Variant. The second sort field, as either text (a PivotTable field or range name) or a Range object. If you omit this argument, there's no second sort field. Cannot be used when sorting Pivot Table reports.

Type Optional Variant . Specifies which elements are to be sorted. Use this argument only when sorting PivotTable reports.

XlSortType can be one of these XlSortType constants.
xlSortLabels. Sorts the PivotTable report by labels.
xlSortValues. Sorts the PivotTable report by values.

Order2 Optional XlSortOrder . The sort order for the field or range specified in Key2. Cannot be used when sorting PivotTable reports.

XlSortOrder can be one of these XlSortOrder constants.
xlDescending. Sorts Key2 in descending order.
xlAscending default. Sorts Key2 in ascending order.

Key3 Optional Variant. The third sort field, as either text (a range name) or a Range object. If you omit this argument, there's no third sort field. Cannot be used when sorting PivotTable reports.

Order3 Optional XlSortOrder . The sort order for the field or range specified in Key3. Cannot be used when sorting PivotTable reports.

XlSortOrder can be one of these XlSortOrder constants.
xlDescending. Sorts Key3 in descending order.
xlAscending default. Sorts Key3 in ascending order.

Header Optional XlYesNoGuess . Specifies whether or not the first row contains headers. Cannot be used when sorting PivotTable reports.

XlYesNoGuess can be one of these XlYesNoGuess constants.
xlGuess. Let Microsoft Excel determine whether there's a header, and to determine where it is, if there is one.
xlNo default. (The entire range should be sorted).
xlYes. (The entire range should not be sorted).

OrderCustom Optional Variant. This argument is a one-based integer offset to the list of custom sort orders. If you omit OrderCustom , a normal sort is used.

MatchCase Optional Variant. True to do a case-sensitive sort; False to do a sort that's not case sensitive. Cannot be used when sorting PivotTable reports.

Orientation Optional XlSortOrientation . The sort orientation.

XlSortOrientation can be one of these XlSortOrientation constants.
xlSortRows default. Sorts by row.
xlSortColumns. Sorts by column.

SortMethod Optional XlSortMethod . The type of sort. Some of these constants may not be available to you, depending on the language support (U.S. English, for example) that you've selected or installed.

XlSortMethod can be one of these XlSortMethod constants.
xlStroke Sorting by the quantity of strokes in each character.
xlPinYin default. Phonetic Chinese sort order for characters.

DataOption1 Optional XlSortDataOption . Specifies how to sort text in key 1. Cannot be used when sorting PivotTable reports.

XlSortDataOption can be one of these XlSortDataOption constants.
xlSortTextAsNumbers. Treat text as numeric data for the sort.
xlSortNormal default. Sorts numeric and text data separately.

DataOption2 Optional XlSortDataOption . Specifies how to sort text in key 2. Cannot be used when sorting PivotTable reports.

XlSortDataOption can be one of these XlSortDataOption constants.
xlSortTextAsNumbers. Treats text as numeric data for the sort.
xlSortNormal default. Sorts numeric and text data separately.

DataOption3 Optional XlSortDataOption . Specifies how to sort text in key 3. Cannot be used when sorting PivotTable reports.

XlSortDataOption can be one of these XlSortDataOption constants.
xlSortTextAsNumbers. Treats text as numeric data for the sort.
xlSortNormal default. Sorts numeric and text data separately.

Remarks
The settings for Header, Order1, Order2, Order3, OrderCustom, and Orientation are saved, for the particular worksheet, each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. Set these arguments explicitly each time you use Sort method, if you choose not to use the saved values.

Text strings which are not convertible to numeric data are sorted normally.

Note If no arguments are defined with the Sort method, Microsoft Excel will sort the selection, chosen to be sorted, in ascending order.

Example
This example sorts the range A1:C20 on Sheet1, using cell A1 as the first sort key and cell B1 as the second sort key. The sort is done in ascending order by row, and there are no headers. This example assumes there is data in the range A1:C20.

Sub SortRange1()
Worksheets("Sheet1").Range("A1:C20").Sort _
Key1:=Worksheets("Sheet1").Range("A1"), _
Key2:=Worksheets("Sheet1").Range("B1")
End Sub

This example sorts the region that contains cell A1 (the active region) on Sheet1, sorting by the data in the first column and automatically using a header row if one exists. This example assumes there is data in the active region, which includes cell A1. The Sort method determines the active region automatically.

Sub SortRange2()
Worksheets("Sheet1").Range("A1").Sort _
Key1:=Worksheets("Sheet1").Columns("A"), _
Header:=xlGuess
End Sub

_________________________________________________________________________________________________________

CAUSE
According to the Excel type library and the Excel VBA Help file, the Sort method has the following syntax: Sort ([Key1], [Order1], [Key2], [Type], [Order2], [Key3], [Order3],
[Header], [OrderCustom], [MatchCase], [Orientation], [SortMethod])
The documentation is incorrect: the Key2 and Type arguments are reversed.

The syntax of the Sort method should read as follows: Sort ([Key1], [Order1], [Type], [Key2], [Order2], [Key3], [Order3],
[Header], [OrderCustom], [MatchCase], [Orientation], [SortMethod])

_________________________________________________________________________________________________________

In your example then, you would do the following:Code:
xlSheet.Range('a6:ad600').Sort(xlSheet.Range('a6'));
xlSheet.Range('a6:ad600').Sort(xlSheet.Range('b6'));

__________________________________________________________________________________________________________

First, sort by column A (only)
Then, sort by column B (notice that you have to do the 'separate' sorts in the reverse order)
That is why I recommended the following code:Code:
xlSheet.Range('a6:ad600').Sort(xlSheet.Range('a6'));
xlSheet.Range('a6:ad600').Sort(xlSheet.Range('b6'));

_________________________________________________________________________________________

hi guys,
I m new to nav
can u tel me how i wil get xlSheet and XlWrkBk objects;
i knw only excel buffer object from that i hav used createbook();
please tel me all thing about this.

Savatage

viewtopic.php?f=7&t=12014