VBA .Refresh BackgroundQuery:=False and Runtime error 1004

Question

Hi,

Running VBA code to open csv files one at a time within a directory. The loop in the code (below) is supposed to loop forward through csv files one at a time, using a button called 'Selected_next.'

In the first part of the loop, I am using an older section of code from a different project to display the csv data in the worksheet. 

This code works on its own but not within the loop, as I get Runtime error 1004 from the .Refresh BackgroundQuery:= False line.

Am I doing something wrong with the way that the variable myFile is declared/structured?

CODE BELOW ------------------------------------------------------------------------------------------------

Sub Iterate_through()

'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them

Dim wb As Workbook

Dim myPath As String

Dim myFile As String 'This should be the same as fStr from old_method

Dim myExtension As String

Dim FldrPicker As fileDialog

' Button controls

Dim Selected_next As Boolean

Dim Selected_back As Boolean

'Optimize Macro Speed

Application.ScreenUpdating = False

Application.EnableEvents = False

Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User

Set FldrPicker = Application.fileDialog(msoFileDialogFolderPicker)

With FldrPicker

      .Title = "Select A Target Folder"

      .AllowMultiSelect = False

        If .Show <> -1 Then GoTo NextCode

        myPath = .SelectedItems(1) & ""

End With

'In Case of Cancel

NextCode:

  myPath = myPath

  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")

  myExtension = "*.csv*"

'Target Path with Ending Extention

  myFile = Dir(myPath & myExtension) 'This might be the line causing issues

'----- LOOP FUNCTION HERE ----------------------------------------------------

' the selected file is a directory based off My path and Extension as above

'Loop through each Excel file in folder

  Do While myFile <> ""

    'Set variable equal to opened workbook

      MsgBox "A file has been acknowledged"

      If Selected_next = False Then

      MsgBox "You have chosen to wait" 'Test message (shows button off)

      MsgBox "Data will now be printed" 'Test message (indicates out of with loop)

      'TEST CODE //////////////////////

      With ActiveSheet.QueryTables.Add(Connection:= _

         "TEXT;" & myFile, Destination:=Range("$A$11")) 'this lines open the selection window

        .Name = "CAPTURE"

        .FieldNames = True

        .RowNumbers = False

       .FillAdjacentFormulas = False

        .PreserveFormatting = True

        .RefreshOnFileOpen = False

        .RefreshStyle = xlInsertDeleteCells

        .SavePassword = False

        .SaveData = True

        .AdjustColumnWidth = False

        .RefreshPeriod = 0

        .TextFilePromptOnRefresh = False

        .TextFilePlatform = 850

        .TextFileStartRow = 1

        .TextFileParseType = xlDelimited

        .TextFileTextQualifier = xlTextQualifierDoubleQuote

        .TextFileConsecutiveDelimiter = False

        .TextFileTabDelimiter = False

        .TextFileSemicolonDelimiter = False

        .TextFileCommaDelimiter = True 'this line delimits the data properly

        .TextFileSpaceDelimiter = False

        .TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1) 'sets HUMS data array size

        .TextFileTrailingMinusNumbers = True

        .Refresh BackgroundQuery:=False

         'Line above changed to true

         MsgBox "Printed data was attempted" 'Test message (indicates printing was attempted)

    End With

    MsgBox "DO NOT PROCEED: Break code now", vbExclamation 'Test message (indefinite loop starts here)

      'TEST CODE //////////////////////

      Else

      'DoEvents

      'ABOVE: Do events would normally run

      'Ensure Workbook has opened before moving on to next line of code

      'Get next file name

      'myFile = Dir

      End If

  Loop

'----- LOOP FUNCTION HERE ----------------------------------------------------

'Message Box when tasks are completed

  MsgBox "Task Complete!"

ResetSettings:

  'Reset Macro Optimization Settings

    Application.EnableEvents = True

    Application.Calculation = xlCalculationAutomatic

    Application.ScreenUpdating = True

End Sub

Answer 1

Really hard to say what the problem might be. You indicate that the code works OK without the loop. Therefore, it appears that maybe the loop is causing it.

Could also try a wait command before and/or after  .Refresh BackgroundQuery:=False to give the system time to catch up.

Application.Wait(Now + TimeValue("0:00:03")).Refresh BackgroundQuery:=False

Application.Wait(Now + TimeValue("0:00:03"))

What occurs if you comment out the .Refresh .BackgroundQuery:=False?  Does it cause other problems?

The Error 1004 usually means that the the code can't identify a reference of some sort. Have a look at the following link and see if you can identify anything that might relate to the problem.

https://www.wallstreetmojo.com/vba-1004-error/

Answer 2

Thankyou for your help!

I made the change and commented out:

Application.Calculation = xlCalculationManual

The problem was still present with the change, as such I then set the calculation to automatic with DoEvents, however it appears the issue still persists.

Answer 3

You create the same query in the same cell again and again and again, that would never work.

Andreas.

Sub Test()

  Dim Path As String, FName As String

  With Application.FileDialog(msoFileDialogFolderPicker)

    If Not .Show Then Exit Sub

    Path = .SelectedItems(1)

    If Right(Path, 1) <> "" Then Path = Path & ""

  End With

  FName = Dir(Path & "*.csv")

  Do While FName <> ""

    If MsgBox("Open " & FName & "?", vbOKCancel) = vbCancel Then Exit Sub

    Workbooks.OpenText Path & FName, Comma:=True, _

      FieldInfo:=Array(2, 1, 1, 1, 1, 1, 1)

    FName = Dir

  Loop

End Sub

Answer 4

Cannot test without the project so only speculating on what might be a problem.

Try commenting out Application.Calculation = xlCalculationManual and see what happens with the Refresh.

You might even be able to set Calculation to automatic just before Refresh and back to manual after the refresh. Also include DoEvents immediately after setting Calculation to Automatic.