Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Flat File Import

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Bob
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I need to import data in a flat file on a daily basis, each file is an addition to the previous one. I have recorded a
    macro that works fine for the first import, but as the destination is a named cell it only works once. How can I change
    the macro so it writes the data to the active cell rather than a named one?.
    regards
    Bob

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If I assume your main worksheet starts at row 1 and has at data in every row (at least one column with data), you can find out where the last row is by using:

    Range("A1").select
    iRow = Selection.CurrentRegion.Rows.Count + 1

    The value in the variable iRow will be the number of rows in your worksheet + 1 for the next blank row.

    Use this information in the start range of your import function (not sure exactly how you have this set up, so I cannot give an exact answer). If the function has a range in it for the start (like Range("A1:A1, you could simply use:

    Range(Cells(irow,1),Cells(iRow,1))

    where the 1 is whatever column was in your range function.

    Good luck and sorry about the sloppy answer. I'm headed to the golf course in 5 minutes and didn't have time for much more.

    K

  3. #3
    New Member
    Join Date
    May 2002
    Location
    Bob
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for the reply,
    I have tried various combinations of your formula but still cannot get it to work.

    This is the macro:

    Sub importc()

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:ordersc.dat", _
    Destination:=Range("A408"))
    .Name = "orders_360"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlWindows
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 3, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, _
    1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .Refresh BackgroundQuery:=False
    End With
    End Sub

    The problem is with the line:

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:ordersc.dat", _
    Destination:=Range("A408"))
    I need to replace ("A408")) with the active cell.

    I have managed to get over this with this:

    pos = InputBox("ENTER CELL")
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:ordersg.dat", _
    Destination:=Range(pos))

    but this is a bit cumbersome, I am sure there must be a better way.

    regards
    Bob

  4. #4
    New Member
    Join Date
    May 2002
    Location
    Bob
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    kkknie.

    I have managed to get over the problem.
    Simple really with your help.

    for anyone who is interested this done the job:

    irow = Selection.CurrentRegion.Rows.Count
    Dim nrow
    nrow = "A" & irow
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:ordersc.dat", _
    Destination:=Range(nrow))

    This now writes the data to the active row.

    regards
    Bob

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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