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

--
Thanks!

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


Gord Dibben MS Excel MVP

On Tue, 23 Dec 2008 16:44:12 -0500, "Saucer Man" <(E-Mail Removed)>
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_EXPORT.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:\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
>
> --
> 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 <49515bad$0$1546$(E-Mail Removed)>,
"Saucer Man" <(E-Mail Removed)> 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",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" <(E-Mail Removed)> wrote in message
news:49515bad$0$1546$(E-Mail Removed)...
>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
>
> --
> Thanks!
>