Error 1004 - Open a workbook

Question

I try to open a workbook in a folder which a user decide by entering Dateconso. Open the workbook copy/paste in another workbook.

The problem is that the code breaks at opening the file.

Execution error 1004:'11400. 16.01.xlsm' not found.

Here the thing the name of the workbook is 11400.16.01.xlsm without any space between 11400. and 16 (which is Dateconso).

I understand that it cannot open this workbook because it doesn't exist...but this is not the workbook I want to open !!!

Sub consolidation()
        '
        ' Macro

        ' Déclaration des variables
          Dim wb As Workbook
          Dim myPath As String
          Dim myFile As String
          Dim Dateconso As String


         'Optimisation de la Macro Speed
         Application.ScreenUpdating = True
         Application.EnableEvents = True


        'Sélection de la date
          Dateconso = InputBox("Quelle date souhaitez-vous consolider?","Question")
         If Dateconso = "" Then Exit Sub 'Si rien exit le  programme


         'Trouve les fichiers qui on la date associée
         myFile = Dir("Z:\7. Personnel\Florian\Projet_BDC\Test\*.xlsm")

    While myFile <> ""
        If InStr(myFile, Dateconso) > 0 Then 'si tu trouve la date recherchée, alors ouvre le fichier puis copie toute puis colle
           Set wb = Workbooks.Open(Filename:=myFile)
           wb.Worksheets(1).Range("A1").Select
           Range(Selection, Selection.End(xlToRight)).Select
           Range(Selection, Selection.End(xlDown)).Select
           Selection.Copy
           Workbooks("Consolidation.xlsm").Worksheets(2).Activate
           ActiveSheet.Paste
           wb.Close
           Else: MsgBox "Fichiers introuvables, vérifiez le format de date entré" 'Si il ne trouve rien, préviens l'utilisateur
           Exit Sub
        End If
        myFile = Dir()
    Wend


End Sub

perhaps this is an obvious question, but is it possible the manual input in the InputBox was type with the extra space in '11400. 16.01.xlsm? You might also want for direct equality by using If myFile = Dateconso, instead of using Instr. This will rid you of simple manual type pages. — Scott Holtzman
There are no blank space when i selected 16 in Dateconso box. The thing is i tried to rename my file as 11400. 16.01.xlsm and i gets the same error... — Florian
I can't use myFile=Dateconso because Dateconso is a part of the name file.... for exemple, file is 1400.16.01 i want to find the file that have a name with 16 in it. — Florian
myFile = replace(myFile, chr(46) & chr(32), chr(46)) doesnt change the problem... — Florian

Unknown Unknown · Accepted Answer · 2016-01-22T19:05:15

This actually has nothing to do with the way that VBA is misreading the filename. In fact, the repair (e.g. extra space character) is necessary in a filenname containing periods for some reason. Leave the filename alone.

The problem is that you are not supplying the path along with the filename. The Dir function only returns the filename, not the full path. You need to add the path back in before using it.

Dim fp As String, fn As String

fp = "Z:\7. Personnel\Florian\Projet_BDC\Test\"
fn = Dir(fp & "*.xlsm")

While fn <> ""
    If InStr(fn, Dateconso) > 0 Then 'si tu trouve la date recherchée, alors ouvre le fichier puis copie toute puis colle
       Set wb = Workbooks.Open(Filename:=fp & fn)
            'do stuff
       wb.Close
       Else: MsgBox "Fichiers introuvables, vérifiez le format de date entré" 'Si il ne trouve rien, préviens l'utilisateur
       Exit Sub
    End If
    fn = Dir()
Wend

Error 1004 - Open a workbook

1 Answers