Как импортировать данные из CSV-файла в XLSM

Я думаю, вы просто неправильно указали свой код

Dim File As Variant
Dim MsgBox_Response As Long
Dim LineFromFile As String
Dim LineItems As Variant
Dim RowCounter As Long

File = Application.GetOpenFilename _
("CSV Files (*.csv), *.csv", , "Select the CSV file", , False)

If File = False Then
    MsgBox_Response = MsgBox("File could not be opened or is not compatible", vbOKOnly)
Else
    Open File For Input As #1

    ' Update this with your sheet reference
    With Sheet1
        Do Until EOF(1)
            Line Input #1, LineFromFile

            LineItems = Split(LineFromFile, ",")
            ' Increment row counter
            RowCounter = RowCounter + 1
            .Range("A" & RowCounter).Resize(, UBound(LineItems) + IIf(LBound(LineItems) = 0, 1, 0)).Value2 = LineItems
            Debug.Print Join(LineItems, vbTab)
        Loop
    End With
    Close #1
End If