How do I identify a variable column from ADO query? (1 Viewer)

[April15Hater]

Hi all-

I have an excel spreadsheet that has column headers that are identical to the column headers that are in an ADO crosstab query I'm running. What I would like to do is take the name of the excel header and use it to call the column identifier in ADO for a report I'm making. I'm just having problems getting the blue text syntax right:

For x = intMaxColumn to xlProductionReportSheet.Range("A1:IV1").End(xlToRight).Column
   strExcelColumnHeader = .cells(1, x)
   .cells(RowCount, x) = [COLOR=Blue]rsvalues!strExcelColumnHeader[/COLOR]
Next x

Thanks,

Joe

 

[Banana]

Try something like this:

revalues.Fields(strExcelColumnHeader)

You can't use a variables directly in a implicit collection using a bang operator (or even a dot operator for matter- it has to be qualified using the (). (I think you can omit the "Fields" since it's the default collection, but my habit is to not do so... makes code less confusing that way.)

But why not just do a Excel.CopyRecordset instead of looping?

 

[April15Hater]

Thanks banana, That worked perfect!

I'd really like to, but I've 3 different recordsets using a criteria from an identifier in a multi listbox just to complete one row in excel. It's actually a really haphazard way of making this report, and I'm well aware of it, but here's my problem. For a set of clients, I have multiple tracking identifiers. For each client, the tracking identifier is unique to the client, however may share the same description name. When multiple clients are selected in the listbox, I want to group everything with similar description names together in one column on the report but I'm having a lot of trouble doing that with SQL. I think part of the problem is because I denormalized this area a little for other reasons. I must admit however, I've never heard of the .copyrecordset command. I'll keep it in mind going forward to see if I can implement it elsewhere.