Variabele verwijzing in Dir functie werkt niet

[RobinVT88]

Hallo Allemaal,

Ik ben nog een echte beginner met VBA en probeert onderstaande werkende code om te zetten naar een variabele verwijzing.
Het probleem is dat de uitkomst van de aangepaste code telkens is: 'Het bestand bestaat niet!', terwijl als ik deze in de eerste code invoer deze aangeeft dat het bestand wel bestaat (en dat is ook zo).

Zouden jullie mij kunnen helpen bij wat ik in de aangepaste code verkeerd heb gedaan?

Alvast dank voor de feedback.

Werkende code:

Sub controle_bestand_test()
 
    If Dir("R:\BNLX-SCM\BNLX-SCM EA\BNLX all stock per day archive\BNLXallstockperday-05112020.xlsx") <> "" Then
 
        MsgBox "Het bestand bestaat!"
 
    Else
 
        MsgBox "Het bestand bestaat niet!"
 
    End If
 
End Sub

De aangepaste code die niet werkt is hieronder weergegeven: het bestand bestaat, maar de code geeft aan dat deze niet bestaat.
In cell 20, 2 staat de tekst '05112020'.
Een deel van een andere VBA code die ik heb geschreven haalt informatie op uit een bestand op basis van cell 20,2 en dit werkt.
Dus aan de opmaak van cell 20,2 kan het niet liggen.

Aangepaste code

Sub exhelp_controle_bestand()

Dim dag1 As String
Dim Pad As String
Dim fileext As String

Pad = " 'R:\BNLX-SCM\BNLX-SCM EA\BNLX all stock per day archive\BNLXallstockperday-"
dag1 = Cells(20, 2)
fileext = " .xlsx "
 
    If Dir(" & Pad & dag1 & fileext ") <> "" Then
     
        MsgBox "Het bestand bestaat!"
 
    Else
 
        MsgBox "Het bestand bestaat niet!"
 
    End If
 
End Sub

 

[edmoor]

Dat komt omdat je alles tussen aanhalingstekens hebt staan.
Doe het eens zo:

If Dir(Pad & dag1 & fileext) <> "" Then

Haal ook die spatie met enkele quote uit de waarde voor de variabele Pad.
Die spaties voor en achter .xlsx horen er ook niet.

En dan is dit voldoende:

Sub exhelp_controle_bestand()
    Dim dag1 As String
    Dim Pad As String
    Dim fileext As String
    
    Pad = "R:\BNLX-SCM\BNLX-SCM EA\BNLX all stock per day archive\BNLXallstockperday-"
    dag1 = Cells(20, 2)
    fileext = ".xlsx"
 
    MsgBox IIf(Dir(Pad & dag1 & fileext) <> "", "Het bestand bestaat", "Het bestand bestaat niet!")
End Sub

Als je geen Option Explicit gebruikt kunnen die Dim opdrachten ook nog weg.

 

[OctaFish]

En drie variabelen gebruiken om één string vast te leggen is ook een beetje onzinnig. Zeker als de lengte ven één variabele net zo lang is als de tekst die je er in zet. Kan makkelijk met één variabele.

    Pad = "R:\BNLX-SCM\BNLX-SCM EA\BNLX all stock per day archive\BNLXallstockperday-" & Cells(20, 2) & ".xlsx"

    MsgBox IIf(Dir(Pad) <> "", "Het bestand bestaat", "Het bestand bestaat niet!")

 

[edmoor]

Uiteraard.
En in dat geval is ook de variabele Pad niet nodig:

Sub exhelp_controle_bestand()
    MsgBox IIf(Dir("R:\BNLX-SCM\BNLX-SCM EA\BNLX all stock per day archive\BNLXallstockperday-" & Cells(20, 2) & ".xlsx") <> "", "Het bestand bestaat", "Het bestand bestaat niet!")
End Sub

 

[VenA]

'Het bestand bestaat' 2 x typen is overbodig :d

 

[HSV]

Maak gebruik van de 2e parameter in 'Dir'. (16 of vbdirectory).

 

[OctaFish]

Daarmee check je een directory, niet een bestand.

 

[HSV]

