Flat File Import

[goplacesuk]

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

 

[kkknie]

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

 

[goplacesuk]

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;Drdersc.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;Drdersc.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;Drdersg.dat", _
Destination:=Range(pos))

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

regards
Bob

 

[goplacesuk]

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;Drdersc.dat", _
Destination:=Range(nrow))

This now writes the data to the active row.

regards
Bob