Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Thanked 0 Times in 0 Posts

    Importing to Excel (9.0)

    Ok, Hans, I really need your help to get my head straight.

    I am exporting data from a business application to a text file using Windows Scheduler. Then I am importing the file to excel so I can run some metrics. I can get a macro to run to import the file, even automatically, but here is the tricky part. This file is based on a daily summary, so it needs to be identified as Monday, Teuesday, Wednesday, etc in excel for ease. The only import options allow an "Insert" or "Overwrite" set up. I cannot append. If I can apend, I know that Monday will always be the first set of data. Right now on Tuesday, Monday's data moves to the right, so the final order winds up, Friday, Thurs, Wed, etc- backwards! Very tedious and confusing, since the import only gives me a date. I want the next import I run to populate the next available empty cell- to append! I tried to write some VBA to populate the next empty column and came close, but it only works if I click on the cell A1 and it holds the total amount of cells counted with the last updated in memory, so it isnt consistent in its placement.

    What is the easiest and correct way (or an example of code if you got it) to locate the next available cell, either horizintally or vertically, in a spreadsheet when importing a text file? I abandoned the code I wrote, since it was pretty much useless at this point.

    Any help?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Importing to Excel (9.0)

    I am not Hans, but if I understand your question: to find the next column after the data, you could use something like:
    <pre>dim iNextCol as integer
    iNextCol = range("a1").SpecialCells(xlCellTypeLastCell).colum n + 1</pre>

    To get the "next row":
    <pre>Dim lNextRow as long
    lNextRowl = range("a1").SpecialCells(xlCellTypeLastCell).row + 1</pre>

    SpecialCells - last cell is the last used cell in spreadsheet (equiv to the cell you get with <end><home>)

    Is this what you are after?


  3. #3
    Join Date
    Dec 2004
    Thanked 0 Times in 0 Posts

    Re: Importing to Excel (9.0)

    Thanks Steve, I think you got it, but I just dont understand it well enough. Here is what I have, similar to your example, but it doesnt act consistently and I am having trouble getting yours to accept. Lets just say I want to find the first empty column, beginning the search from A1 for now::

    Sub getData()
    Set rgLast = Range("A1").SpecialCells(xlCellTypeLastCell)
    lLastRow = rgLast.Row
    lLastCol = rgLast.Column

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;Cocuments and SettingsOwnerMy DocumentsDays of the week.txt", _
    .Name = "Days of the week"
    .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)
    .Refresh BackgroundQuery:=False
    End With
    End Sub

    One, I can only get it to drop down in rows. I tried removing just the row vba, but even that didnt work. What is the significanceof the +1 you included and what is different about what I have?

    Two, I realize that if I delete the old data and start again, it remembers the deleted data and starts the next import at the old empty space- unless I save before importing again, so I can fix that.

    Any further help is greatly appreciated.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Importing to Excel (9.0)

    If column A will always be filled, you can replace

    Set rgLast = Range("A1").SpecialCells(xlCellTypeLastCell)
    lLastRow = rgLast.Row
    lLastCol = rgLast.Column


    Dim rgLast As Range
    Set rgLast = Range("A65536").End(xlUp).Offset(1, 0)


    Range("A65536") is the bottom cell in column A.
    Range("A65536").End(xlUp) is the last non-blank cell in column A.
    Range("A65536").End(xlUp).Offset(1, 0) is the cell below that, i.e. the first available blank cell in column A.

Posting Permissions

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