Solved Export to Excel from Child Table

Discussion forum about all things Gupta, OpenText and the community.
SSRJMC
Saudi Arabia
Posts: 12
Joined: 08 May 2017, 12:27
Location: Jeddah,Saudi Arabia

Export to Excel from Child Table

Post by SSRJMC » 25 Apr 2020, 09:13

hi,

I am looking for the sample code for Export to Excel from Child Table .
Note:
which is the format i used for column in the child table have to be export as it is...
Ex: DateTime format like (dd/MM/yyyy , dd-MM-yyyy, ddMMyyyy) must be export,what i showed in Screen

FRBhote
India
Posts: 2215
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Export to Excel from Child Table

Post by FRBhote » 25 Apr 2020, 09:49

Code: Select all

!!CB!! 160
Set nError = MTblExportToExcel( hWndForm, MTE_LNG_ENGLISH, String_NULL, 
			MTE_EXCEL_NEW_INSTANCE|MTE_EXCEL_STRING_COLS_AS_TEXT, 
			MTE_COL_HEADERS | MTE_SPLIT_ROWS, 0, ROW_MarkDeleted, COL_Visible, 0 )
If nError != 1
	Call ErrorBox( 'Not Completed - Error: ' || SalNumberToStrX( nError, 0 ) || 'Please verify that you have downloaded Mtbl ' )

SSRJMC
Saudi Arabia
Posts: 12
Joined: 08 May 2017, 12:27
Location: Jeddah,Saudi Arabia

Re: Export to Excel from Child Table

Post by SSRJMC » 25 Apr 2020, 09:54

FRBhote wrote:
25 Apr 2020, 09:49

Code: Select all

!!CB!! 160
Set nError = MTblExportToExcel( hWndForm, MTE_LNG_ENGLISH, String_NULL, 
			MTE_EXCEL_NEW_INSTANCE|MTE_EXCEL_STRING_COLS_AS_TEXT, 
			MTE_COL_HEADERS | MTE_SPLIT_ROWS, 0, ROW_MarkDeleted, COL_Visible, 0 )
If nError != 1
	Call ErrorBox( 'Not Completed - Error: ' || SalNumberToStrX( nError, 0 ) || 'Please verify that you have downloaded Mtbl ' )
I am using Child Table Control not Mtbl...

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 137
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Export to Excel from Child Table

Post by Steve Leighton » 26 Apr 2020, 21:52

.
Simple Automation example attached.

Populate the Child Table from any SQL query first .
.
ExportTblToExcel_Automation.zip
You do not have the required permissions to view the files attached to this post.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

SSRJMC
Saudi Arabia
Posts: 12
Joined: 08 May 2017, 12:27
Location: Jeddah,Saudi Arabia

Re: Export to Excel from Child Table

Post by SSRJMC » 27 Apr 2020, 11:15

Steve Leighton wrote:
26 Apr 2020, 21:52
.
Simple Automation example attached.

Populate the Child Table from any SQL query first .
.

ExportTblToExcel_Automation.zip
Thanks for your reply..your sample code also having same issue..
what is the issue i am facing in my existing code,
NOTE: discrepancy date format (Populate table and after export excel )
Please Find Attached
Populate Table Screen.png
Excel.png
You do not have the required permissions to view the files attached to this post.

User avatar
Steve Leighton
Site Admin
Site Admin
New Zealand
Posts: 137
Joined: 05 Mar 2017, 20:57
Location: Tauranga, New Zealand <--> Stroud, England

Re: Export to Excel from Child Table

Post by Steve Leighton » 28 Apr 2020, 04:53

.
Since the dates are being copied into Excel as Text,
you first need to format the Excel Range to be Text also. Otherwise Excel defaults and ( incorrectly ) assumes it is a Date format required.

As soon as the Excel Range is defined, you simply apply a Text format to the whole Range:

Code: Select all

Call vFormat.SetString( '@' )
Call iXRange.PropSetNumberFormat( vFormat )
this way , Excel acts as if the Text ( Dates in this case ) are being typed in directly and does not apply any other formatting - it just takes whatever is typed ( or copied ) into the cell.
Obviously, if you don't want to apply a Text format to an entire Range, you can apply to just columns or cells - but they require defining first in the same way as the Range is defined.

Revised sample app attached to include the declaration of a variant, and the call to set the entire Range to Text format.
.
ExportTblToExcel_Automation.zip
You do not have the required permissions to view the files attached to this post.
Greetings from New Zealand
Steve Leighton

Bankside Systems Ltd.
UK ♦ Australia ♦ New Zealand

www.banksidesystems.co.uk

Image

SSRJMC
Saudi Arabia
Posts: 12
Joined: 08 May 2017, 12:27
Location: Jeddah,Saudi Arabia

Re: Export to Excel from Child Table

Post by SSRJMC » 28 Apr 2020, 08:12

Thanks for your post Mr.Steve Leighton

I got a solution from your sample code..

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot], aspurga and 0 guests