Importing Data Error #NAME?

[purceld2]

Hi

I have imported a CSV into spreadsheet and get a funny error in on of the coulums

It appears the Excel does not like the data being import into this colum

Data

- Help Them Please.mp3

Excel changes the cell

=- Help Them Please.mp3

And the Cell displays #NAME?

Any ideas why this is?

Thanks in advance for your help

Desmond

 

[Joe4]

Hmmm...

It seems to work just fine for me. Can you do the following?
1. Turn on the Macro Recorder
2. Record yourself opening the file with the Import Wizard, like I instructed
3. Stop the Macro Recorder
4. Copy and paste the VBA code it recorded here
5. Let me know what column these values are showing up in

That will show he exactly what you are doing, and I can check to see if all the settings look correct
(note that when you get to Step 3, it will be on the FIRST field, by default; you need to select the field that has this value, and then change THAT field to text).

 

[purceld2]

Hi Joe4,

As requested I have recorded my actions see below

Sub DateInput()
'
' DateInput Macro
'


'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;I:\BT Sync Folder\BT Cloud\New Music Library\New Music Library Tags.csv" _
        , Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "New Music Library Tags"
        .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 = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
End Sub

 

[Joe4]

You missed my last question.
Which field (column) contains this value that is causing problems?
I need to know that in order to verify your code.

 

[purceld2]

Hi Joe4,

The fields that are throwing up the errors are J2-J10 in image in previous post.

see image below

 

[Joe4]

You aren't selecting the field. You are updating the first one, as can be seen here in this line:

        .TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

The values in the arrays represents the selections that you made for each columns (first is column A, second is column B, etc).
Since "J" is the tenth column, the tenth number in that array is what you want to look at. It is set to 1 (the first option in the Data Type list - General).
As you can see, the first one (column "A") is set to 2 (which is Text). So you are changing the Data Type for column A, not column J.

In looking at the screen print you posted, you scroll over to column J, but never selected it. Click on the title "General" above that column. The whole column should then be darkened/highlighted. That is how you select that column. Then change the data type to "Text".

If done right, the line of VBA code above would look like:

        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1)

 

[purceld2]

Hi Joe4,

You are absolutely correct I was not selecting the field. Working Fine now.

Thank you for sticking this out with me, I really appreciated it.

Thanks again

Desmond

 

[Joe4]

You are welcome!
Glad we got it all sorted out.