- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
I get my Bookmarks out of Firefox using this Add-on:
SQLite Manager :: Add-ons for Firefox . . . https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/
and this query:
SELECT a.id AS ID, a.title AS Title, b.url AS URL, datetime(a.dateAdded/1000000,"unixepoch","localtime") AS Date
FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id
I save it as a .csv to my Desktop, and then run the following Excel macro on it to process the data . . . I have a couple of questions:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
QUESTION #1
This query gives an error sometimes. It doesn't like how it completes for some reason.
I think I added this to fix it: SendKeys ("{ESC}")
It still bombs out at the end, though, sometimes.
It looks like it handles the data fine, though, it's just something about how it's not completing properly.
Can you tell me if there's anything wrong at the end?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
QUESTION #2
There are a lot of these underscores, where I check for garbage characters . . . _
Is it wrapped like this for a reason?
Or could I get everything on one row, separating the lines by a space?
I think it would then be easier for me to see, and understand, what each row is doing.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Sub Process_Bookmarks_SQLite()
' Process_Bookmarks_SQLite Macro
Workbooks.Open Filename:="C:\Users\USER2017\Desktop\output.csv"
Windows.Arrange ArrangeStyle:=xlHorizontal
Cells.Select
With Selection.Font
.Name = "Microsoft Sans Serif"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Font
.Name = "Microsoft Sans Serif"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
ActiveWindow.Zoom = 80
Selection.ColumnWidth = 70
Columns("A:A").Select
Selection.ColumnWidth = 7
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("C:D").Select
Selection.ColumnWidth = 7
Columns("D:D").Select
Selection.NumberFormat = "mm/dd/yy;@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.SpecialCells(xlLastCell).Select
Range("A1").Select
' Find and Replace special characters
Cells.Replace What:=", the free encyclopedia", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:=ChrW(&H9D), Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:=ChrW(&HA6) & ChrW(&H81), Replacement:=ChrW(&H2022), LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:=ChrW(&HFEFF), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="…", Replacement:=" . . .", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="ñ", Replacement:=ChrW(&HF1), LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="–", Replacement:=ChrW(&H2013), LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
Cells.Replace What:="—", Replacement:=ChrW(&H2014), LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
Cells.Replace What:="−", Replacement:=ChrW(&H2212), LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
Cells.Replace What:="‘", Replacement:=ChrW(&H2018), LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
Cells.Replace What:="’", Replacement:=ChrW(&H2019), LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
Cells.Replace What:="•", Replacement:=ChrW(&H2022), LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="“", Replacement:=ChrW(&H201C), LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="â€", Replacement:=ChrW(&H201D), LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
Cells.Replace What:="Â", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
' Delete static Bookmarks, above
' 06/16/16 Figuring out how to code better, pretty sure that I don't need this
' Cells.Find(What:="122", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
' xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
' False, SearchFormat:=False).Activate
Rows("71:71").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
' If there's only one Bookmark, it's hard to copy just that. You have to go round-about.
' First, get the Index column out of the way, to make it easy to use Ctrl+Home
' Don't delete the Index column, as you will want to see it later, if the static Bookmarks change
Columns("A:A").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
' Now, go far down into Column C, farther than you would ever have any Bookmarks, and then up to the last Bookmark
Range("C5000").Select
Selection.End(xlUp).Select
' Now, easily select all of the Bookmarks, even just one
Range(Selection, Cells(ActiveCell.Row, 1)).Select
Range(Selection, Cells(1)).Select
Selection.copy
' Go to the Bookmarks Worksheet
Workbooks("bookmarks.xlsm").Activate
Workbooks("bookmarks.xlsm").Worksheets("Bookmarks").Select
' Go to the bottom right of Bookmarks
Range("Z2").Select
Selection.End(xlDown).Select
' Paste the Bookmarks
ActiveCell.Offset(1, -6).Select
ActiveSheet.Paste
Selection.End(xlDown).Select
' Get the Sort Formulas, Copy and Paste them down
ActiveCell.Offset(0, 4).Select
Selection.End(xlUp).Select
Range(ActiveCell, ActiveCell.Offset(0, 3)).Select
Selection.copy
ActiveCell.Offset(1, 0).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Paste
Selection.End(xlDown).Select
' Maneuver over to the first Bookmark to edit / Esc
ActiveCell.Offset(0, -1).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, -3).Select
SendKeys ("{ESC}")
End Sub
I get my Bookmarks out of Firefox using this Add-on:
SQLite Manager :: Add-ons for Firefox . . . https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/
and this query:
SELECT a.id AS ID, a.title AS Title, b.url AS URL, datetime(a.dateAdded/1000000,"unixepoch","localtime") AS Date
FROM moz_bookmarks AS a JOIN moz_places AS b ON a.fk = b.id
I save it as a .csv to my Desktop, and then run the following Excel macro on it to process the data . . . I have a couple of questions:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
QUESTION #1
This query gives an error sometimes. It doesn't like how it completes for some reason.
I think I added this to fix it: SendKeys ("{ESC}")
It still bombs out at the end, though, sometimes.
It looks like it handles the data fine, though, it's just something about how it's not completing properly.
Can you tell me if there's anything wrong at the end?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
QUESTION #2
There are a lot of these underscores, where I check for garbage characters . . . _
Is it wrapped like this for a reason?
Or could I get everything on one row, separating the lines by a space?
I think it would then be easier for me to see, and understand, what each row is doing.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Sub Process_Bookmarks_SQLite()
' Process_Bookmarks_SQLite Macro
Workbooks.Open Filename:="C:\Users\USER2017\Desktop\output.csv"
Windows.Arrange ArrangeStyle:=xlHorizontal
Cells.Select
With Selection.Font
.Name = "Microsoft Sans Serif"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Font
.Name = "Microsoft Sans Serif"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
ActiveWindow.Zoom = 80
Selection.ColumnWidth = 70
Columns("A:A").Select
Selection.ColumnWidth = 7
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("C:D").Select
Selection.ColumnWidth = 7
Columns("D:D").Select
Selection.NumberFormat = "mm/dd/yy;@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.SpecialCells(xlLastCell).Select
Range("A1").Select
' Find and Replace special characters
Cells.Replace What:=", the free encyclopedia", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:=ChrW(&H9D), Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
Cells.Replace What:="", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:=ChrW(&HA6) & ChrW(&H81), Replacement:=ChrW(&H2022), LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:=ChrW(&HFEFF), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="…", Replacement:=" . . .", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="ñ", Replacement:=ChrW(&HF1), LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="–", Replacement:=ChrW(&H2013), LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
Cells.Replace What:="—", Replacement:=ChrW(&H2014), LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
Cells.Replace What:="−", Replacement:=ChrW(&H2212), LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
Cells.Replace What:="‘", Replacement:=ChrW(&H2018), LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
Cells.Replace What:="’", Replacement:=ChrW(&H2019), LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
Cells.Replace What:="•", Replacement:=ChrW(&H2022), LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="“", Replacement:=ChrW(&H201C), LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="â€", Replacement:=ChrW(&H201D), LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
Cells.Replace What:="Â", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _
False
' Delete static Bookmarks, above
' 06/16/16 Figuring out how to code better, pretty sure that I don't need this
' Cells.Find(What:="122", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
' xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
' False, SearchFormat:=False).Activate
Rows("71:71").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
' If there's only one Bookmark, it's hard to copy just that. You have to go round-about.
' First, get the Index column out of the way, to make it easy to use Ctrl+Home
' Don't delete the Index column, as you will want to see it later, if the static Bookmarks change
Columns("A:A").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
' Now, go far down into Column C, farther than you would ever have any Bookmarks, and then up to the last Bookmark
Range("C5000").Select
Selection.End(xlUp).Select
' Now, easily select all of the Bookmarks, even just one
Range(Selection, Cells(ActiveCell.Row, 1)).Select
Range(Selection, Cells(1)).Select
Selection.copy
' Go to the Bookmarks Worksheet
Workbooks("bookmarks.xlsm").Activate
Workbooks("bookmarks.xlsm").Worksheets("Bookmarks").Select
' Go to the bottom right of Bookmarks
Range("Z2").Select
Selection.End(xlDown).Select
' Paste the Bookmarks
ActiveCell.Offset(1, -6).Select
ActiveSheet.Paste
Selection.End(xlDown).Select
' Get the Sort Formulas, Copy and Paste them down
ActiveCell.Offset(0, 4).Select
Selection.End(xlUp).Select
Range(ActiveCell, ActiveCell.Offset(0, 3)).Select
Selection.copy
ActiveCell.Offset(1, 0).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Paste
Selection.End(xlDown).Select
' Maneuver over to the first Bookmark to edit / Esc
ActiveCell.Offset(0, -1).Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, -3).Select
SendKeys ("{ESC}")
End Sub