I have a macro which automatically imports the contents of a .csv file.
However, it imports only into the row which is hardcoded. How do I get the
import to start in Column A of the next empty row? The import should start
in Column A of the next empty row and fill in from there. Here is my
code...

With
ActiveSheet.QueryTables.Add(Connection:="TEXT;c:\U PS_CSV_EXPORT.csv",Destination:=Range("A8"))
.Name = "UPS_CSV_EXPORT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

--
Thanks!

Set rng1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
With
ActiveSheet.QueryTables.Add(Connection:="TEXT;c:\U PS_CSV_EXPORT.csv",Destination:=rng1)


Gord Dibben MS Excel MVP

On Tue, 23 Dec 2008 16:44:12 -0500, "Saucer Man"
wrote:

I have a macro which automatically imports the contents of a .csv file.
However, it imports only into the row which is hardcoded. How do I get the
import to start in Column A of the next empty row? The import should start
in Column A of the next empty row and fill in from there. Here is my
code...

With
ActiveSheet.QueryTables.Add(Connection:="TEXT;c:\ UPS_CSV_EXPORT.csv",Destination:=Range("A8"))
.Name = "UPS_CSV_EXPORT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Dim DestCell as range

with activesheet
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
with .QueryTables.Add(Connection:="TEXT;c:\UPS_CSV_EXPO RT.csv", _
Destination:=destcell)
.name = .....
...
end with
end with


Saucer Man wrote:

I have a macro which automatically imports the contents of a .csv file.
However, it imports only into the row which is hardcoded. How do I get the
import to start in Column A of the next empty row? The import should start
in Column A of the next empty row and fill in from there. Here is my
code...

With
ActiveSheet.QueryTables.Add(Connection:="TEXT;c:\U PS_CSV_EXPORT.csv",Destination:=Range("A8"))
.Name = "UPS_CSV_EXPORT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

--
Thanks!

--

Dave Peterson

One way:

Dim rDest As Range
Set rDest = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

then in your QueryTables.Add call, use

Destination:=rDest


In article ,
"Saucer Man" wrote:

I have a macro which automatically imports the contents of a .csv file.
However, it imports only into the row which is hardcoded. How do I get the
import to start in Column A of the next empty row? The import should start
in Column A of the next empty row and fill in from there. Here is my
code...

With
ActiveSheet.QueryTables.Add(Connection:="TEXT;c:\U PS_CSV_EXPORT.csv",Destinati
on:=Range("A8"))
.Name = "UPS_CSV_EXPORT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Thanks for the replies. It works!

"Saucer Man" wrote in message
...
I have a macro which automatically imports the contents of a .csv file.
However, it imports only into the row which is hardcoded. How do I get the
import to start in Column A of the next empty row? The import should start
in Column A of the next empty row and fill in from there. Here is my
code...

With
ActiveSheet.QueryTables.Add(Connection:="TEXT;c:\U PS_CSV_EXPORT.csv",Destination:=Range("A8"))
.Name = "UPS_CSV_EXPORT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

--
Thanks!