The issue you are experiencing is likely due to the fact that the nvarchar columns are being retrieved as Unicode strings, but Excel is not able to properly handle these types of strings and is displaying them as garbled text.
There are a few different ways to handle this issue:
- You can convert the nvarchar columns to varchar before retrieving them from the Dataverse. This can be done by using the CAST or CONVERT function in your SQL query. For example:
strSql = "select CAST(column_name as varchar(max)) as column_name from Account"
- You can change the ADO type in your VBA code to adVarChar, which will cause the nvarchar columns to be retrieved as regular strings.
rs1.Fields("column_name").Type = adVarChar
- You can convert the nvarchar columns to a different data type that Excel can handle, such as a number or a date. This can be done by using the CAST or CONVERT function in your SQL query.
- You can use a third-party tool that can handle the Unicode strings and display them correctly in Excel, for example, the "Microsoft Access Database Engine" which is able to handle Unicode data.
It's worth noting that converting nvarchar columns to varchar could cause data loss if the column has characters that are not supported by the varchar data type.