0

I currently have this code which will take files from a folder, open one each one, print its name into the first column of my "Master file" close it and loop through the entire folder that way.

In each file that is opened, there is information in cell J1 that I would like to copy and paste into column 3 of my "master file". The section of code currently returns an error (Object does not support this property or method but I cannot tell which line it is referring to) and causes the program to stop after only opening one file.

Any ideas?

FULL CODE:

Sub LoopThroughDirectory()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim MyFolder As String
    Dim Sht As Worksheet
    Dim i As Integer

    MyFolder = "C:\Users\trembos\Documents\TDS\progress\"

Set Sht = ActiveSheet

    'create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'get the folder object
    Set objFolder = objFSO.GetFolder(MyFolder)
    i = 1
    'loop through directory file and print names
    For Each objFile In objFolder.Files

        If LCase(Right(objFile.Name, 3)) <> "xls" And LCase(Left(Right(objFile.Name, 4), 3)) <> "xls" Then
        Else
            'print file name
            Sht.Cells(i + 1, 1) = objFile.Name
            i = i + 1
            Workbooks.Open Filename:=MyFolder & objFile.Name
        End If
        'Get TDS name of open file
        Range("J1").Select
        Selection.Copy
        Windows("masterfile.xlsm").Activate
        Range("C2").Select
        ActiveSheet.Paste
        objFile.Activate
        ActiveWorkbook.Close
    Next objFile


End Sub

Part of code that is messing up the program:

'Get TDS name of open file
Range("J1").Select
Selection.Copy
Windows("masterfile.xlsm").Activate
Range("C2").Select
ActiveSheet.Paste
objFile.Activate
pnuts
  • 58,317
  • 11
  • 87
  • 139
Taylor
  • 181
  • 1
  • 3
  • 24

2 Answers2

1

objFile.Activate is your issue.

objFile is not a workbook variable, it's being assigned a path\filename from objFolder.Files.

Use the following:

Dim NewWorkbook as Workbook
set NewWorkbook = Workbooks.Open Filename:=MyFolder & objFile.Name
.
.
.
NewWorkbook.Activate
ActiveWorkbook.Close

Now, instead of the last two lines, since you have a variable that's referencing the opened workbook, you can replace those two lines with this one:

NewWorkbook.Close

Read this link for some good advice on other ways to eliminate Activate, Select, etc to make your code cleaner, more readable, less likely to have bugs due to the wrong place having the focus and easier to maintain.

Community
  • 1
  • 1
FreeMan
  • 5,660
  • 1
  • 27
  • 53
  • Thank you! That makes a lot of sense. With your second line though, I tried inputting it and it highlights "Filename" in the "Filename:=MyFolder ..." section and comes back with an error: Expected: end of statement. – Taylor May 29 '15 at 19:36
  • My bad... `set NewWorkbook = Workbooks.Open (Filename:=MyFolder & objFile.Name)`. Needs parens () – FreeMan May 29 '15 at 19:41
  • Beautiful! Thanks for your help! – Taylor May 29 '15 at 19:43
  • Do you know how to work on the range for printing J1 in the opening files to the third column in a masterfile? – Taylor May 29 '15 at 19:48
  • Open a new question for that - SO is designed for one question per post. Plus, questions in comments are almost impossible to find. – FreeMan May 29 '15 at 19:51
0

I think the problem is due to unqualified references. Specifically, I am not sure if you can paste a selection from a not-active sheet to a newly active sheet, but I am not sure because I avoid use of .Select and .Activate so I don't have problems.

Try replacing the problematic section with this:

Sht.Range("J1").Copy Workbooks("masterfile.xlsm").Sheets(1).Cells(2,3)
objFile.Activate
puzzlepiece87
  • 1,537
  • 2
  • 19
  • 36