MACRO / VBA: Completion error sometimes, Can I "un-wrap" the code?

slack7639

Board Regular
Joined
Apr 19, 2016
Messages
57
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,130,021
Messages
5,639,581
Members
417,100
Latest member
Simon123456789

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top