Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Application Workbooks.Open run-time error 1004 select method of worksheet class failed

0

Hi

I'm hopping somebody can help me to move forward with my code

I get the above error when trying to run the below code

It errors at this line:

Workbooks.Open Filename:=SelectedFile

When I select "End" or "Debug" the error, the file opens but the code stops running

This code is part of a biger one. And you can see when the file gets open I Call for another sub to copy the data from open file to ThisWorkbook, but I cannot get to that point because I'm stuck at opening the files.

Alos, just for information, the files that I'm opening have their own macro in it (not needed for this purposes) and may or may not be a large files. The files stored in shared folder. I need to copy data from one tab in each file to summurise all in ThisWorkbook. But as I'm saying the errors stops me from moving forward.

Thank you

Option Explicit

Dim VendorReconciliationSummary As Workbook

Dim VendorReconciliationIndividual As Workbook

Dim SelectedFile As Variant

Sub APweeklyReport()

Set VendorReconciliationSummary = ThisWorkbook

Application.ScreenUpdating = False

With Application.FileDialog(msoFileDialogFilePicker)

                .Title = "Choose data files to load..."

                .AllowMultiSelect = True

                .Filters.Clear

If .Show = -1 Then                                             

  For Each SelectedFile In .SelectedItems

        MsgBox (SelectedFile)                                           'just to check                      

        Set VendorReconciliationIndividual = ActiveWorkbook

         Workbooks.Open Filename:=SelectedFile        

            Call CopyVendorRecIndividualtoSummary

            VendorReconciliationIndividual.Close SaveChanges:=False

        Next SelectedFile

    Else                                                    

    End If

End With

End Sub

excel
posted by: Oksana on Thu Mar 15, 2018 at 1:04 am
Answer
Discuss

Answers

0

Your 'Selected File' is of variant datatype, meaning it can be anything you (or your macro) assign to it. Your code assigns a member of a the 'SelectedItems' collection to it. Bear in mind that 'For Each' only works with objects. Therefore 'SelectedFile' is assigned an object.

The 'Workbook.Open' class requires 'FileName' to be a string. So, in fact, you are asking VBA to convert the object you have assigned to 'SelectedFile' to a string. Obviously (since you get an error) that is more than VBA is willing to do.

You might try 'FileName:=CStr(SelectedFile)'. Perhaps that will work. However, I would avoid the 'For Each' loop on the grounds that 'SelectedItems' isn't an object and shouldn't be treated as one, because doing so generates errors of the type that brought you here.

    Dim SelectedFile As String
    Dim i As Long

    For i = 0 To UBound(SelectedeItems)
        SelectedFile = SelectedItems(i)
        Workbooks.Open Filename:=SelectedFile
    Next i

I think you can even do without 'SelectedFile' entirely. Just say 'Workbooks.Open Filename:= SelectedItems(i)' or, if that should give a problem, which I doubt, 'CStr(SelectedItems(i))'.

posted by: Variatus (rep: 4889) on Thu Mar 15, 2018 at 3:56 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login