Results 1 to 4 of 4
Thread: Importing to Excel (9.0)

20041219, 04:41 #1
 Join Date
 Dec 2004
 Posts
 39
 Thanks
 0
 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?

20041219, 09:11 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 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?
Steve

20041220, 03:20 #3
 Join Date
 Dec 2004
 Posts
 39
 Thanks
 0
 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", _
Destination:=rgLast)
.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.

20041220, 07:10 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 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
with
Dim rgLast As Range
Set rgLast = Range("A65536").End(xlUp).Offset(1, 0)
Explanation:
Range("A65536") is the bottom cell in column A.
Range("A65536").End(xlUp) is the last nonblank 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.