Excel Macros - Saving as tab delimited with the

Question

I have a couple hundred excel workbooks all with the exact same format and structure. I need to prep each file to be processed later by:

1. Deleting one row,
2. Adding three columns
3. Adding Placeholder text to one of the columns (this will be replaced later by the filename with a script)
4. saving the file as tab delimited with the filename it originally had.(the purpose of the tab delimited change is that i want it in this format so i can replace the placeholder text in #3 with the filename)

Ideally I could open all of the workbooks I need to run this on and run the macro on each one, saving each file with its original name as a tab delimited file, with all of the changes i made in 1-3.

I keep getting a syntax error on this code:

Sub Macro4_eggplant()

    Dim newname as string
    newname = ThisWorkbook.FullName
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Columns("C:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "Filename.xls"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("Table4[Column1]")
    Range("Table4[Column1]").Select
    Range("D5").Select

    ActiveWorkbook.SaveAs Filename:= _
        "/Users/matthewhughes/Desktop/" "newname" ".txt" _
        , FileFormat:=xlText, CreateBackup:=False
End Sub

Answer 1

ThisWorkbook.FullName will return the fully qualified filename of the workbook containing your macros. Your following code (after fixing the syntax error, i.e. Desktop/" "newname" ".txt" should be Desktop/" & newname & ".txt") is expecting just the base filename (i.e. without path and extension), and should probably be referring to the ActiveWorkbook rather than the macro workbook.

Sub Macro4_eggplant()

    Dim newname as String
    With ActiveSheet
        newname = Left(.Parent.Name, InstrRev(.Parent.Name, ".") - 1)
        .Rows(1).Delete Shift:=xlUp
        .Columns("C:E").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Range("C2").FormulaR1C1 = "Filename.xls"  ' or = .Parent.Name ?
        .Range("C2").AutoFill Destination:=.Range("Table4[Column1]")

        .Parent.SaveAs Filename:= "/Users/matthewhughes/Desktop/" & newname & ".txt", _
                       FileFormat:=xlText, _
                       CreateBackup:=False
    End With
End Sub

(I assume Table4 exists somewhere.)