Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Apr 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automate import text files (Excel v2002)

    Hi,
    Is there a way to automate the import of 3 text files into a workbook and have each text file show on a specific sheet? I would like to either select all 3 files using a user form and then do the import or select the file during the macro execution.

    Thank you,

    Matt J.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate import text files (Excel v2002)

    Hi Matt,

    Will the text files have the same structure each time, or can they be different? If they will have the same structure, you can use the macro recorder to record the instruction needed to import each of the files.
    You can use Application.GetOpenFilename to prompt the user for a file name or file names (there is a MultiSelect option). It doesn't do anything with the files, it is up to you as programmer to use the file name(s) provided.

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate import text files (Excel v2002)

    Hans,
    thanks for the reply. The files are basically the same format except for length of the text file. I have recorded a macro:

    Sub SelectFileToOpen()
    Dim fName As Variant
    fName = _
    Application.GetOpenFilename("(*.*), *.*", _
    , "Pick one:")
    If fName = False Then
    MsgBox "You cancelled"
    Else
    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;fName" _
    , Destination:=Range("A1"))
    .Name = "fName_1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlOverwriteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = True
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = True
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With
    End If
    End Sub

    When the macro gets to the line .Refresh BackgroundQuery:=False, I receive a run-time error saying that Excel cannot find the text file to refresh the external data range. - and then End or Debug


    Thanks.....Matt

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate import text files (Excel v2002)

    Sorry, was offline for a while.

    You have included fName in a quoted string, therefore it is interpreted as literal text instead of as a variable. Replace "TEXT;fName" by "TEXT;" & fName:

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, Destination:=Range("A1"))

  5. #5
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate import text files (Excel v2002)

    When I try this sort of thing I get 'Check the file hasn't been moved' message when the macro gets to
    .Refresh BackgroundQuery:=False

    If I add in a debug.print line I can see that the fName variable is holding the filename I selected so that suggests to me that the problem is with
    .Add(Connection:= "TEXT;fName", Destination:=Range("A1"))

    any ideas?

    stuck

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automate import text files (Excel v2002)

    Did you see Hans correction to that line in <post#=363611>post 363611</post#>

    Does that fix it?
    Steve

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate import text files (Excel v2002)

    <img src=/S/drop.gif border=0 alt=drop width=23 height=23> <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Oops, how did I miss that one? Yes, that's the fix.

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> stuck

  8. #8
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automate import text files (Excel v2002)

    Hans,

    I would like to use this VB coding but cannot find the section you are referring to in your previous post.

    Justin.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automate import text files (Excel v2002)

    What can't you find?

Posting Permissions

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