Forum Discussion
VBA Data Import
Looking for VBA code to parse a list. The list is an extract of a music playlist which retains the Line 1 "name of song", skips the second line which is a repeat of Line 1. Line 3: Retains Artist na...
How about this:
Sub ParseAmazonPlaylist()
Dim wsSource As Worksheet
Dim wsOutput As Worksheet
Dim i As Long, outRow As Long
Dim songName As String, artistName As String, albumName As String
' Set source and output worksheets
Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Change to your source sheet name
Set wsOutput = ThisWorkbook.Sheets.Add
wsOutput.Name = "ParsedPlaylist"
' Set headers
wsOutput.Cells(1, 1).Value = "Song"
wsOutput.Cells(1, 2).Value = "Artist"
wsOutput.Cells(1, 3).Value = "Album"
outRow = 2
' Start parsing from row 6 (assuming header + 5 lines per song)
i = 6
Do While wsSource.Cells(i, 1).Value <> ""
songName = wsSource.Cells(i, 1).Value
artistName = wsSource.Cells(i + 2, 1).Value
albumName = wsSource.Cells(i + 3, 1).Value
' Write to output sheet
wsOutput.Cells(outRow, 1).Value = songName
wsOutput.Cells(outRow, 2).Value = artistName
wsOutput.Cells(outRow, 3).Value = albumName
' Move to next song block
i = i + 5
outRow = outRow + 1
Loop
MsgBox "Playlist parsed successfully!", vbInformation
End Sub
Really appreciate the Help!
Works well enough where I can now import into a database to keep track. Not sure why it skewed the columns though. In Column A under "Song" placed a decimal value. Then brought in the Song in next column, and Artist in next. However, it gives me enough to work with to keep track of the Playlist. Thank you.