Consulting

Results 1 to 5 of 5

Thread: Solved: Importing a .txt file without a user prompt

  1. #1

    Solved: Importing a .txt file without a user prompt

    I'm trying to set up a script that will import a .txt document without user intervention. I've gotten it to find the exact file that it should be

    C:\Documents and Settings\*****\Desktop\SRE Script\FDR\7.2.2012 11.15.18 AM FDR

    When I run the script FinfsFile = C:\Documents and Settings\*****\Desktop\SRE Script\FDR7.2.2012 11.15.18 AM FDR.txt

    But when I try to refresh the file to complete the import, the it tells me that the .txt file cannot be found.

    Here's what I have.
    [VBA]
    Dim datetest As String
    Dim FinDateTest As String
    Dim fs As String
    Dim fsFile As String
    Dim i As Long
    Dim LfsFile As String
    Dim FinfsFile As String

    Workbooks("Automated SRE Dashboard").Activate

    x = 2

    'finds date needed in workbook

    While Range("C" & x) <> ""
    x = x + 1
    Wend

    If Range("C" & x - 1) = "Date Sent" Then
    datetest = Range("A4")
    Else
    datetest = Range("C" & x)
    End If

    FinDateTest = StripIllegalChar(datetest)


    Set newbook = Workbooks.Add
    With newbook
    .Title = "xferWB"
    .Subject = "xferWB"
    .SaveAs Filename:="xferWB.xls"
    End With
    Workbooks("xferWB").Activate

    'searches files for correct date

    fs = "C:\Documents and Settings\*****\Desktop\SRE Script\FDR"

    If Dir(fs, vbDirectory) <> "" Then

    fsFile = Dir(fs & "\*.txt")
    Do While fsFile <> ""

    LfsFile = Left(fsFile, 8)

    If LfsFile = FinDateTest Then

    FinfsFile = Trim(fs & fsFile)
    Range("a1") = FinfsFile

    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & FinfsFile _
    , Destination:=Range("$A$1"))
    .Name = LfsFile
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 1252
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With
    End If
    fsFile = Dir
    Loop
    End If[/VBA]

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Why would you need a refresh?

    See if my QueryTable routine helps any. http://www.vbaexpress.com/forum/showthread.php?t=42676

  3. #3
    The querytable that I'm trying to run looks pretty much the same as yours. Any idea what could be wrong?

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    If you use debug.print, debug by F8 and such, you can see that the problem is here:
    [VBA] FinfsFile = Trim(fs & fsFile) [/VBA]

    which should be:
    [VBA]FinfsFile =fs & "\" & fsFile[/VBA] though I usually just add the trailing backslash to fs to begin with. There is an API routine that I could show you that would add it if needed though it is easily coded for otherwise.

  5. #5
    you sir are the man! IT WORKED! I poured over this all day yesterday trying to get it to load.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •