(no subject)
Jul. 16th, 2004 01:39 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
saint_monkeythis is a macro to pull all the "lj:music" tags from the xml feed of livejournal's latest posts. requires an active internet connection. i'm working on a much more useless and far more complex thing using this feed, but i won't get into that now...
instructions:
open an Excel workbook
go to the tools menu, then Macro then Macros
type "musica" in the Macro Name box, hit the "Create" Button
highlight the text that is there, and hit delete, then cut-n-paste in the code below. (watch out for text wrap.)
"X" out ofthe Visual Basic Editor.
go back to your workbook, choose Tools>macro>macros, pick "Musica" from the list, and hit Run.
'begin code NOW
Sub musica()
Sheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.livejournal.com/stats/latest-rss.bml", Destination:=Range( _
"A1"))
.PostText = "Livejournal Latest XML"
.Name = False
.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.HasAutoFormat = True
.RefreshOnFileOpen = 0
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.SaveData = True
.Refresh BackgroundQuery:=False
End With
'filter out everything but the posts themselves
Range("A1").Select
lr = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
Range("A1:A" & lr).Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=<lj:music>*", Operator:=xlAnd _
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Copy
Sheets.Add
Range("A1").PasteSpecial
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=">"
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="<"
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
End Sub
'end of code HERE
sample output, the music choices of countless livejournal people type things:
all i hear is the air conditioner
Strawberry Fields Forever -The Beatles
die NYU die
Pankow - Me & My Ding Dong
girls just want to have fun :: cyndi lauper
bela fleck ~ burned by sam/jeremy!
Offspring - Keep 'Em Separated
office sounds
Demilich
Newsboys-Shine
Orgy \\ Faces
dave matthews and tim reynolds- i don't know what song
nothing
still the mix.
Kill Bill theme song
Tsuki no Uta -Gackt
Dignity - Anti-heros
Franz Ferdinand - Take Me Out
American Soilder ~ Toby Kieth
Rupert Holmes
tegan and sara, if it was you
Voices in my head.
access - only the love survive
NFG
chinese freestyle
Disillusion - Back To Times Of Splendor
Just the tickity tick of my keys as my fingers fly
"My Happy Ending" by Avril Lavigne
beastie boys- alive
none
Phantom Planet - California
Hanson - mmmbop!
Eminem_Benzino Diss//Nail In The Coffin
DJ Slow - I'm over you, Pablo.
nothing again... must install winamp
Memory_sugarcult
Cadillac's on 22's
how you do talk to an angel~ the heights
niente
a good run of bad luck - clint black
WOW.
"Drop In The Ocean"~Michelle Branch
hazard - richard marx
lost without each other by hanson
tropeizo-cd
Gregory Abbot - Shake You Down
Gary Jules' cover of "Mad World"
The rattling of my fan in my liveing room.. SO ANNOYING!
How Come - Eminem and D12
mystery band
united "take all of me"
Honesty, SevenDust
Young Buck: Its burning hott we on fire
Sold-John Micheal Motgomery(a redneck country song)
Blondie- Heart of Glass
Hoobastank
yellowcard - ocean avenue
"Jimmy Kimmel Live"
Family MAtter
AS-Better Off
the ataris
EDIT: to make a mood breakdown, perdagoski's comment:
easy enough, alter the code
the following line,
Selection.AutoFilter Field:=1, Criteria1:="=<lj:music>*", Operator:=xlAnd _
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Copy
becomes
Selection.AutoFilter Field:=1, Criteria1:="=<lj:mood id*", Operator:=xlAnd _
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Copy
and then it's wired for moods rather than music.
the general mood of livejournal right now (4:40 PM, Jul 16th) is:
instructions:
open an Excel workbook
go to the tools menu, then Macro then Macros
type "musica" in the Macro Name box, hit the "Create" Button
highlight the text that is there, and hit delete, then cut-n-paste in the code below. (watch out for text wrap.)
"X" out ofthe Visual Basic Editor.
go back to your workbook, choose Tools>macro>macros, pick "Musica" from the list, and hit Run.
'begin code NOW
Sub musica()
Sheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.livejournal.com/stats/latest-rss.bml", Destination:=Range( _
"A1"))
.PostText = "Livejournal Latest XML"
.Name = False
.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.HasAutoFormat = True
.RefreshOnFileOpen = 0
.BackgroundQuery = False
.TablesOnlyFromHTML = True
.SaveData = True
.Refresh BackgroundQuery:=False
End With
'filter out everything but the posts themselves
Range("A1").Select
lr = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
Range("A1:A" & lr).Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=<lj:music>*", Operator:=xlAnd _
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Copy
Sheets.Add
Range("A1").PasteSpecial
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=">"
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="<"
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").EntireColumn.AutoFit
End Sub
'end of code HERE
sample output, the music choices of countless livejournal people type things:
all i hear is the air conditioner
Strawberry Fields Forever -The Beatles
die NYU die
Pankow - Me & My Ding Dong
girls just want to have fun :: cyndi lauper
bela fleck ~ burned by sam/jeremy!
Offspring - Keep 'Em Separated
office sounds
Demilich
Newsboys-Shine
Orgy \\ Faces
dave matthews and tim reynolds- i don't know what song
nothing
still the mix.
Kill Bill theme song
Tsuki no Uta -Gackt
Dignity - Anti-heros
Franz Ferdinand - Take Me Out
American Soilder ~ Toby Kieth
Rupert Holmes
tegan and sara, if it was you
Voices in my head.
access - only the love survive
NFG
chinese freestyle
Disillusion - Back To Times Of Splendor
Just the tickity tick of my keys as my fingers fly
"My Happy Ending" by Avril Lavigne
beastie boys- alive
none
Phantom Planet - California
Hanson - mmmbop!
Eminem_Benzino Diss//Nail In The Coffin
DJ Slow - I'm over you, Pablo.
nothing again... must install winamp
Memory_sugarcult
Cadillac's on 22's
how you do talk to an angel~ the heights
niente
a good run of bad luck - clint black
WOW.
"Drop In The Ocean"~Michelle Branch
hazard - richard marx
lost without each other by hanson
tropeizo-cd
Gregory Abbot - Shake You Down
Gary Jules' cover of "Mad World"
The rattling of my fan in my liveing room.. SO ANNOYING!
How Come - Eminem and D12
mystery band
united "take all of me"
Honesty, SevenDust
Young Buck: Its burning hott we on fire
Sold-John Micheal Motgomery(a redneck country song)
Blondie- Heart of Glass
Hoobastank
yellowcard - ocean avenue
"Jimmy Kimmel Live"
Family MAtter
AS-Better Off
the ataris
EDIT: to make a mood breakdown, per
dagoski's comment:easy enough, alter the code
the following line,
Selection.AutoFilter Field:=1, Criteria1:="=<lj:music>*", Operator:=xlAnd _
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Copy
becomes
Selection.AutoFilter Field:=1, Criteria1:="=<lj:mood id*", Operator:=xlAnd _
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Copy
and then it's wired for moods rather than music.
the general mood of livejournal right now (4:40 PM, Jul 16th) is:
mood |
count |
percent |
bored |
8 |
9% |
amused |
6 |
7% |
accomplished |
4 |
4% |
bouncy |
4 |
4% |
tired |
4 |
4% |
angry |
3 |
3% |
annoyed |
3 |
3% |
confused |
3 |
3% |
crazy |
3 |
3% |
drained |
3 |
3% |
Happy |
3 |
3% |
blank |
2 |
2% |
cheerful |
2 |
2% |
chipper |
2 |
2% |
cranky |
2 |
2% |
excited |
2 |
2% |
okay |
2 |
2% |
pleased |
2 |
2% |
... |
1 |
1% |
anxious |
1 |
1% |
awake |
1 |
1% |
blah |
1 |
1% |
bored.grrr.grr. |
1 |
1% |
calm |
1 |
1% |
complacent |
1 |
1% |
contemplative |
1 |
1% |
content |
1 |
1% |
curious |
1 |
1% |
dead |
1 |
1% |
devious |
1 |
1% |
disappointed |
1 |
1% |
discontent |
1 |
1% |
ecstatic |
1 |
1% |
eeeeeeeehkm |
1 |
1% |
exanimate |
1 |
1% |
exhausted |
1 |
1% |
geeky |
1 |
1% |
good |
1 |
1% |
grumble |
1 |
1% |
hot |
1 |
1% |
hungry |
1 |
1% |
indescribable |
1 |
1% |
infuriated |
1 |
1% |
irate |
1 |
1% |
morose |
1 |
1% |
nostalgic |
1 |
1% |
satisfied |
1 |
1% |
sleepy |
1 |
1% |
worried |
1 |
1% |
