Update Several Excel files in same location

Question

I saw a similar question where the code below was provided. But I am getting an error on the "With" line. I am using excel 2010. Please help.

Sub Auto_open_change()

Dim WrkBook As Workbook
Dim StrFileName As String
Dim FileLocnStr As String
Dim LAARNmeWrkbk As String

PERNmeWrkbk = ThisWorkbook.Name
StrFileName = "*.xlsx"
FileLocnStr = ThisWorkbook.Path
Workbooks.Open (FileLocnStr & "\" & StrFileName)
Workbooks(StrFileName).Activate

With Application.FindFile
SearchSubFolders = False
LookIn = "Network location"
Filename = "*.xlsm"
If .Execute > 0 Then
    Debug.Print "There were " & .FoundFiles.Count & " file(s) found."
    For i = 1 To .FoundFiles.Count
        ' added Set as per web cite, original did not have it
        Set WrkBook = Workbooks.Open(Filename:=.FoundFiles(i))
        WrkBook.Worksheets(1).Select
        ThisWorkbook.Worksheets(1).Cells(DestinationRange) = WrkBook.Worksheets(1).Cells(SourceRange).Value
    Next i
Else
    Debug.Print "There were no files found."

End If

Error image

Error Description:

Compile Error
With object must be user-defined type, Object, or Variant

Sorry, I was in a meeting. I got the code from this same site, another similar question. — Ernesto Salinas, Aug 04 '16 at 19:47
The error I am getting is "Complie error, WITH object must be user-defined type, Object or Variant" — Ernesto Salinas, Aug 04 '16 at 19:50
Where's the rest of the code? I don't see an End With or End Sub. — Kerry White, Aug 04 '16 at 20:07

score 0 · Answer 1 · edited May 23 '17 at 12:32

The code you want to open the workbook with will not work, as there is no valid name for the file. I would refer you to this for further guidance.

For the FindFile() try this:

With Application.FileSearch
    .SearchSubFolders = False
    .LookIn = "Network location"
    .Filename = "*.xlsm"

    If .Execute > 0 Then
        Debug.Print "There were " & .FoundFiles.Count & " file(s) found."
        For i = 1 To .FoundFiles.Count
            ' added Set as per web cite, original did not have it
            Set WrkBook = Workbooks.Open(Filename:=.FoundFiles(i))
            WrkBook.Worksheets(1).Select
            ThisWorkbook.Worksheets(1).Cells(DestinationRange) = WrkBook.Worksheets(1).Cells(SourceRange).Value
        Next i
    Else
        Debug.Print "There were no files found."

    End If
End With

JNevill · Answer 2 · 2016-08-04T20:46:44.260

0

I would suggest switching over to use DIR instead. Your implementation of Application.FindFile and your use of With both appear to be wrong.

Instead something like:

Sub test()
    Dim WrkBook As Workbook
    Dim strPath As String, strFile As String

    'Path to search for files
    strPath = "C:\"

    'Store the first found file that matches "*.txt" in strFile
    strFile = Dir(strPath + "*.xlsm")

    'Loop through files
    Do While strFile <> ""

        'Do your stuff here
        Set WrkBook = Workbooks.Open(strPath & "/" & strFile)
        WrkBook.Worksheets(1).Select

        'Doesn't look like DestinationRange or SourceRange are set up here yet
        ThisWorkbook.Worksheets(1).Cells(DestinationRange) = WrkBook.Worksheets(1).Cells(SourceRange).Value

        'Close the found workbook
        WrkBook.Close False
        'set strFile to the next matching file in the path
        strFile = Dir
    Loop


End Sub

edited Aug 04 '16 at 20:46

answered Aug 04 '16 at 18:18

JNevill

46,980
4
38
63

JNevill, Thanks for the code. I used it but it first gives me a Runtime error stating the directory could not be found. I changed it from "C:\" to "C:\Users\saliner\Documents\_Files\Projects\BOM Cost Compare\Export BOM Files\" and the macro stops in: "Set WrkBook = Workbooks.Open(strPath & "/" & StrFileName)" – Ernesto Salinas Aug 04 '16 at 20:21
Right before that line, add in `Debug.Print strpath, strFileName` and make sure, in your VBE, you go to View>>Immediate Window. The run the macro and it should print both values out so you can see what workbook/file it's trying to open. Optionally, you can hit the "Debug" button when the error pops up and hover over both of those variables in the code to see what their values are. That should give you some clue why that line is erroring out. – JNevill Aug 04 '16 at 20:31
I only get the path "C:\Users\saliner\Documents\_Files\Projects\BOM Cost Compare\Export BOM Files\ " not a file name – Ernesto Salinas Aug 04 '16 at 20:45
Oh! oh. Geez. YEa... wow. I'm sorry, Ernesto. I see that I messed that line up and used a bad variable. Should have stuck an `OPTION EXPLICIT` at the top of this code. Change that line to `Set WrkBook = Workbooks.Open(strPath & "/" & strFile)`. Terribly sorry for that one. :( – JNevill Aug 04 '16 at 20:46
I made the change. Now it gets stuck in "WrkBook.Worksheets(1).Select" . So let me clarify something, once the worksheet is open I need to perform some changes and then close and save the file. each file only has ONE sheet named the same as the file name (without the .xls). Below I will paste the section where it begins to stop – Ernesto Salinas Aug 04 '16 at 21:02
WrkBook.Worksheets(1).Select – Ernesto Salinas Aug 04 '16 at 21:03
Code Execution has been interrupted – Ernesto Salinas Aug 04 '16 at 21:22
but it gives me an option to continue. When I hit continue it does update the file, but after update it give me another error "Run Time: Application defined or object defined Error" and it stops at the following line " 'Doesn't look like DestinationRange or SourceRange are set up here yet ThisWorkbook.Worksheets(1).Cells(DestinationRange) = WrkBook.Worksheets(1).Cells(SourceRange).Value " – Ernesto Salinas Aug 04 '16 at 21:28
JNevill, did you have a chance to look at my error? – Ernesto Salinas Aug 05 '16 at 12:32
You didn't define "DestinationRange" or "SourceRange" variables. VBA can't guess for you. Not sure on the "Code Execution has been interrupted" That generally means you manually interrupted the script. – JNevill Aug 05 '16 at 13:29

Update Several Excel files in same location

2 Answers2