Marco Works from Menu but not with Call

[Shiseiji]

TIA everyone. Macro was started using a thread here then record macro to refine the details. When the macro is called from another macro, it breaks at .Refresh BackgroundQuery:=False One site suggested changing the line to .Refresh BackgroundQuery = False but that hasn't corrected the problem. If I remove the line the macro continues to break. Hope the correction is obvious to someone. Some of the Dim aren't used in this module, they remain so I can paste in other code later or remove them when everything is working.

Ron

Sub m_ImportAD_txt() 'Import the most recent AD report.
'
' ImportAD_txt
'
 ' https://www.mrexcel.com/forum/excel-questions/828654-visual-basic-applications-open-copy-paste-close.html
    '
    Worksheets("AD").Activate
    '
    Application.DisplayAlerts = False
    Application.DisplayStatusBar = False
    Application.ScreenUpdating = False
    Dim thiswb, otherwb As Workbook
    Dim thisws, otherws As Worksheet
    Dim LastRow As Long
    Dim LastCol As Integer
    Dim rng As Range
    '
    Set thiswb = ActiveWorkbook
    Set thisws = ActiveSheet
    Application.DisplayAlerts = False
    Call m_RemoveConnections
    thisws.Cells.ClearContents
    ChDir "\\cifs005\tbls\AD"
    '
    '
   MsgBox ("Select the most recent AD report to import.")
    
    Do
      fNameAndPath = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt", _
                     Title:="Select AD Report File To Be Opened")
    
    If fNameAndPath = "False" Then
        Exit Sub
    End If
    
    If InStr(fNameAndPath, "AD") = 0 Then
        MsgBox "You can only import an Active Directory report file.  Please select the most recent file with 'AD...' in the file name."
    End If
    '
    Loop Until InStr(fNameAndPath, "AD") <> 0
    '
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, Destination:=Range("$A$1"))
            .Name = "AD"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierNone
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "^"
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
    End With
BeforeExit:
    Application.ScreenUpdating = True
Exit Sub
ErrorHandle:
    MsgBox Err.Description
    Range("A1").Select
End Sub

 

[Norie]

Are you sure ActiveSheet and Range("$A$1") are referring to the correct sheet here?

 With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, Destination:=Range("$A$1"))
            .Name = "AD"

 

[Shiseiji]

Doesn't

[COLOR=#333333]Worksheets("AD").Activate[/COLOR]

ensure I'm using the correct sheet? I'm the only one who will be using this, Sheet1 will work just as well if it will make a difference. Or remove the .AD?

Edit:
I also tried

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, Destination:=Worksheets("AD").Range("$A$1"))

and it also errored.

R

 

[Norie]

Well it kind of should but it's always better to explicitly state the worksheets/workbooks you are trying to refer to.

Does this work any better?

With Sheets("AD")
  With .QueryTables.Add(Connection:="TEXT;" & fName, Destination:=.Range("$A$1"))
            .Name = "AD"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierNone
            .TextFileConsecutiveDelimiter = True
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileOtherDelimiter = "^"
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
               1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
    End With
End With

 

[Shiseiji]

Worked as a stand alone, and from a Call no error, but sadly, didn't import the data either. I ran into that earlier, but figured I had just really missed something.