making a usable list of ios apps in...

of 13

Making a usable list of iOS apps in Excel Prof. Lee Townsend March 25, 2017

When I have searched for how to make a list of my apps, I found several references telling me how to make a PDF. Of course, I want an Excel listing so I can sort, comment, etc. The discussion below shows you how I get the Excel file using a Mac. Please let me know of any issues. ltownsend at hartford dot edu. ==================== in iTunes ================= I print the following two pages from iTunes using Print/Songs/Custom.

Page 1 Name Size Release Date Purchase Date Date Added Page 2 Name Size Genre Kind Version

You can adjust which columns show by right clicking on one of the titles.

Autofit columns in iTunes the shrink title so all fits. Then refit the title column so the last column fits. See pictures below. I use the relationship of the search symbol at the top to adjust my title column width.

of 13

You can always go back and print just title and size it you want the entire app name. I always include the size as it does not take up much space and it allows me to check that multiple sheets have the same initial data. Now print the list.

of 13

Make sure you print to landscape to none of the text gets truncated. ============== in Lightning PDF Pro (Mac) =========== Open in Lightning PDF Pro - Acrobat makes the last song on each page a footer so I needed to use an alternative app. Convert to Word. OCR happens automatically.

You will need to find the equivalent app for Windows. Maybe Acrobat is better behaved on that platform.

of 13

Do the following for each data sheet.

==================== in Word =================== Open the generated file in Word

Note the lack of tab after the size data. Insert tabs after the size by running the macro below.

Sub MSDN() With ActiveDocument.Content.Find .Execute FindText:=" KB ", ReplaceWith:=" KB^t", _ Format:=True, Replace:=wdReplaceAll .Execute FindText:=" MB ", ReplaceWith:=" MB^t", _ Format:=True, Replace:=wdReplaceAll .Execute FindText:=" GB ", ReplaceWith:=" GB^t", _ Format:=True, Replace:=wdReplaceAll End With End Sub

Also get rid of section breaks.

Sub DeleSectionBreaks() 'https://www.extendoffice.com/documents/word/ ‘ 636-word-remove-section-break.html#a3 Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "^bApps" .Replacement.Text = "" End With Selection.Find.Execute Replace:=wdReplaceAll End Sub

Here is what’s in my VBA editor in Word 2011:

of 13

Results:

Save as a UTF-8 txt file.

of 13

==================== in Excel ================== Import the tab-delimited file into Excel. First create an empty worksheet then import the data.

of 13

Select cell B1 on the empty worksheet. Then import as tab-delimited.

Press finish.

The result:

of 13

Save immediately – in my case there's a lot of data - use xlsm as we will run a macro. Run the code for each sheet. The code cleans up the data. First I make sure that the number of apps the code found matches that of iTunes. If not, there may be a data issue. After running the code for both sheets and checking the results, combine the two sheets together. I just used copy/paste. I compare the file size columns to make sure everything is OK. Sometimes weird things happen when iTunes prints the list. If you want the full app name, create a third PDF from iTunes of just name and size. The Excel code for the above process follows. Run the following routine.

sub processTheData

of 13

Option Explicit 'March 25 2017 'Lee Townsend. ltownsend at hartford dot edu 'The code was developed in Excel 2011 on a 2010 MacBookPro. 'NOTE: the code tells you how many apps it found. If it disagrees then there ' is an issue with the data. Please look at the data just after it is read in ' to make sure all is fine. I found that sort by size will show ' the bad data Sub processTheData() Dim numRows As Long Dim numRowEnd As Long Dim rngSize As Range Dim strRange As String Dim rngAppsList As Range Dim sortRange As String Dim sortKey As String Application.ScreenUpdating = False ' Call GetTheData(1) 'See the module calle DebugHelp '<-------- Prepare the data for processing ------------------ numRows = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Set rngSize = Range("$C$7:$C" & numRows) Set rngAppsList = Range("$A$7:$K" & numRows) 'Get rid of a weird character. When I saved the text file from Word, I used UTF-8 Cells.Replace What:="Â", Replacement:="", LookAt:=xlPart, SearchOrder _ :=xlByColumns, MatchCase:=False 'Get rid of extraneous pictures ActiveSheet.Pictures.Delete 'Add the numbers column 'The title row must be in row 11 and start in column B. ' If MsgBox("Is the first title in B7?", vbYesNo) = vbNo Then Exit Sub Cells(7, 1) = "#" Cells(8, 1) = "1" Range("A8:A" & numRows).DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False '<-------- Process the data ------------------ 'Sort the data on column C - the size. All the non-sizes move to the bottom

