Failure to compile - due to "Selection"

**StA** · 12-16-2010, 09:43 AM

Hi everyone,

Can anyone help with the following problem please. The macro below should open some files, copy data into excel, save the excel file and then cycle round. However, it fails to compile/run with the message:
"Compile error: Expected Function or variable"
and points to the first occurence of the word selection, ie the line

Range(Selection, Selection.End(xlDown)).Select

If I comment that line, it just makes it point to the next occurence of selection and so on.

I do not understand why it does this as almost identical code in other workbooks functions fine. Is there something really obvious I am missing, such as changing some setting somewhere?
Any help or advice would be much appreciated.

The code is as follows:

Sub FileOpener()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim File_Names As Variant
Dim FFF As String
Dim Temp_File_Name As String
Dim Source_Temp_File_Name As String
Dim i As Integer
Dim Full_Path_Name As String
Dim Text_File_Name As Variant
Dim Name_for_File As String

ChDrive ("C")
ChDir ("C:\Projects\Data")
FFF = "Excel Files (*.xls), *.xls," & "Text Files (*.txt), *.txt," & "All files (*.*), *.*"
File_Names = Application.GetOpenFilename(FFF, 2, "Select files for import ...", , MultiSelect:=True)

If IsArray(File_Names) = False Then
If File_Names = False Then
MsgBox "Import cancelled by user"
Exit Sub
End If
End If

For i = LBound(File_Names) To UBound(File_Names)
Source_Temp_File_Name = ThisWorkbook.Name
Full_Path_Name = File_Names(i)
Text_File_Name = Split(Full_Path_Name, ".")
Name_for_File = Text_File_Name(0) & ".xls"

Workbooks.Open File_Names(i)
Temp_File_Name = ActiveWorkbook.Name
Windows(Temp_File_Name).Activate
Range("B2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows(Source_Temp_File_Name).Activate
Sheets("Data").Activate
Range("P3:Q2000").Select
Selection.Clear
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("P3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("C1").Select
Windows(Temp_File_Name).Close
Windows(Source_Temp_File_Name).Activate
ActiveWorkbook.SaveAs Name_for_File

Next

End Sub

**TMShucks** · 12-16-2010, 09:59 AM

The good news: The line works in isolation.

Your code also works for me, barring the fact that I am missing folders, files and worksheets that are referred to in the code.

The bad news: I think you'll need to step through the code and check that all the variables have the values you expect them to have.

Regards

**shg** · 12-16-2010, 11:36 AM

I think your code could be simplified:

Sub FileOpener()
    Const sPath     As String = "C:\Projects\Data"
    Dim avsFile     As Variant
    Dim vsFile      As Variant

    Application.ScreenUpdating = False

    ChDrive sPath
    ChDir sPath
    avsFile = Application.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _
                                          Title:="Select files for import ...", _
                                          MultiSelect:=True)
    If Not IsArray(avsFile) Then
        MsgBox "Import cancelled by user"
        Exit Sub
    End If

    For Each vsFile In avsFile
        With ThisWorkbook.Worksheets("Data").Range("P3:Q2000")
            .Clear
            .Interior.ColorIndex = 36
            .Interior.Pattern = xlSolid
        End With

        Workbooks.Open vsFile

        With Range("B2:C2")
            .Range(.Cells, .Cells.End(xlDown)).Copy
            ThisWorkbook.Worksheets("Data").Range("P3").PasteSpecial Paste:=xlPasteValues
            .Worksheet.Parent.Close SaveChanges:=False
        End With
        
        ThisWorkbook.SaveAs Left(vsFile, InStrRev(vsFile, ".")) & "xls"
    Next
End Sub

Please take a few minutes to read the forum rules about CODE tags and use them in future posts.

Thanks.