ansaurus

Question

Macro too big to run, keeps 'not responding'.

Answer 1

A:

I don't know if it's the query that's causing the problem, but that would be my first concern - it's horrible. I can't see any reason for all those correlated sub-queries. Nor can I see any purpose in the self-join. It looks like you're trying to get the maximum value for each sample for each value of A? Without anything on which to test it, I'm only guessing, but I'd say that this:

Dim sampno As Integer

strSQL = "SELECT a.[A], Max(a.Serial) As Serial"
For sampno = 1 To 187
    strSQL = strSQL & ", Max(a.Sample" & sampno & ") As Sample" & sampno
Next
strSQL = strSQL & " From [Data$] a" _
    & " GROUP BY a.[A]"

would give the same results.

400 fields would be a problem in any version of Excel pre-2007 (because those versions only supported 256 columns), otherwise 400 should be fine.

Mike Woodhouse 2009-11-30 12:30:23

I am trying to dedupe against column A and then add on data (columns 'sample1' to 'sample187') from duplicate rows if data is found-basically concatenate the data onto on respondent.

Keziah Dorman 2009-11-30 13:35:51

Answer 2

A:

This has grown very far from the original example you posted. I will answer, based on that original example, that you can use max without the sub queries if you still only have True / False records:

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim strWhere As String
Dim i As Integer

''http://support.microsoft.com/kb/246335

strFile = ActiveWorkbook.FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT a.[Name], Max([Black]) As Black, Max([Blue]) As Blue,Max([Green]) As Green " _
       & "FROM [Sheet3$] a " _
       & "GROUP BY a.[Name]"

rs.Open strSQL, cn, 3, 3

For i = 0 To rs.fields.Count - 1
    Sheets("Sheet5").Cells(1, i + 1) = rs.fields(i).Name
Next

Worksheets("Sheet6").Cells(2, 1).CopyFromRecordset rs

Remou 2009-11-30 12:32:50

Hi does this mean if it the column has a '1' or blank rather than words? Thanks

Keziah Dorman 2009-11-30 12:37:33

Yes. That is what I tried it with.

Remou 2009-11-30 12:50:36

It says too many fields defined. I have gone upto 261 columns. Is this too big? THanks

Keziah Dorman 2009-11-30 13:21:44

It also says record is too large.

Keziah Dorman 2009-11-30 13:29:05

I have not tested with 261 columns. If you do not have to do this very often, it may be best to create a list of names (SELECT [Name] from [sheet3%] group by [name] ) and then work through the original spreadsheet for each name using VBA to pull the row of data. It really seems like something that should be in a database.

Remou 2009-11-30 13:41:18

Hi thanks, I have been able to do it for the number of columns needed now-it's a little tempermental sometimes! Thanks again I think I can finally put this one to rest.

Keziah Dorman 2009-11-30 14:31:43

ansaurus

tags:

views:

answers:

Macro too big to run, keeps 'not responding'.

related questions