View original (French)

Issue with the .Refresh BackgroundQuery:=True function

Solved
Aymericci -  
 Aymericci -
Hello everyone,

I just retrieved a program from another computer that allows me to fetch data from the measuring machine.

I would like to adapt the code to my computer and my machine, but it doesn't work.
When I run the macro, I get error 1004: Cannot find the text file to refresh this range of external data. Ensure that the text has not been moved or renamed, and try again.

I don't know much about VBA and I would like to know if anyone has a solution to my problem; I am copying below the part of the code that is malfunctioning. It is the last line Refresh BackgroundQuery := True that is highlighted in yellow during debugging.

Thank you for your help,

Aymeric

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

1 answer

Answer 1 / 1
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   Ambassadeur 1 588
 
Good evening, it seems that the error message indicates that it is impossible to find the text file you want to import.
At the time of debugging, what are the values of "location" and "fname"? You can find them by hovering the cursor over these names.
1
Aymericci
 
Hello, Thank you for your response. Regarding the location, I have G:\DATA\BATCH001\stat, which is the correct path for the data, but the value of fname is noted as empty!!!! I don't know what this means and where to fill it in...
0
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > Aymericci
 
If not present, add "Option Explicit" at the beginning of the module.
You're only showing us a part of the code, making it difficult for us to be more precise.
0
Aymericci
 
He tells me that the variable is not defined. Here is the 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
0
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > Aymericci
 
Are you sure that the Excel workbook was really working on the other computer?
What is the name of the file to import that is located in G:\DATA\BATCH001\stat?
Does it have the same name in the other folders?
By the way: add 
const fname as string = ".csv"
below 
Public Sub Count_Subfolders_Test()
0
Aymericci
 
Hi yg_be,

I finally found the error, it was in the access path, it wasn't batch but test, I was so used to talking about batch that I didn't realize it was translated for this instrument. In any case, thank you so much for your help !!
Happy holidays
0