Help With _Excel_RangeWrite() function

[n3wbie]

I had been Lately Writing Code to Write Data array to Excel

Using this Function is not giving error also Not giving any result

Someone Can Please Guide me 

Attaching Sample Code(Main Code is too Big)

 

    $resultS=Taxpower_query('SELECT * FROM ChallanDetails'); This is Ado Connection; Array display i m attaching here for your Reference to Understand data
    _ArrayDisplay($resultS)
    Local $oExcel = _Excel_Open()
    Local $oWorkbook = _Excel_BookNew($oExcel);Excel workbook and Worksheet opens properly
    $data=_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $resultS,"A1")
    MsgBox(0,0,$data &@crlf&@error)

Not Able to figure out where i m going wrong.

is it because of large data size or something else.

Please let me know where i m going wrong

[abberration]

I did a quick test with a small array that I created myself and your code works. In the past, if I had errors with the Excel UDF, I would have trouble with Excel working properly. I would have to go into the Task Manager, kill the Excel process, open Excel, close Excel - and then my script would start working again. I say it's worth trying.

[n3wbie]

17 minutes ago, abberration said:

I did a quick test with a small array that I created myself and your code works. In the past, if I had errors with the Excel UDF, I would have trouble with Excel working properly. I would have to go into the Task Manager, kill the Excel process, open Excel, close Excel - and then my script would start working again. I say it's worth trying.

I Jus tried the same multiple times.I have Nothing Open as i freshly started the computer.Also the same piece of code works with another data set.

Also Excel sheet is getting opened but there is no data getting populated.

It feels like Some Limitation on amount of data that can be writted using the function.

as even @error is blank

Edited April 16, 2022 by n3wbie
Added Other Useful information that could be of use for debugging

[water]

Please set parameter ForceFunc of _Excel_RangeWrite to True. You might have hit one of the limitations of the Excel transpose function.
See help file for details.

[n3wbie]

18 minutes ago, water said:

Please set parameter ForceFunc of _Excel_RangeWrite to True. You might have hit one of the limitations of the Excel transpose function.
See help file for details.

I felt that was Suppose to change rows to columns and columns to rows.

Thanks The thing worked Charm and data is now getting filled properly.

Thanks once again.

[n3wbie]

Also I had One More Query that I have date field in my database.

When Writing to Excel it gets converted into Number.When i Change The formatting to short date i get back the details 

but is there any way to get a workaround not doing all this stuff?

[water]

22 minutes ago, n3wbie said:

I felt that was Suppose to change rows to columns and columns to rows.

That's exactly what the Excel Transpose function does. As this function has its limitations, you can set parameter ForceFunc to True to use the internal Transpose function of the Excel UDF. For this function only AutoIt limits apply and it takes more processing time.

[water]

24 minutes ago, n3wbie said:

When Writing to Excel it gets converted into Number.

That's exactly the way Excel processes date/time values. Plese see: https://www.autoitscript.com/wiki/Excel_UDF#Date_and_Time

 

25 minutes ago, n3wbie said:

When i Change The formatting to short date i get back the details

Formatting changes the way Excel displays it's data. But it does not change the way Excel internally stores data.
You can set the formatting or translate date/time values to a string as described in the wiki.

[n3wbie]

2 minutes ago, water said:

That's exactly the way Excel processes date/time values. Plese see: https://www.autoitscript.com/wiki/Excel_UDF#Date_and_Time

 

Formatting changes the way Excel displays it's data. But it does not change the way Excel internally stores data.
You can set the formatting or translate date/time values to a string as described in the wiki.

I Already Know the concept that how it stores.

I was Wondering if there is a way out on how to display as it is.

I tried value and Formula Method in udf Doesnt Seem to change output.

is there any other way doing this.Basic Problem is the data that is retrieved is not known to me so i cant hard code that excel output has to be same.

You can say its like a Connection to unknown database where i cannot map fields.I just want as they look in database or _arraydisplay

[water]

They only way I can think of is to modify the query so it returns all data in the required format.