XML string layout into Excel with VBA

Question

I'm trying to pull in the results of the below query, but when it gets to the XML String [Processed Data], it fails to display the results in Excel. Please can someone assist? Thanks

Public Sub Load_Error()
Dim ADOC As New ADODB.Connection
Dim SQL1 As String
Dim rst1 As New ADODB.Recordset
Dim i As Integer
Dim r As Integer

'#### Server Information
Server_Name = "A"
Database_Name = "B"

'#### Server Connection is opened

Worksheets("Load Errors").Activate

r = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(1, 1), Cells(r, 6)).ClearContents

ADOC.Open "Provider=MSDASQL.1;DRIVER=SQL Server;SERVER=" & Server_Name & ";DATABASE=" & Database_Name & ";Trusted_Connection=Yes"

SQL1 = "SELECT [CreatedDate],[RecordStatus],[Comment], CONVERT(varchar(max),[ProcessedData]) as [Error Message], [DataExceptionID], [SessionID] " & _
" FROM [C].[dbo].[D](nolock) " & _
" where CreatedDate >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) and comment not in  ('FrontierPositionRecon Load Errors','Errors', 'MCAP load errors') " & _
" order by createddate asc "

ADOC.CommandTimeout = 600
Set rst1 = ADOC.Execute(SQL1)

ActiveSheet.Cells(2, 1).CopyFromRecordset rst1
For i = 0 To rst1.Fields.Count - 1
    ActiveSheet.Cells(1, i + 1).Value = rst1.Fields(i).Name
Next

Set rst1 = Nothing
Set ADOC = Nothing


End Sub

So it runs, gives no errors, but also no data? - What is your record count when returned? — Automate This
– Automate This, Commented Sep 20, 2013 at 13:46
Correct, no errors. The first three columns all have data, but it doesn't return anything for the xml column or the two to the right of it. This query usually returns around 30-40 rows. The xml string is usually around 400 characters. Thanks — Käse
– Käse, Commented Sep 20, 2013 at 13:51

Russop · Accepted Answer · 2013-09-23 15:25:40Z

1

No idea why, but if you replace

Set rst1 = ADOC.Execute(SQL1)

With

rst1.Open SQL1, ADOC, adOpenStatic

it works.

answered Sep 23, 2013 at 15:25

Russop

761 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

XML string layout into Excel with VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related