Here's the VBA I used on the SQLite3 CLI, to get my bookmarks out of Firefox
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
' Timer Function - See comments in "Sub SQLite3_VBA_code()"
Function waitTime(ByVal milliSeconds As Double)
Application.Wait (Now() + milliSeconds / 24 / 60 / 60 / 1000)
End Function
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Sub SQLite3_VBA_Code(control As IRibbonControl)
' Use Excel 2010's VBA to run SQLite3 to get the Bookmarks, which is one less click if combined in with the code to process them, but,
' If you want to Debug the processing code, below . . .
' . . . "waitTime" makes F8 hang. . . and . . . "Call SendKeys" makes F8 hang
' . . . . . . Debug might also include modifying the code for new things
' . . . so, these commands stop you from using Debug . . . therefore, it makes sense to keep these two functions separate
' Give the SQLite3 one second to open, otherwise, VBA is too quick, and erroneously writes the Commands to the Worksheet
' Use the Timer Function and waitTime - you can't nest a Function inside a procedure. It needs to be above.
' Application.Wait (used if these two processes are combined) doesn't work on the Shell: Application.Wait (Now + TimeValue("00:00:01"))
' ShellandWait would be better to use, but is more complicated, and I can't figure out how to get it to work.
Dim SQLITE3_BOOKMARKS As String
SQLITE3_BOOKMARKS = "C:\Program Files (x86)\SQLite3\sqlite-tools-win32-x86-3210000\sqlite3.exe"
Call Shell(SQLITE3_BOOKMARKS, vbNormalFocus)
waitTime (1000)
' output.csv could go to a folder like:
' C:\1\01\1 backup\a Bookmarks BAT files\temp
' but you might forget to delete it, so, it's good to send it to the Desktop
' Commands to get the Bookmarks. In SQLite3, you have to use forward slashes in the paths. Curly brackets are needed around ENTER.
' You have to have "ENTER" after each command
' Application.SendKeys Method (Excel) . . .
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-sendkeys-method-excel
' True to have Microsoft Excel wait for the keys to be processed before returning control to the macro.
' False (or omitted) to continue running the macro without waiting for the keys to be processed.
Call SendKeys(".echo off", True)
Call SendKeys("{ENTER}", True)
Call SendKeys(".open C:/Users/MyUserName/AppData/Roaming/Mozilla/Firefox/Profiles/u55555dh.default/places.sqlite", True)
Call SendKeys("{ENTER}", True)
Call SendKeys(".mode csv", True)
Call SendKeys("{ENTER}", True)
Call SendKeys(".once C:/Users/MyUserName/Desktop/output.csv", True)
Call SendKeys("{ENTER}", True)
' Curly brackets are needed around the inner parentheses
Call SendKeys("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;", True)
Call SendKeys("{ENTER}", True)
Call SendKeys(".quit", True)
Call SendKeys("{ENTER}", True)
End Sub