VBA - CSV import to Excel

[saint_2008]

I've taken a code I've found by a google search and tried to implement it for what I need but I'm getting an error Subscript out of range error can anyone shed some light on why i might be getting this error?

Sub ImportSamplesReceivedFile()


Dim FPath As Variant

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

ChDrive "I"
ChDir "I:\"
FPath = Application.GetOpenFilename(FileFilter:="CSV Excel Files, *.CSV", Title:="Select Today's Export File")
If FPath = False Then
MsgBox UCase(Environ("UserName")) & " did not select a reference file." & Chr(13) & "Database Update has been aborted!", vbCritical, "Error Handler"
Exit Sub
Else
'Continue Procedure
End If

Sheets("VMDump").Activate

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FPath, Destination:=Range("$A$2"))
.CommandType = 0
.Name = "VMDump"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub

If you need any further information please let me known.

I need the data to skip top (header) row on both sheet.

There random amount of data the source sheet can be anything from 10-100 rows.

 

[Trevor G]

Have you stepped through the code to see where it highlights the issue. In your code use F8 and it will step through and highlight the issue and then you can post back and provide more information.

 

[saint_2008]

The Run time error "9" starts on this part, of the code

Sheets("VMDump").Activate

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FPath, Destination:=Range("$A$2"))

 

[Trevor G]

Can I make a suggestion record a macro to open a CSV file to give you the basic code then add that into your thread and hopefully we can spot what's actually happening.

 

[saint_2008]

Thank bud, got it to work doing it that way. Thank for your enlightenment and help

 

[Trevor G]

Pleased to read you have a working solution well done, and thanks for letting me know.