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

slack7639

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

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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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
Back
Top