Excel提取淘宝/天猫整店所有产品的ID 货号 价格…

Excel破解版【下载地址↓】

1.提取表格中添加在文字里的链接(*只要是文字中内嵌的链接,都可以用这个代码把链接提取出来)

Sub 提取链接()

Selection.Cells.Offset(0, 1).EntireColumn.Insert
For Each cell In Selection.Cells
cell.Offset(0, 1) = cell.Hyperlinks(1).Address
Next

End Sub

2.提取表格中添加在文字里的链接,并提取链接中的id(淘宝天猫)

Sub 提取文字中链接的id()

Selection.Cells.Offset(0, 1).EntireColumn.Insert
For Each cell In Selection.Cells
cell.Offset(0, 1) = cell.Hyperlinks(1).Address
cell.Offset(0, 1) = Mid(cell.Offset(0, 1), InStr(cell.Offset(0, 1), "id=") + 3, 12)
    With cell.Offset(0, 1)
    .NumberFormatLocal = "0_ "
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    End With
cell.Offset(0, 1).Value = "'" & cell.Offset(0, 1).Value
Next

End Sub

3.让产品id再转成链接(淘宝天猫)

Sub id转链接()

Selection.Cells.Offset(0, 1).EntireColumn.Insert
Selection.Cells.Offset(0, 1).EntireColumn.Insert
For Each cell In Selection.Cells
    cell.Offset(0, 1) = "https://detail.tmall.com/item.htm?id=" & cell
    cell.Offset(0, 2) = "//h5.m.taobao.com/awp/core/detail.htm?id=" & cell
Next
   
End Sub

4.提取原链接中的id,然后生成PC短链接&手机端链接(淘宝天猫)

Sub 提取id并转链接()

Selection.Cells.Offset(0, 1).EntireColumn.Insert
For Each cell In Selection.Cells
cell.Offset(0, 1) = Mid(cell, InStr(cell, "id=") + 3, 12)
    With cell.Offset(0, 1)
    .NumberFormatLocal = "0_ "
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    End With
cell.Offset(0, 1).Value = "'" & cell.Offset(0, 1).Value
cell.Offset(0, 2) = "https://detail.tmall.com/item.htm?id=" & cell.Offset(0, 1)
cell.Offset(0, 3) = "//h5.m.taobao.com/awp/core/detail.htm?id=" & cell.Offset(0, 2)
Next
  
End Sub

VBA使用教程后续更新,敬请期待……

© 版权声明
THE END
喜欢就支持一下吧
点赞9赞赏 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片