Cannot copy Recordset After Using VBA Function

CPGDeveloper

Board Regular
Joined
Oct 8, 2008
Messages
174
I have an MS Access based Reporting Applications that outputs reports to Excel. In the back-end I have a table similar to this:

ID, Comment
1, Comment1
1, Comment2
1, Comment3


And from this, I have a request to create something like this:

ID, Comments
1, Comment1, Comment2, Comment3

I found a Function that achives this --

Public Function GetList(SQL As String _
, Optional ColumnDelimeter As String = ", " _
, Optional RowDelimeter As String = vbCrLf) As String
'PURPOSE: to return a combined string from the passed query
'ARGS:
' 1. SQL is a valid Select statement
' 2. ColumnDelimiter is the character(s) that separate each column
' 3. RowDelimiter is the character(s) that separate each row
'RETURN VAL: Concatenated list
'DESIGN NOTES:
'EXAMPLE CALL: =GetList("Select Col1,Col2 From Table1 Where Table1.Key = " & OuterTable.Key)


Const PROCNAME = "GetList"
Const adClipString = 2
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sResult As String


On Error GoTo ProcErr


Set oConn = CurrentProject.Connection
Set oRS = oConn.Execute(SQL)


sResult = oRS.GetString(adClipString, -1, ColumnDelimeter, RowDelimeter)


If Right(sResult, Len(RowDelimeter)) = RowDelimeter Then
sResult = Mid$(sResult, 1, Len(sResult) - Len(RowDelimeter))
End If


GetList = sResult
oRS.Close
oConn.Close


CleanUp:
Set oRS = Nothing
Set oConn = Nothing


Exit Function
ProcErr:
' insert error handler
Resume CleanUp


End Function


It works well -- however, after running a query using this function, I cannot Export the Recordset or even Copy it. I don't get an error, but it simply hangs for well over 20 minutes before not responding. Perhaps there is a more efficient way to achieve this result? Any help would be greatly appreciated.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I haven't used the function you are using (I have seen it before), but have used this one: http://allenbrowne.com/func-concat.html and was able to export it in the past.

Have you tried using it with a subset of your data to see if it is just the amount of data that you are passing is causing the crash? If so then it may be worth running the initial query as a make table query and exporting the resulting table.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top