+ Reply to Thread
Results 1 to 4 of 4

Getting Excel (via VBA Script) to import multiple textpad files in the next empty cell.

  1. #1
    Registered User
    Join Date
    02-02-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Getting Excel (via VBA Script) to import multiple textpad files in the next empty cell.

    Hi All,

    I recorded a macro as i wanted the line breaks to be in specific locations. Only trouble is when i recorded the macro, the default location to put the data was cell A2.

    I actually need Excel to put sequential textpad imports into the next empty row. (Ie, if there is data in column 355, start the next import in column 356).

    Current Code as follows:

    Sub Import_TXT()
    '
    ' Import_TXT Macro
    ' Import Files created by ODM Shortrec
    '
    ' Keyboard Shortcut: Ctrl+Shift+F
    '
    Dim sfilename As String
    sfilename = Application.GetOpenFilename
    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & sfilename, Destination:=Range("A2"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlFixedWidth
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .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)
    .TextFileFixedColumnWidths = Array(10, 14, 7, 35, 30, 4, 36, 8, 26, 11, 7, 7, 7, 13, 10, 3 _
    , 4, 4, 19, 25, 13, 17, 16, 2, 348, 20, 30, 80, 2, 2, 34, 40, 15, 17, 5, 63)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With
    End Sub


    How can i change this?

    Thanks so much,

    red_dot
    Last edited by red_dot; 02-02-2013 at 03:25 AM. Reason: more detail added.

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Getting Excel (via VBA Script) to import multiple textpad files in the next empty cell

    You could try change

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    Alf

  3. #3
    Registered User
    Join Date
    02-02-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Getting Excel (via VBA Script) to import multiple textpad files in the next empty cell

    BRILLIANT. Thanks, this worked exactly how i wanted it to.

    Thanks again.

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Getting Excel (via VBA Script) to import multiple textpad files in the next empty cell

    Glad to be of help and thanks for feed back.

    Alf

    Ps Next time you post don't forget to wrap your code as this upsets the Moderators.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1