csv import

[lembi2001]

Hi all

First post here and I apologise if this has been answered elsewhere. Did look but couldn't find exactly what i was looking for.

Right, I have got a csv file called export. This is created in a third party application. It will always reside at the same location (C:\FSL\data\export.csv). I need to create a workbook that upon opening imports the data contained in the csv file and adds it onto the end of any existing data in a specific sheet.

My workbook contains an All Records sheet and this is where i want to import the data to.

I have tried recording a macro to import the data and set this to run upon opening the workbook however i can't work out how to tell it to add the data onto the end of the worksheet.

Please be nice to me and explain it in the simplest way possible. I have very little understanding of the syntax used in VBA and thought that you nice people on here would be able to help me out.

Thanks

[Bob Phillips]

Do it manually with the macro recorder on, post that code, and we can help you make it generic.

[lembi2001]

Here is the requested macro recording code:

Sub import()
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\FSL\data\export.csv", _
Destination:=Range("$A$1"))
.Name = "export_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(4, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


Thanks

[Bob Phillips]

Not much change needed



Sub import()
Const FullName As String = "TEXT;C:\FSL\data\export.csv"
Dim Lastrow As Long

With ActiveSheet

If .Range("A1").Value = "" Then
Lastrow = 0
Else
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End If
.QueryTables.Add Connection:=FullName, Destination:=.Range("A1").Offset(Lastrow)
.Name = "export_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(4, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

[lembi2001]

Thanks for this but when i run the macro i get a debug error.

anything after this section:

End If
.QueryTables.Add Connection:=FullName, Destination:=.Range("A1").Offset(Lastrow)
.Name = "export_1"

is highlighted as the cause.

[Bob Phillips]

Can you post a copy of the csv file so that I can test it?

[lembi2001]

Here you are, as requested

7235

Thanks again for this

[Bob Phillips]

See if this works better



Sub import()
Const FullName As String = "TEXT;C:\FSL\data\export.csv"
Dim Lastrow As Long

With ActiveSheet

If .Range("A1").Value = "" Then
Lastrow = 0
Else
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End If
With .QueryTables.Add(Connection:=FullName, Destination:=.Range("A1").Offset(Lastrow))

.Name = "export_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(4, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End With
End Sub

[lembi2001]

PERFECT - Thank you very much!!!