0

Hey I have got the code below. I wanna be able to open mutiple workbooks through application.getopenfilename and then extract the respective book names to compare them to a list.

I don't why but whenever I try to extract the workbook name the " Object required" error pop's up. In this exactract I used dir, but if I use name or path the result is the name, same error pop's up if I try to use Filenales (i) instead of active workbook. Could someone help me or explain me what actually happens? Thank you in advance,

Sub importDealflow()
    Dim twb As Workbook, aWB As Workbook
    Dim Userrange As Range, cell As Range
    Dim Defaultrange As Range
    Dim x As Long, Ent As Variant
    Dim FileNames() As Variant, nw As Integer
    Dim i As Integer
    Dim FN As String, pth() As Variant
    Dim Getbook As String
    
    Set twb = ThisWorkbook
    Set Userrange = twb.Sheets("Deal Workflow").Range("G5:G28")
    
    FileNames = Application.GetOpenFilename(FileFilter:="Excel Filter(*.xlsx),*xlsx", Title:="Open File(s)", MultiSelect:=True)
    nw = UBound(FileNames)
    
    For Each cell In Userrange
        If cell.Value <> "" Then
            Ent = Mid(cell, InStr(cell, "(") + 1, InStr(cell, ")") - InStr(cell, "(") - 1)
        End If
            
        For i = 1 To nw
            Workbooks.Open FileNames(i)
            Set aWB = ActiveWorkbook
            Set Getbook = Dir(ActiveWorkbook)
            If Left(pth, InStr(pth(i), "-" - 1)) = Ent Then
                MsgBox Ent
            End If
        Next i
    Next
End Sub
basodre
  • 5,720
  • 1
  • 15
  • 23
Brendan
  • 1
  • The Dir() function returns a String. The Set assignment keyword is only for objects. You don't need Set for a String. – DS_London Oct 05 '20 at 15:24
  • Thank for your help. However even when I remove set I still get error 438 Object does not support this property or method. – Brendan Oct 05 '20 at 15:50
  • Please try and format the question so that the code is more easily legible. It is just not clear what you are trying to achieve. – DS_London Oct 05 '20 at 16:07
  • I have an excel file where I want to importe data from different files which are in the same folder. Each line in my excel file matches one file which has the same name. I want to use application.getopenfile to open the different files, then use a for loop to go through the lines and match the right line with the right file and then import the data from this specific file into this line. The issue is when I use Dir,path or name on Filenames(i) which is the getopenfile array, it displays error 438. I hope this is clearer. – Brendan Oct 06 '20 at 07:56
  • You might need to spend a little time understanding the different datatypes in VBA, how to use the Debugger, and the reference for the functions you are using. But in the meantime, (1) ActiveWorkbook is a Workbook object, not a String. The Dir() function expects a String. (2) Change the code (no Set) to: Getbook = aWB.Name (3) Probably change Left(pth, ...) to Left(pth(i), .... – DS_London Oct 06 '20 at 10:10
  • For i = 1 To nw Workbooks.Open FileNames(i) Set aWB = ActiveWorkbook Getbook = aWB.Name If Left(FileNames(i), InStr(FileNames(i), "-" - 1)) = Ent Then MsgBox Ent Next i – Brendan Oct 07 '20 at 10:40
  • Thank you for your help, I updated my code according to your recomendation see above: The error Run-time 13 type mismatch pop's up when I run the workbooks.open (Filesnames(i)) line, could this be due to the fact that the file has actually links that cannot be updated? I does not work whether I use Getbook or Instr(FileNames(i) Thank you in advance, – Brendan Oct 07 '20 at 10:46

0 Answers0