Not a Valid Name

seahawk56

New Member
Joined
Jun 23, 2008
Messages
30
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

Rich (BB code):
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
I think you meant:
Rich (BB code):
.CommandText = Array("" & MyProc1 & "$")
 
Upvote 0
No more than is wrong with what I posted - this would be safer:
Rich (BB code):
.CommandText = "[" & MyProc1 & "$]"
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top