Macro error 76 help

[fmiranda019]

Hello Community I am experiencing the following error can you help?

Sub All()

Sheets("Dictionary").Select
Range("B2:B150").Select
Selection.Copy


Sheets("Macro").Select
Range("E11").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False


Range("A1").Select


End Sub


Sub Carga()


'Variables
Dim i, j, n, p, b, w As Integer
Dim Path, qpwd As String
Dim f_exist As Boolean
Const nbl = 5850
Dim File(300), Firm(200), no_firm(200), todel(200)
Dim objFSO, objFile, objFolder
Dim xlBook, wb As New Workbook
Dim XlSheet1, XlSheet2, XlSheet3, XlSheet4 As New Worksheet


w = 2


'On Error Resume Next
Application.DisplayAlerts = False 'Remove excel alerts
Application.DefaultSaveFormat = 51
Namebook = ActiveWorkbook.Name


qpwd = InputBox("Type the password to unlock sheets", "Password")


'Path
Path = Range("G3").Value
If Right(Path, 1) <> "\" Then
 Path = Path & "\"
End If


'Clean
Sheets("Files").Select
Range("A1:B300").Delete


Set wb = ActiveWorkbook


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Path)
  
  'Initiate integers
b = 1
n = 1


 'Names of the firms in an array
 Sheets("Macro").Select
 LastRowE = Range("E" & Rows.Count).End(xlUp).Row
 
 For i = 11 To LastRowE
  Firm(i - 10) = Range("E" & i).Value
 Next
 
  Sheets("Files").Select
 'Paste all the names of the files from the path to Result sheet
 For Each objFile In objFolder.Files
  Range("A" & n).Value = objFile.Name
  n = n + 1
 Next
  
 Set objFolder = Nothing
 Set objFSO = Nothing


 'Looks if the firm name exists in the list of files with SF
 For i = 1 To n - 1
  If Range("A" & i).Value Like ("*SF*") And Range("A" & i).Value Like ("*.xls") And Not Range("A" & i).Value Like ("*$*") Then
   File(b) = Path & Range("A" & i).Value
   b = b + 1
   Range("B" & b).Value = Range("A" & i).Value
   End If
 Next


Range("A1:A300").ClearContents


'If no files with FormS exists, leave the macro
If b < 2 Then
 t = MsgBox("You don't have files with 'SF' inside the title, check the names or the path.", vbCritical + vbOKOnly, "Error")
 Exit Sub
End If


u = 1


With ActiveWorkbook
 If .Worksheets.Count >= 6 Then
  ts = .Worksheets.Count
 For m = 6 To ts
  todel(u) = Worksheets(m).Name
  u = u + 1
 Next
 For v = 1 To u - 1
  Worksheets(todel(v)).Delete
 Next
 End If
End With


'Open the files
For i = 1 To b - 1


 Workbooks.Open (File(i)), UpdateLinks:=0
 Set xlBook = ActiveWorkbook
 Set XlSheet1 = xlBook.Sheets("Title")
  
  f_exist = False
  XlSheet1.Activate
  Firm_Name = Range("A5").Value
  
  wb.Activate
  Sheets("Macro").Select
  
  For p = 1 To 150
   If Firm_Name Like ("*" & Firm(p) & "*") And Firm(p) <> "" Then
    f_exist = True
    p = 150
   End If
  Next


   If f_exist = False Then
    no_firm(w) = Firm_Name
    w = w + 1
    GoTo fin
   End If


 Set XlSheet2 = xlBook.Sheets("F28")
 
 xlBook.Activate
 ActiveWorkbook.Unprotect (qpwd)
 Sheets("F0").Activate
 DataF0 = Range("B15").Value
 
 XlSheet2.Select
 Sheets("F28").Copy After:=Workbooks(Namebook).Sheets("Macro")
 ActiveSheet.Name = Firm_Name
 ActiveSheet.Unprotect (qpwd)
 
 Range("C25:bn32").Select
 Selection.ClearContents
 Range("C43:bn50").Select
 Selection.ClearContents
 Range("C70:bn77").Select
 Selection.ClearContents
 Range("C97:bn104").Select
 Selection.ClearContents


 xlBook.Close False
 wb.Activate


 Sheets("detalle Red").Activate
 For j = 6 To 150
  If Range("G" & j).Value = Firm_Name Then
   Range("C" & j).Select
   ActiveCell.FormulaR1C1 = "=+'" & Firm_Name & "'!R17C47"
   Range("D" & j).Value = DataF0
   j = 150
  End If
 Next
 
 Sheets("Tipos de cambio mensuales").Activate
 For l = 5 To nbl
  If Range("A" & l).Value = Firm_Name Then
   Range("D" & l).Select
   ActiveCell.FormulaR1C1 = "=+HLOOKUP(RC2,'" & Range("A" & l).Value & "'!R39C3:R55C44,17,0)"
   Range("E" & l).Select
   ActiveCell.FormulaR1C1 = "=+HLOOKUP(RC2,'" & Range("A" & l).Value & "'!R66C3:R82C44,17,0)"
   Range("F" & l).Select
   ActiveCell.FormulaR1C1 = "=+HLOOKUP(RC2,'" & Range("A" & l).Value & "'!R93C3:R109C44,17,0)"
  End If
 Next


fin:
If f_exist = False Then
 xlBook.Close False
End If


Next


'Add the name of the firm which haven't been found in the list but were in the destination folder (as file)
wb.Activate
Sheets("Files").Select
For i = 2 To w - 1
 Range("C" & i).Value = no_firm(i)
Next


Sheets("Macro").Select
t = MsgBox("Done.", vbOKOnly + vbInformation, "Done")


End Sub

 

[fmiranda019]

The error happens here:

Set objFolder = objFSO.GetFolder(Path)

 

[fmiranda019]

anyone?

 

[Joe4]

It looks like it is saying that it is not a valid path.
See: https://msdn.microsoft.com/en-us/library/aa264532(v=vs.60).aspx
(Google searches are great tools for searching what these error codes mean).

So check what is in cell G3 and see if it really is a valid path that you have access to.

 

[fmiranda019]

Thanks Joe, I have checked the path. Because i knew it was an error related to it, but despite the fact I double checked i cannot find the error.

 

[BarryL]

what value are you getting for Path? whats the value in G3?

 

[Joe4]

Just before the line that is returning the error, add this line:

MsgBox Path

and see what that returns.
That will show you exactly what the value of it is just before you get the error.

I have seen situations where people are not in the file or on the sheet they think they are when a certain piece of code runs, so it does not produce the expected results (i.e. maybe you are pulling the value from G3 on a different sheet, and not the sheet that actually has the path in cell G3).

 

[fmiranda019]

ok bros, look.

I added MsgBox Path and got the Path.

C:\Users\ines\Desktop\test


and then: "You don't have files with 'SP' inside the title, check the names or the path.

Inside the test folder I have a file XLXS file that starts with SP

 

[Norie]

So is the GetFolder part now working?

 

[fmiranda019]

just before the err msg?