Klopt, ik gebruik het soms in twee keer om meerdere bestanden aan te maken in een nieuwe map, maar dat was de vraag dus niet (vandaar).

MsgBox "het bestand bestaat" & IIf(Dir([COLOR=#3E3E3E]"R:\BNLX-SCM\BNLX-SCM EA\BNLX all stock per day archive\BNLXallstockperday-" [/COLOR]& Cells(20, 2) & ".xlsx") <> "", "!", " niet!")

 

[snb]

MsgBox "Bestaat het bestand ?  " & Format(Len(Dir("R:\BNLX-SCM\BNLX-SCM EA\BNLX all stock per day archive\BNLXallstockperday-" & Cells(20, 2) & ".xlsx")), "yes/no")

 

[RobinVT88]

Bedankt allemaal voor de reacties.
Ik heb de code nu aangepast en deze werkt!

Reden waarom ik ervoor gekozen had een variabele voor de 'dag' aan te maken is omdat er data opgehaald moet worden uit een flink aantal bestanden met verschillende datums. Hieronder het voorbeeld voor dag-7 (dus de huidige datum -7).

Wellicht zou onderstaande code een stuk eenvoudiger kunnen, maar ik merk ook dat de somproduct functie erg traag is.
Met de ingebouwde timerfunctie duurt het nu 1:31 minuten om: uit 7 bestanden 35 maal data te halen (5 maal een somproduct per bestand).

Ik zou de code graag sneller maken en vermoed dat dit kan door de bestanden op de achtergrond te openen, een sumif toe te passen en dan de bestanden weer te sluiten. Maar voordat ik daar mee aan de slag ga zou ik graag van jullie weten: is dat inderdaad de snelste manier om de 35 gegevens op te halen? Of is daar een betere manier voor?

' Dag -7

    Range("B7").Select
    If Dir(Pad & dag7 & fileext) <> "" Then
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((" & Padv & dag7 & ".xlsx]Basic'!R2C19:R30000C19=R2C1)*(" & Padv & dag7 & ".xlsx]Basic'!R2C2:R30000C2=R7C1)*+" & Padv & dag7 & ".xlsx]Basic'!R2C8:R30000C8)"
    Else
    ActiveCell.FormulaR1C1 = "geen data"
    End If
      
    Range("B8").Select
    If Dir(Pad & dag7 & fileext) <> "" Then
     ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((" & Padv & dag7 & ".xlsx]Basic'!R2C19:R30000C19=R2C1)*(" & Padv & dag7 & ".xlsx]Basic'!R2C2:R30000C2=R8C1)*+" & Padv & dag7 & ".xlsx]Basic'!R2C8:R30000C8)"
    Else
    ActiveCell.FormulaR1C1 = "geen data"
    End If
    
    Range("B9").Select
    If Dir(Pad & dag7 & fileext) <> "" Then
         ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((" & Padv & dag7 & ".xlsx]Basic'!R2C19:R30000C19=R2C1)*(" & Padv & dag7 & ".xlsx]Basic'!R2C2:R30000C2=R9C1)*+" & Padv & dag7 & ".xlsx]Basic'!R2C8:R30000C8)"
    Else
    ActiveCell.FormulaR1C1 = "geen data"
    End If
    
    Range("B10").Select
    If Dir(Pad & dag7 & fileext) <> "" Then
         ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((" & Padv & dag7 & ".xlsx]Basic'!R2C19:R30000C19=R2C1)*(" & Padv & dag7 & ".xlsx]Basic'!R2C2:R30000C2=R10C1)*+" & Padv & dag7 & ".xlsx]Basic'!R2C8:R30000C8)"
      Else
    ActiveCell.FormulaR1C1 = "geen data"
    End If
    
    Range("B11").Select
    If Dir(Pad & dag7 & fileext) <> "" Then
       ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((" & Padv & dag7 & ".xlsx]Basic'!R2C19:R30000C19=R2C1)*(" & Padv & dag7 & ".xlsx]Basic'!R2C2:R30000C2=R11C1)*+" & Padv & dag7 & ".xlsx]Basic'!R2C8:R30000C8)"
      Else
    ActiveCell.FormulaR1C1 = "geen data"
    End If