Copy selective columns from one sheet to another using ADO VBA-CodePudding

I have a Summary data sheet containing the 1st row with selective column names, that i want to fill with data pulled from Paste data sheet using ADO. I have written the following code:

Sub CreateSummaryData()
Dim oCon As Object, oRec As Object
Dim strSQL As String
Dim wb As Workbook, wk1 As Worksheet, wk2 As Worksheet
Dim xLastColumn As Long, xLastRow As Long, i As Integer
Dim xCell, xRng As Range

On Error Resume Next
Set wb = ThisWorkbook

Set wk1 = wb.Worksheets("Paste Data")
Set wk2 = wb.Worksheets("Summary of data")

Set oCon = CreateObject("ADODB.Connection")
Set oRec = CreateObject("ADODB.Recordset")

With oCon
    .Open Join$(Array("Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & _
    wb.FullName & "';Extended Properties=""Excel 12.0; HDR=YES;IMEX=0"""), vbNullString)
End With

With wk2
    xLastColumn = .Range("1:1").Cells(.Columns.Count).End(xlToLeft).Column
    If xLastColumn = 1 Then Exit Sub
    
    Set xRng = .Range(.Cells(1, 1), .Cells(1, xLastColumn))
    
    strSQL = ""
    strSQL = "SELECT "
    For Each xCell In xRng
        strSQL = strSQL & "[" & xCell.Value2 & "],"
    Next xCell
    strSQL = Left(strSQL, Len(strSQL) - 1)
    strSQL = strSQL & " FROM [" & wk1.Name & "$]"
End With

With oRec
    .Open strSQL, oCon, 3, 3
End With

wk2.Range("A2").CopyFromRecordset oRec

With oCon
    .Close
End With

Set oRec = Nothing
Set oCon = Nothing

End Sub

The problem is that the Paste Data sheet columns contain spaces or dots in between their names. I think this is the reason why the ADO recordset shows up as empty with "operation is not allowed when object is closed" error.

The column names are like this:

 ----------- --------- --------- ------ ------------------ ----- ----- ----- ----- ----- ------------ ------------ ------------ ------------ ------------ ------------ 
| segmentId | segment | concept | type | metric.base.size | p.5 | p.4 | p.3 | p.2 | p.1 | fitted.p.5 | fitted.p.4 | fitted.p.3 | fitted.p.2 | fitted.p.1 | Mean Value |
 ----------- --------- --------- ------ ------------------ ----- ----- ----- ----- ----- ------------ ------------ ------------ ------------ ------------ ------------

I have tried encapsulating the column names using "[" or "`", but wasn't very helpful. Is there a way to handle this issue in ADO itself without resorting to manipulating the column names in Paste data or Summary of data sheets?

Any help would be most appreciated.

Edit:

After commenting On error resume next, I get the following error on the break line i.e. when it encounters the first columnname with "." in it viz., metric.base.size:

I also tried the solution given in this

Here is the complete column names that i formatted after following the above post. I also tried enclosing with "[" brackets as well as "`", but got same error. Is it because there are more than one "." or " " in a column?

SELECT `segmentId`,`exerciseConceptId`,`ConceptName`,`ConceptType`,`Groups`,`Segment`,`metric#base#size`,`fitted#p#5`,`fitted#p#4`,`fitted#p#3`,`fitted#p#2`,`fitted#p#1`,`MeanFrequency`,`MeanUnitsFav`,`MeanValue` FROM [Paste Data$]

CodePudding user response：

This worked for me - replace any period in a field name with #

For Each xCell In xRng
    strSQL = strSQL & "[" & Replace(xCell.Value2, ".", "#") & "],"
Next xCell

This is the solution from your linked post - you don't show all your code for that so I'm not sure why it didn't work for you.