of 13

ActiveSheet.Sort.SortFields.Clear sortKey = "$C$7" rngAppsList.Sort key1:=Range(sortKey), order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom 'Find the location of the first row that starts with Page in column C On Error GoTo noPage numRowEnd = WorksheetFunction.Match("Page" & "*", rngSize, 0) Rows(numRowEnd & ":" & numRows).Delete numRows = numRows - numRowEnd - 1 noPage: Err.Clear 'Name then blanks are at the bottom Call deleteExtraneousRows '<---------- Tidy up ---------------------------------------- 'Sort the data into the original order ActiveSheet.Sort.SortFields.Clear sortKey = "$A$7" rngAppsList.Sort key1:=Range(sortKey), order1:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Call fixTheTitleRow Call formatAppsList 'Put the number of apps found at the top. If there is disagreement with ' the iTunes statement, your data probably has an issue numRows = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Cells(3, 2).Value = " Number of apps found is " & _ Cells(numRows, 1).Value Range("A1").Select Application.ScreenUpdating = True End Sub Sub fixTheTitleRow() Dim combinedTitle As String 'First make room for the new title Range("D7").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

of 13

'Store a copy of the combined titles combinedTitle = Range("C7").Value 'Split the title Range("C7").Value = Left(combinedTitle, 4) Range("D7").Value = Right(combinedTitle, Len(combinedTitle) - 6) End Sub 'Fix the titles leftover from having to split the size column from the ' next column Sub deleteExtraneousRows() Dim numRows As Long Dim strRange As String Dim firstBlankRow As Long Dim firstSizeRow As Long numRows = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row 'Sorted the data on column C - the size. All the non-sizes move to the ' bottom firstBlankRow = WorksheetFunction.Match(" ", Range("$B$7:$B" & _ numRows), 0) + 6 'Delete the blank rows at the bottom strRange = firstBlankRow & ":" & numRows Range(strRange).Delete '<-------------- Blank rows are deleted ------------ numRows = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row firstSizeRow = WorksheetFunction.Match("Size*", Range("$C$8:$C" & _ numRows), 0) + 7 strRange = firstSizeRow & ":" & numRows Range(strRange).Delete '<-------------- TItle rows are deleted ------------ End Sub Sub formatAppsList() Dim numRows As Long 'Trim the data - some titles seem to have blanks in front of them numRows = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Range("L8").Select ActiveCell.FormulaR1C1 = "=TRIM(RC[-10])" Range("L8:L" & numRows).Select

of 13

Selection.FillDown 'Copy/Paste Special over column B Selection.Copy Range("B8").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False 'Delete the extra column Columns("L").ClearContents Cells(1, 1).Select 'Redo the series numRows = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Range("A8:A" & numRows).DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False 'Set the title row font Rows("7:7").Select With Selection.Font .Size = 14 .Bold = True End With 'Delete extra rows at the top Rows("6:6").Select Selection.Delete Shift:=xlUp Rows("1:2").Select Selection.Delete Shift:=xlUp 'Align the version column - align all left Columns("F:F").HorizontalAlignment = xlLeft 'Center the numbering column Columns("A:A").HorizontalAlignment = xlCenter 'Autofit the columns Columns("A:F").AutoFit End Sub

of 13

When testing the code I needed to keep reloading the text file from Word. I ended up automating the process as follows. The two text files were called Page0.txt and Page1.txt. Once read in, I copied the two sheets to keep for debugging. I protected both copied sheets so that I would not change them by mistake. I then wrote the following code to restore the original data to Page0 and Page 1.

Option Explicit 'I left this code in for future debugging. At the top of the subroutine processTheData 'I call this routine. ' Call GetTheData(1) '0 or 1 1 is OK 'I had two sheets for each iTunes printout. ' Page0 Page1 Page0 (2) Page1 (2) 'The (2)s were protected. They contained the original data imported as 'tab delimited text. Sub GetTheData(pageNumber As Integer) Dim numRows As Long Dim sourcePageName As String Dim gotoPageName As String sourcePageName = "Page" & pageNumber & " (2)" gotoPageName = "Page" & pageNumber Sheets(sourcePageName).Cells.Select Selection.Copy Sheets(gotoPageName).Activate ActiveSheet.Cells.Clear Range("A1").Select ActiveSheet.Paste Cells(7, 1) = "#" Cells(8, 1) = "1" numRows = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row Range("A8:A" & numRows).DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, Date:=xlDay, Step:=1, Trend:=False Columns("A:A").HorizontalAlignment = xlCenter End Sub

making a usable list of ios apps in...

Documents