A little more, or more exactly, a way of doing things differently.
Enter the ISBN or the bar code of the book in cell B3 of the "Datas" page. This means that it is therefore possible to enter this value via a barcode scanner (
EAN 13)... Once entered, the call to the
Google’s API is done naturally and the cells C3 to H3 fill normally. Sometimes a field is not filled; the word “
None” is therefore placed in the concerned cell. A
conditional format emphasizes this lack, just to make it clear.
There is in cell A3 a hyperlink “
Copy in list” which calls a macro which will automatically make the copy of the useful cells of the line 3 in a list located just below. The addition is always done at the end of the list. In this collection, therefore, there is no longer any unnecessary calls, but simply storage, which can be modified or supplemented and, of course, eventually transformed into a real database.
AutoFilters still allow you to quickly sort the list, which can be very practical for grouping works with the same content (typically: “
None”).
Finally, we will easily notice the advantage of
freezing the six first lines, which allows, of course, to always have in view the end of the list on which we make the copies.
Enjoy!
Code: Select all
Option Explicit
Const myZone1 = "$B$3:$H$3" ' Range where the fields of the book are downloaded.
' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Copy the data range myZone1 to the bottom of the list. ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
' ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Sub copyInList()
Dim mySheet As Object, myFunctionAccess As Object, myDataZone1 As Object, myDataZone2 As Object
Dim myArgs(3) As Variant
Dim myZone2 As String
myFunctionAccess = createUnoService("com.sun.star.sheet.FunctionAccess")
myArgs(3) = knowLastCell("Datas", 1, 0) + 2
myArgs(0) = myZone1 : myArgs(1) = 4 : myArgs(2) = 1
myZone2 = myFunctionAccess.callFunction("REPLACE", myArgs())
myArgs(0) = myZone2 : myArgs(1) = len(myZone2) : myArgs(2) = 1
myZone2 = myFunctionAccess.callFunction("REPLACE", myArgs())
mySheet = thisComponent.sheets.getByname("Datas")
myDataZone1 = mySheet.getCellRangeByName(myZone1)
myDataZone2 = mySheet.getCellRangeByName(myZone2)
myDataZone2.dataArray = myDataZone1.dataArray
End Sub
' ╔══════════════════════════════════════════════════════════════════════════════════╗
' ║ Search the number of the last line with something in cell in the column. ║█
' ║ or the number of the last column with something in cell in the line ║█
' ║ ║█
' ║ If myType = 0, the search is do on the column. ║█
' ║ If myType = 1, the search is do on the line. ║█
' ╚══════════════════════════════════════════════════════════════════════════════════╝█
' ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
Function knowLastCell(mySheetName As String, myColumnOrMyLine As Long, myType As Integer) As Long
Dim mySheet As Object, myBlock As Object, myFound As Object
On Error Goto myError
mySheet = thisComponent.sheets.getByName(mySheetName)
Select Case myType
Case 0 : myBlock = mySheet.columns(myColumnOrMyLine)
Case 1 : myBlock = mySheet.rows (myColumnOrMyLine)
End Select
myFound = myBlock.queryEmptyCells.rangeAddresses
With myFound(uBound(myFound))
Select Case myType
Case 0 : knowLastCell = .startRow - 1
Case 1 : knowLastCell = .startColumn - 1
End Select
End With
Exit Function
myError: ' If the column or the line is empty.
knowLastCell = -1
End Function