Not a Valid Name

[seahawk56]

I created a macro that would go into 20 folders and copy the data and paste it into the workbook. It is working fine but a friend showed me how to write (I believe they are called) arguments and since other departments are going to start to use the workbook it makes since making an "argument" for the name of the folder. I think I'm only getting caught up on referencing the tab. I named the tab the same name as the folder thinking that would allow me not to have an "argument" for both the tab and the file. I am receiving error "Run-time error '1004'" saying my (" & MyProc1 & $" is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long). Before I rethink that my tactic I thought I would try out the great advice on this site. Any information you can provide would be great. I'm still pretty new at visual basic so sorry if I'm using the wrong terminology. If you also see a gross error in my coding please point it out. I highlighted the area in red that I think I have the problem.
Thanks,
Seahawk56

Option Explicit
Const sheetname1 = "Daily Prod"
Dim Proddate As String
Dim MyMonth As String
Dim MyProc1 As String
 
Sub Prod()
'
' Prod Macro
' Macro recorded 8/11/2008 by whitjh1
'
'
    Proddate = Trim(Sheets(sheetname1).Cells(1, 7).Value)
    MyProc1 = Trim(Sheets(sheetname1).Cells(2, 1).Value)
'  Proc 1
    With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=""G:\ROC-CLAIMS\Clms Proc-Model Line\POS Production Tracki" _
        , _
        "ng\" & MyProc1 & "\" & Proddate & """;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet" _
        , _
        " OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Glob" _
        , _
        "al Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=F" _
        , _
        "alse;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=Fals" _
        , "e;Jet OLEDB:SFP=False"), Destination:=Range("A1:A50"))
        .CommandType = xlCmdTable
        .CommandText = Array(" & MyProc1 & $")
        .Name = "" & Proddate & ""
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = _
        "G:\ROC-CLAIMS\Clms Proc-Model Line\POS Production Tracking\" & MyProc1 & "\" & Proddate & ""
        .Refresh BackgroundQuery:=False

 

[Legacy 108787]

Couldn't it just be the $ sign in the argument?
(" & MyProc1 & $")

 

[RoryA]

I think you meant:

.CommandText = Array("" & MyProc1 & "$")

 

[Richard Schollar]

I think you meant:

.CommandText = Array("" & MyProc1 & "$")

Something wrong with this?:

.CommandText = Array(MyProc1 & "$")

 

[RoryA]

No more than is wrong with what I posted - this would be safer:

.CommandText = "[" & MyProc1 & "$]"

 

[seahawk56]

Thank you works great.
Seahawk56