1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
| Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim temps As String
Dim ISBN As String
ISBN = UserForm1.TextBox1.Value
If temps = ISBN Then
Sheets("stock").Activate
Cells.Find(What:=temps, After:=Range("b2"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
If MsgBox("L'article est déjà présent dans le stock:" & Chr(10) & "Voulez-vous modifier le stock de l'article", vbYesNo, "Doublon enregistrement")= vbYes Then
Sheets("stock").Range("I" & ActiveCell.Row).Value = Sheets("stock").Range("I" & ActiveCell.Row).Value + 1
MsgBox "Opération réalisé !"
End If
Else: CreateObject("Wscript.shell").Popup "Article non trouvé" & Chr(10) & "Veillez à l'enregistrer SVP", 1, "Information produit", vbExclamation
With Sheets("temp")
With .QueryTables.Add(Connection:= _
"URL;http://www.amazon.fr/s/ref=nb_sb_noss?__mk_fr_FR=%C3%85M%C3%85%C5%BD%C3%95%C3%91&url=search-alias%3Daps&field-keywords=" & ISBN _
, Destination:=Sheets("temp").Range("$A$1"))
.Name = "Test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
'.WebSelectionType = xlSpecifiedTables
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
'.WebTables =
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End With
TextBox2 = Sheets("temp").Range("a19")
TextBox3 = Sheets("temp").Range("a19")
TextBox5 = Sheets("temp").Range("a23")
End If
End Sub |
Partager