VBA run time error 76

[MacNala]

I am getting the run-time error 76 on the line
Set f = fs.GetFolder(FolderName)
the ActiveWorkBook is called 2022M12.xlsm
the folder ""D:\SMA\Sunny Explorer\Data\Daily\" & ActiveWorkBookYear (=2022) contains a lot of CSV files to be imported one at a time
and then manipulated to a standard format before being graphed.
I am now trying to catch up on files from December 2022
The original script was running correctly

Sub GetCSVFiles()
'
    ActiveWorkBookPath = ActiveWorkbook.Path
    ActiveWorkBookName = ActiveWorkbook.Name
    ActiveWorkBookYear = Left(ActiveWorkBookName, 4)
    ActiveWorkBookMonth = Mid(ActiveWorkBookName, 6, 2)
    ActiveWorkBookMonthList = "JanFebMarAprMayJunJulAugSepOctNovDec"
    ActiveWorkBookMonthTxt = Mid(ActiveWorkBookMonthList, ((Val(ActiveWorkBookMonth) - 1) * 3) + 1, 3)
    SheetCount = ActiveWorkbook.Sheets.Count
    FolderName = "D:\SMA\Sunny Explorer\Data\Daily\" & ActiveWorkBookYear
    FileType = ".csv"
    Dim fs, f, f1, fc, s
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(FolderName)
    Set fc = f.Files
    For Each f1 In fc
        If Right(f1, Len(FileType)) = FileType Then
            If Mid(f1, 73, 4) = ActiveWorkBookYear And Mid(f1, 77, 2) = ActiveWorkBookMonth Then
                Workbooks.Open (f1)
                FormatCSV2XLS (SheetCount)
                SheetCount = SheetCount + 1
            End If
        End If
    Next
    FormatMaxAvg (SheetCount)
    CreateChart
    Application.ScreenUpdating = True
'
End Sub

 

[rlv01]

It is likely that the variable FolderName does not resolve to a valid folder

(Tip: when posting code, please try to use 'code tags' to format the code

How to Post Your VBA Code

as it makes the code easier to read.)

 

[MacNala]

Could you please define "does not resolve to valid folder".
The folders on my D drive can be seen in the attached image, thanks for the reply.

 

[MacNala]

I have found my error it was a naming problem the folder name was misspelled.

 

[HaHoBe]

Hi MacNala,

you should work a little bit on the code as a lot of lines may be deleted. I included a check for the folder and a message instead of letting a runtime error being raised:

Sub GetCSVFiles()
'https://www.mrexcel.com/board/threads/vba-run-time-error-76.1230324/
'
Dim wbAct As Workbook
Dim strYear As String
Dim strMonth As String
Dim lngNrSht As Long
Dim strFolder As String
Dim strExt As String
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim objFilesPres As Object

Set wbAct = ActiveWorkbook

strYear = Left(wbAct.Name, 4)
strMonth = Mid(wbAct.Name, 6, 2)

lngNrSht = wbAct.Sheets.Count
strFolder = "D:\SMA\Sunny Explorer\Data\Daily\" & ActiveWorkBookYear
strExt = ".csv"

If Dir(strFolder, vbDirectory) <> "" Then
  Set objFSO = CreateObject("Scripting.FileSystemObject")
  Set objFolder = objFSO.GetFolder(strFolder)
  Set objFilesPres = objFolder.Files
  For Each objFile In objFilesPres
    If Right(objFile, Len(strExt)) = strExt Then
      If Mid(objFile, 73, 4) = strYear And Mid(objFile, 77, 2) = strMonth Then
        Workbooks.Open (objFile)
        FormatCSV2XLS (lngNrSht)
        lngNrSht = lngNrSht + 1
      End If
    End If
  Next objFile
  FormatMaxAvg (lngNrSht)
  CreateChart
  Set objFilesPres = Nothing
  Set objFolder = Nothing
  Set objFSO = Nothing
Else
  MsgBox "Please check the Path and Folder for " & strFolder, vbInformation, "Folder not found"
End If
Application.ScreenUpdating = True
Set wbAct = Nothing
'
End Sub

There may be more action to take but maybe this can be a strating point.

Ciao,
Holger

 

[rlv01]

I have found my error it was a naming problem the folder name was misspelled.

Yes, that's often what does it. We've all been there. .

It's usually a good idea to add a test to see if the folder you are depending on actually exists. In your case it could be something similar to this this:

    If Not fs.FolderExists(FolderName) Then
        MsgBox "Folder not found:" & vbCr & FolderName, vbOKOnly Or vbExclamation, "File Folder Error"
        Exit Sub
    End If

That would have alerted you that something was wrong with the name.