VBA: copy and paste from a csv to xlsm

[Nelson78]

Hello everybody.

I'm dealing with a closed csv file called "record".
It I open it manually, it seems a pure xls file (there is one only sheet with a series of data organized in rows and columns).

I need to copy the data to thisworkbook (which obviously is a .xlsm).

But something gets wrong.

Sub copy_csv

Dim wb As Workbook
Set wb = Workbooks.Open("C:\Desktop\CSV\record.csv")

ThisWorkbook.Sheets(1).Range("B1").Value = wb.Range("A1").CurrentRegion.Value

End Sub

Error 438 object doesn't support this property or method

in the following line

ThisWorkbook.Sheets(1).Range("B1").Value = wb.Range("A1").CurrentRegion.Value

The process stops with the "record" file opened with the data displayed without columns (as in a notepad).


How can I figure it out?

 

[Nelson78]

You were going to supply a sanitised version of the file.

I cannot manipulate the file unless I bump into something referring to this

https://en.wikipedia.org/wiki/SYmbolic_LinK_(SYLK)

 

[Fluff]

Try recording a macro by importing the file, on the data tab > From Text, and use Semi-colon as the delimiter.

 

[Nelson78]

Try recording a macro by importing the file, on the data tab > From Text, and use Semi-colon as the delimiter.

This way?

Sub import()
'
' import Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Desktop\CSV\record.csv", Destination:=Range("$A$1") _
        )
        .Name = "record"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 65001
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .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)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[Fluff]

Did it open correctly?

 

[Nelson78]

Did it open correctly?

Yes.

 

[Nelson78]

Did it open correctly?

Ok, figured it out following the macro recorder model.

Thank's a lot.

 

[Fluff]

Glad you sorted it & thanks for the feedback