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

  1.
    yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   Ambassador 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
    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
      1. 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
    2. 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
      1. 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
    3. 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