How to select dynamic sheet names with an Excel VBA macro that moves data with ADODB?

Question

I'm using a macro to move data across workbooks with an ADODB connection. When I select the data from the closed workbook, I need to label the sheet names as dynamic. However, when using my string variable for the name, the code will not run.

Public Sub MoveData()

    Dim fileName As String
        fileName = Worksheets("Cover").range("B5").Value

    Dim path As String
        path = "C:\Users\(user)\Documents\(folder)\" & fileName & ".csv"

    Dim currentWB As Workbook
    Set currentWB = ThisWorkbook

    Dim openWB As Workbook
    Set openWB = Workbooks.Open(path)

    Dim openWs As Worksheet
    Set openWs = openWB.Sheets(fileName)

    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & path & ";" & _
                    "Extended Properties=Excel 12.0 Xml;"
        .Open
    End With

    strQuery = "SELECT * FROM [filename$C2:C100]"

    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandText = strQuery
    End With

    Dim rst As New ADODB.Recordset
    With rst
        .ActiveConnection = cn
        .Open cmd
    End With

    currentWB.Worksheets("Bms").range("C7").CopyFromRecordset rst

    openWB.Close

End Sub

The issue occurs in strQuery = "SELECT * FROM [fileName$C2:C100]". The sheet name will always be the same as the filename. The code seems to take fileName as a literal string, which I do not want. How can I allow the sheet name to be dynamic?

Answer 1

You need to concatenate the filename into the string using & to concatenate so for example:

strQuery = "SELECT * FROM [" & filename & "$C2:C100]"

Answer 2

Change:

strQuery = "SELECT * FROM [filename$C2:C100]"

to:

strQuery = "SELECT * FROM [" & filename & "$C2:C100]"