PB avec la fonction .Refresh BackgroundQuery:=True

Bonjour, Merci de ta réponse, au niveau de location j'ai G:\DATA\BATCH001\stat qui est le chemin correct de l'emplacement des donneés mais la valeur de fname est noté vide !!!! et je ne sais pas ce que cela signifie et ou la renseigner...

Il me dit que la variable n'est pas définie. Voici le code :

Option Explicit
Public Sub Count_Subfolders_Test()





Dim oFso As Object
Dim amountfiles As Integer
Dim IQ As String


IQ = Cells(10, 1).Text


Set oFso = CreateObject("Scripting.FileSystemObject")
amountfiles = Count_Subfolders(oFso, IQ & ":\DATA\")

Set oFso = Nothing

Dim Batch As Integer

'Starting loop function

Dim i As Integer
Dim name As Integer
Dim file As String
Dim location As String



For i = 1 To amountfiles

name = 0 + i

If i <= 9 Then
file = "BATCH" & ".00" & name
location = IQ & ":\DATA" & "\" & file & "\" & "stats"

Else

If i <= 99 Then
file = "BATCH" & ".0" & name
location = IQ & ":\DATA" & "\" & file & "\" & "stats"

Else

file = "BATCH" & "." & name
location = IQ & ":\DATA" & "\" & file & "\" & "stats"

End If
End If





With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & location & fname, Destination:=Range("A1"))
'With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & "H:\DATA\BATCH.001\stats.csv" & fname, Destination:=Range("A1"))

.name = "stats"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=True
End With

Next i



' delete columns
Dim del As Integer

For i = 1 To amountfiles

del = 3 + i

Columns(del).Select
Selection.delete Shift:=xlToLeft

Next i

Range("C3:HC43").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C3:HC3") _
, SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"BATCH.001,BATCH.002", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("C3:HC43")
.Header = xlGuess
.MatchCase = True
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-15
Range("B2").Select

End Sub



Private Function Count_Subfolders(oFso As Object, folderPath As String) As Long

Dim Folder As Object, Subfolder As Object

Set Folder = oFso.GetFolder(folderPath)

Count_Subfolders = 0
For Each Subfolder In Folder.Subfolders
Count_Subfolders = Count_Subfolders + 1 + Count_Subfolders(oFso, Subfolder.path)
Next

End Function

Salut yg_be,

J'ai finalement trouvé l'erreur, elle etait dans le chemin d'acces, c etait pas batch mais essai, tellement l 'habitude de parler de batch que je n'avais pas réaliser que sur cet instrument c'était traduit. En tout cas je te remercie bcp pr ton aide !!
Bonne fetes