CODEWITHSUNDEEP
excelvba
M. Laurencon
M. Laurencon

Reputation: 1

Excel VBA - Runtime error 1004 file could not be found

In a part of my program, I want to open existing Excel files via VBA in order to modify it and manipulate data.

 'Declaration des variables d'objects Excel
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim Fname As String
        'Declaration des variables de calcul
        Dim a As Double
        Dim numimpact, nummatrix, debut, fin, e, n As Long
        Dim i As Boolean

        'Initialisation des variables
        i = True
        a = 0
        e = 1
        numimpact = 1
        nummatrix = 1
        debut = 2
        n = 1000
        fin = debut + n
        'Boucle de lecture de tous les fichiers Excel
        Do While i = True
            'Test et incrementation des fichiers Excel
            If numimpact < 7 Then
                'Ouverture fichiers
                Fname = "D:\mmLaurencon\Desktop\NL\Test\CFRP1\"
                Set wb = Workbooks.Open(Fname & "CFRP1-" & nummatrix & "-" & numimpact & ".xlsm")
                'Set wb = Workbooks.Open("D:\mmLaurencon\Desktop\NL\Test\CFRP 1\CFRP1-1-" & numimpact)
                Set ws = wb.Worksheets(1)
                    'Parcourir colonne B
                    Do While Cells(e, 2).Value <> ""
                        For Each e In Columns(2)
                            Cells(fin, 3).Value = Application.Sum(Cells(debut, 2).Value, Cells(fin, 2).Value) / n
                            debut = debut + 1
                            fin = fin + 1
                            e = e + 1
                            'save the file
                            ActiveWorkbook.SaveAs Filename:= _
                            "D:\mmLaurencon\Desktop\NL\Test\CFRP " & nummatrix & "\CFRP1-1-" & numimpact & ".xlsm", _
                            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
                            'close the file
                            wb.Close
                        Next
                        numimpact = numimpact + 1
                    Loop
            ElseIf numimpact = 7 Then
                nummatrix = nummatrix + 1
                numimpact = 1
            ElseIf nummatrix = 10 Then
                i = False
            End If

        Loop

I made this code, but a runtime error 1004 File could not be found appears on line Set wb = Workbooks.Open (Fname & "CFRP1-" & nummatrix & "-" & numimpact & ".xlsm"). I don't understand why as I indicated the right path and file. I tried another way of doing this Set wb = Workbooks.Open("D:\mmLaurencon\Desktop\NL\Test\CFRP 1\CFRP1-1-" & numimpact) but a new runtime error 1004 the document may be read-only or encrypted appears.

Have you an idea about what is going wrong? Thank you in advance!

Upvotes: 0

Views: 2440

Answers (1)

Kwakkers
Kwakkers

Reputation: 71

This line Set wb = Workbooks.Open(Fname & "CFRP1-" & nummatrix & "-" & numimpact & ".xlsm") is trying to open file D:\mmLaurencon\Desktop\NL\Test\CFRP1\CFRP1-1-1.xlsm and is entirely different to your second attempt Set wb = Workbooks.Open("D:\mmLaurencon\Desktop\NL\Test\CFRP 1\CFRP1-1-" & numimpact).

The second attempt is missing the file extension, and the file path is different. The file path differs at this point D:\mmLaurencon\Desktop\NL\Test\CFRP1 compared to D:\mmLaurencon\Desktop\NL\Test\CFRP 1

I assume that both attempts were supposed to open the same file? You will need to correct the file paths - I'm not sure which is the correct one.

Upvotes: 1

Related Questions