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
 

Skunk

Board Regular
Joined
Jan 5, 2009
Messages
83
Couldn't it just be the $ sign in the argument?
(" & MyProc1 & $")
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
I think you meant:
Rich (BB code):
.CommandText = Array("" & MyProc1 & "$")
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
No more than is wrong with what I posted - this would be safer:
Rich (BB code):
.CommandText = "[" & MyProc1 & "$]"
 

Forum statistics

Threads
1,081,556
Messages
5,359,552
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top