I Think I Need a "Static" But I'm Not Sure

[Shiseiji]

I'm trying to render the fNameAndPath in the code below into a cell on the worksheet the text was imported onto.

I "think" one option is to set the fNameAndPath as a static variable so it can be reused. But . . the code below isn't failing, but neither is it putting the fNameAndPath in the desired cell.

Thanks for taking a look.

Ron

Sub tm_ImportPastUsersText()
'Import Active Directory file
' Validated mm-dd-yyyy
'
    
    Application.DisplayAlerts = False
    Application.DisplayStatusBar = False
    Application.ScreenUpdating = False
    '
    Sheets("Past User Accounts").Activate
    '
    Dim LastCol                     As Integer
    Dim LastRow                     As Long
    Dim FileName                    As Variant
    Dim fNameAndPath                As Variant
    Dim wbk1                        As Workbook
    Dim wks                         As Worksheet
    Dim Thisws                      As Worksheet
        
    Application.Volatile True
    '
    Static Import_FileName
    Import_FileName = fNameAndPath
    '
    Set Thisws = ActiveSheet
    '
    ChDrive "S:"
    ChDir "\AD_Listing\Raw_AD_Users\"
    '
    '----------------------------------------------------------
    MsgBox ("Select the most recent AD User text file to import or [Ctrl] + [Fn] + B (B for Break).")
    
    Do
      fNameAndPath = Application.GetOpenFilename(fileFilter:="Text Files (*.txt),*.txt", _
                     Title:="Select AD User text File To Be Opened")
    
    If fNameAndPath = "False" Then
        Exit Sub


    End If
    
    If InStr(fNameAndPath, "AD_User") = 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_User") <> 0
    '
    '-----------------------------------------------------------
    '
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fNameAndPath, Destination:=Range("$A$1"))
        .Name = FileName
        .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 = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .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)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery = False
    End With
    '
    With Thiswksht
        LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
        LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    End With
    '
    Cells(1, LastCol).Offset(0, 1).Value = Import_FileName
    


BeforeExit:
'Application.ScreenUpdating = True
'Exit Sub
    Range("A1").Select
End Sub

 

[daverunt]

Hi,

I think you have placed the code in the wrong position and the variable is empty.
Static? I would Dim it as a String

Try placing Import_FileName = fNameAndPath after ApplicationGetOpenFileName

Use ImportFileName= Dir(fNameAndPath) if you just want the name of the file

 

[Shiseiji]

Thanks! Pointed me in the right direction.

Cells(1, LastCol).Offset(0, 1).Value = fNameAndPath

I've been going around in circles trying to figure out how to capture the imported file's create date to put it in the worksheet name. As I create the query that creates the text file, putting the date and time in the file name is easy, getting the last saved date time from the metadata from an Excel file not so much. It's a workaround, but it will work.

Thanks again,

Ron

Ron

 

[daverunt]

Untried:
https://www.techonthenet.com/excel/formulas/filedatetime.php

 

[Shiseiji]

One of the most sincint explanations I've read! And with straight to the point examples. Thanks much!

 

[daverunt]

It's a good site for just that. Glad to help.