Copy several sheets from one workbook to another

Hello,

I am trying to copy data from several sheets in one closed workbook and paste it into another workbook and different sheets. It works for most of the sheets (5) but for two it does not work. I have posted an example below, for the first two sheets if works but not for the third sheet.

I get the error message: "Run-time error '-2147217900 (80040e14)' Invalid bracketing of name RET. MAT. RECIEPT QT.

Any idea of why it does not work? Could it be because of the . in the name of the sheet? Is there any way to get around that? Can I change the name of the sheet in the closed workbook before I copy the contents? Any better suggestion?


Sub ImporteraExcelTillExcel_ADO()

'--------------------------------------------------------------
'importerar data från en extern Excelbok utan att öppna den
'--------------------------------------------------------------


'variabeldeklareringar
Dim datConnection As ADODB.Connection
Dim recSet As ADODB.Recordset
Dim recRubrik As ADODB.Field
Dim strDB, strSQL As String
Dim strDriver As String
Dim i As Long

'sökväg till den externa Excelfilen
strDB = "B:\LP\Cewe-Control\QA\Avdelningen\Drives Warranty process\20180306.xls"
'strDB = "C:\ExcelVBA\MinExcelFil.xlsx" 'filen i annan folder

'uppkoppling
Set datConnection = New ADODB.Connection

Set recSet = New ADODB.Recordset

strDriver = "DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
datConnection.Open "DBQ=" & strDB & ";" & strDriver & ";UID=admin;"

'SQL-förfrågan
strSQL = "SELECT * FROM [NCR-PRODUCT$A1:BI9999]" 'cellområde på visst ark
'strSQL = "SELECT * FROM [Cellområdesnamn]" 'för namngivna cellområden

'öppnar ett "recordset"
recSet.Open strSQL, datConnection, adOpenStatic

'väljer rätt sheet
Sheets("NCR-PRODUCT").Select

'kopierar in ny data
ActiveSheet.Range("A2").CopyFromRecordset recSet

'upprepar ovan för nästa blad i excelfilen (2)
Set recSetB = New ADODB.Recordset
strSQL = "SELECT * FROM [CREDIT-DEBIT NOTE QT$A1:L9999]"
recSetB.Open strSQL, datConnection, adOpenStatic
Sheets("CREDIT-DEBIT NOTE QT").Select
ActiveSheet.Range("A2").CopyFromRecordset recSetB

'upprepar ovan för nästa blad i excelfilen (3)
Set recSetC = New ADODB.Recordset
strSQL = "SELECT * FROM [RET. MAT. RECIEPT QT$A1:L9999]"
recSetC.Open strSQL, datConnection, adOpenStatic
Sheets("RET. MAT. RECIEPT QT").Select
ActiveSheet.Range("A2").CopyFromRecordset recSetC

'kopplar ned (viktigt!)
recSet.Close
datConnection.Close

'stänger ned objekten (viktigt!)
Set recSet = Nothing
Set datConnection = Nothing


End Sub

["RET. MAT. RECIEPT QT"!$A1:L9999] gives me the the error: Compile error: Expected: End of statement

and I do not get Sheets(...) to work with the rest of the row... strSQL = "SELECT * FROM [CREDIT-DEBIT NOTE QT$A1:L9999]"

I have spaces in all of the names of the sheets and it works for the other sheets...

I get the files this way from the system so I would prefer not having to open the files and rename them. Is there a way I can rename the sheet of a closed workbook? How do I do that?