Excel怎么抓取互联网数据,专业薄及专业表

作者: 金沙澳门官网  发布:2019-08-14

Step2:使用“查找与引用”函数实现数据查询

建立查询区域,包含“届数”和“主办城市”,在届数中随意选取一届输入,下图输入“第08届”,在主办城市下输入vlookup函数,可以得到第08届奥运会的主办城市是巴黎,当更改届数时,对应的主办城市也随之变动。

公式:=VLOOKUP([届数],奥运会[#全部],4,0)

金沙澳门官网 1

注意点:若网页中的数据变动较频繁,则可以设置链接网页的数据定时刷新:

①将鼠标定位于导入的数据区域中,切换到选项卡,点击下拉箭头→

金沙澳门官网 2

②在弹出的对话框中,设置,比如设置为10分钟进行刷新。这样,每隔10分钟数据就会刷新一次,时刻保证获取的数据位最新的。

金沙澳门官网 3


style="font-weight: bold;">「精进Excel」系头条签约作者,关注我,如果任意点开三篇文章,没有你想要的知识,算我耍流氓!

回答:

金沙澳门官网 ,大家好,我是@Excel实例视频网站长@欢迎私信或者邀请我回答Excel相关问题!


有人在群里问手机号怎么批量查归属地,第一感觉是百度一下,结果还真没找到好用的,既然如此,我就自己写一个吧!首先找了几个webapi,找到个挺好用的,就用vba写了个自定义函数,测试下感觉还是挺好用,速度也挺快

金沙澳门官网 4

style="font-weight: bold;">源文件下载链接请私信回复63005即可

使用方法:

1.在本表中直接在A1列输入手机号即可

2.要在其他表中,alt f11打开vbe编辑器,复制模块中代码,在你的新表中建立模块,粘贴代码即可

3.函数参数说明

GetPhoneInfo(号码,参数)

号码—即单个手机号

参数(1,2,3,4):1-城市,2-省,3-运营商, 4-全部

代码如下

Dim ObjXML As Object

Function GetPhoneInfo(number, Optional para As Byte = 1)

'获取手机号对应的基本信息 默认为城市

'para:1-城市,2-省,3-运营商,4,全部

Dim s As String

s = GetBody("" & number)

Select Case para

Case 1

GetPhoneInfo = HtmlFilter(s, "City"":""", """")

Case 2

GetPhoneInfo = HtmlFilter(s, "Province"":""", """")

Case 3

GetPhoneInfo = HtmlFilter(s, "TO"":""", """")

Case 4

GetPhoneInfo = HtmlFilter(s, "City"":""", """") & "," & HtmlFilter(s, "Province"":""", """") & "," & HtmlFilter(s, "TO"":""", """")

End Select

GetPhoneInfo = Replace(GetPhoneInfo, " ", "")

End Function

Private Sub Test()

Dim i&, j&, k&, arr, brr

url = ""

Debug.Print GetBody(url)

End Sub

'''如果出现乱码,UTF-8可改为GB2312

Public Function GetBody(ByVal url$, Optional ByVal Coding$ = "utf-8")

On Error Resume Next

Set ObjXML = CreateObject("Microsoft.XMLHTTP")

With ObjXML

.Open "Get", url, False, "", ""

'.setRequestHeader "If-Modified-Since", "0"

'.setRequestHeader "User-Agent", _

".Mozilla/5.0 (Windows NT 6.1; WOW64; rv:47.0) Gecko/20100101 Firefox/47.0"

.Send

GetBody = .ResponseBody

End With

GetBody = BytesToBstr(GetBody, Coding)

Set ObjXML = Nothing

End Function

Public Function BytesToBstr(strBody, CodeBase)

Dim ObjStream

Set ObjStream = CreateObject("Adodb.Stream")

With ObjStream

.Type = 1: .Mode = 3: .Open:

.Write strBody: .Position = 0: .Type = 2: .Charset = CodeBase

BytesToBstr = .ReadText: .Close

End With

Set ObjStream = Nothing

End Function

Public Function HtmlFilter(ByVal htmlText$, ByVal Label1$, ByVal label2$)

'返回html字符串lable1和最近的lable2标签中的数据

Dim pStart As Long, pStop As Long

pStart = InStr(htmlText, Label1) Len(Label1)

If pStart <> 0 Then

pStop = InStr(pStart, htmlText, label2)

HtmlFilter = Mid(htmlText, pStart, pStop - pStart)

End If

End Function

回答:

专业的人做专业事情。

Option Explicit

这是抓取的赔率数据

金沙澳门官网 5

总之,如果想学是不难的。

回答:

以EXCEL2003为例来给你说明。

一、首先打开EXCEL2003,在菜单栏找到“数据”然后在下拉菜单点击“导入外部数据-新建WEB查询”
金沙澳门官网 6
二、然后在打开的对话框中的地址栏中,将你要导入的网址输入进去,按下转到按钮。
金沙澳门官网 7
三、在弹开的对话框中原则需要导入的区域,按下导入按钮,这个时候,数据就被导入到EXCEL里面啦!
金沙澳门官网 8最后,你的电脑得链接网络,要不没有数据,这样导入的好处是,可以和网站上保持一致,无需进行手动更新,很方便。

Private Declare Function SetWindowPos Lib "user32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal x As Long, y, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
Private Const HWND_TOPMOST = -1
Private Const HWND_NOTOPMOST = -2
Private Const SWP_NOMOVE = &H2
Private Const SWP_NOSIZE = &H1
Private Const TOPMOST_FLAGS = SWP_NOMOVE Or SWP_NOSIZE

如果只是偶尔有这个任务,还是在网上出点钱,找人做了。

花费的钱真的不多。几百元足够了。

Set vld = Target.Validation
On Error GoTo Terminate
sFml1 = vld.Formula1
On Error GoTo 0

如果是平时任务多,且有一定的基础,学习一下未必不可。

老猫是通过VBA操作的,写一个代码,抓取数据,也很方便。

老猫正在开发的一款足彩软件程序救市从网上抓取大量数据。然后分析和预测足彩。

测试 WorkBook 是否已开启

例:下图是百度百科“奥运会”网页中的一个表格,我们以此为例实现抓取该表格至Excel中,并且能够通过输入第几届来查询对应的举办城市。

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Const dFixedPos As Double = "0.8"
Const dFixWidth As Double = "16" 'Change here to change WIDTH of the DropDown
Dim vld As Validation
Dim lDpdLine As Long

回答:

On Error GoTo 0
If Not Rng1 Is Nothing Then
For Each Cell In Rng1
If Left(Cell.Formula, 2) = "='" Then
Cell.Value = Cell.Value
End If
Next
End If
Set Rng1 = Nothing
End With
Next

这是VBA程序代码

金沙澳门官网 9

金沙澳门官网 10

Excel抓取并查询网络数据可以使用“获取和转换” “查找引用函数”的功能组合来实现。

'// 293=Delete menu of the right click on row
'// 294=Delete menu of the right click on column
'// 293=Delete menu of the Edit of main menu
arrIdNum = Array(293, 294, 478)

Step1:使用“获取和转换”功能将网络数据抓取至Excel中

依次点击“数据选项卡”、“新建查询”、“从其他源”、“从Web”。

金沙澳门官网 11

弹出如下窗口,手动将百度百科“奥运会”的网址复制粘入URL栏,并点击确定。

金沙澳门官网 12

Excel与网页连接需要一定时间,稍等片刻后会弹出如下窗口,左边列表中的每个Table都代表该网页中的一个表格,挨个点击预览后发现,Table3是我们所需的数据。

金沙澳门官网 13

点开下方的“加载”旁边的下拉箭头,选择“加载到”。

金沙澳门官网 14

在弹出的窗口中,在“选择想要在工作薄中查看此数据的方式”下选择“表”,并点击加载。

金沙澳门官网 15

如图,网页表格中的数据已被抓取至Excel中。

金沙澳门官网 16

依次点击“表格工具”、“设计”,将“表名称”改为奥运会。

金沙澳门官网 17

防止 Excel 關閉

问题:在平时工作中会遇到,知道其中一个数据,比如姓名,在表格中输入姓名后,想要自动带出网页中该姓名对应的相关数据,比如该姓名的电话,地址等信息,如何做到呢?

Set prvTarget = Target

这是抓取的比赛列表:

金沙澳门官网 18

Option Explicit

金沙澳门官网 19

Sub get_Mod_Size()
Dim myProject As Object
Dim ComName As String
Dim tempPath As String
Dim fs As Object, a As Object
Dim result As String

----------------- Module

Public Sub SetShutDownPrivilege()
Dim Phndl As Long, Thndl As Long
Dim MyLUID As LUID
Dim MyPriv As TOKEN_PRIVILEGES, MyNewPriv As TOKEN_PRIVILEGES

' Delete the exported file
fs.Deletefile tempPath

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
Public LoginTime

Public Sub MakeNormal(hwnd As Long)
SetWindowPos hwnd, HWND_NOTOPMOST, 0, 0, 0, 0, TOPMOST_FLAGS
End Sub
Public Sub MakeTopMost(hwnd As Long)
SetWindowPos hwnd, HWND_TOPMOST, 0, 0, 0, 0, TOPMOST_FLAGS
End Sub

指定电脑上运行

Option Explicit

2.

On Error Resume Next
oDpd.Delete
sFml1 = vbNullString
Set oDpd = Nothing
On Error GoTo 0

Option Explicit

Private Sub DelExecute(ByVal str, ByVal lngDerec As Long)
MsgBox "deleted:" & str
Selection.Delete lngDerec
End Sub

Sub SaveShtsAsBook()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
MyFilePath$ = ActiveWorkbook.Path & "" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
' End With
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
End With
'save book in this folder
.SaveAs Filename:=MyFilePath _
& "" & SheetName & ".xls"
.Close SaveChanges:=True
End With
.CutCopyMode = False
Next
End With
Sheet1.Activate
End Sub

' Set these to run
ComName = "Module1"
tempPath = "c:Test.bas"

' ***** No action needed after this point *****

' 23 - All formulae
' 16 - All formulae with errors
' 2 - All formulae with text
' 4 - All formulae with logic
' 6 - All formulae with text or logic

For Each WS In ActiveWorkbook.Worksheets
With WS
On Error Resume Next
Set Rng1 = Cells.SpecialCells(xlCellTypeFormulas, 23)

MicroSoft 沒有文件顯示 編碼 的大小限制
64K 太大,很難跟進

'用 F8 逐句执行篮色编码,取值后更改红色部份

For lngId = LBound(arrIdNum) To UBound(arrIdNum)
Set CtrlCbcRet = CommandBars.FindControls(ID:=arrIdNum(lngId))
For Each CtrlCbc In CtrlCbcRet
CtrlCbc.OnAction = strProc
Next
Set CtrlCbcRet = Nothing
Next
End Sub

If Not prvTarget Is Nothing Then
If Not oDpd Is Nothing Then
If oDpd.Value = 0 Then
prvTarget.Value = vbNullString
Else
prvTarget.Value = Range(Mid(sFml1, 2)).Item(oDpd.Value)
End If
Set prvTarget = Nothing
End If
End If

End Sub

Public Type TOKEN_PRIVILEGES
PrivilegeCount As Long
Privileges(1) As LUID_AND_ATTRIBUTES
End Type

Function RegRead()
'RegRead: 從註冊傳回鍵的值或值名稱
On Error Resume Next
Dim WshShell, bKey
fname = ThisWorkbook.Name
Regkey = "HKCUchijanzenBudgetDate" & fname
Set WshShell = CreateObject("WScript.Shell")
RegRead = WshShell.RegRead(Regkey)
End Function

可以监控删除行及列吗

' chijanzen
(原始) 2003/10/1
' 今天介紹如何讓Excel檔案有使用期限,範例中使用Windows Script"在註冊表上的讀.寫.刪除的用法
' 本範例使用期限設定 0 天,所以檔案只能開啟一次就自動銷毀
' Script 能使用的根鍵值有五個根鍵名稱
HKEY_CURRENT_USER '縮寫 HKCU
HKEY_LOCAL_MACHINE '縮寫 HKLM
HKEY_CLASSES_ROOT '縮寫 HKCR
HKEY_USERS '縮寫 HKEY_USERS
HKEY_CURRENT_CONFIG '縮寫 HKEY_CURRENT_CONFIG

原碼出自 Tek-Tips Forum

' Get the size of the file created
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.getfile(tempPath)
result = ComName & " uses " & (a.Size / 1000) & " KB."

' Module

If Target.Count > 1 Then
Set oDpd = Nothing
Exit Sub
End If

On Error Resume Next
Set VBCodeMod = Workbooks(wbk).VBProject.VBComponents(VBComp).CodeModule
With VBCodeMod
StartLine = .CountOfDeclarationLines 1
Do Until StartLine >= .CountOfLines
ReDim Preserve aList(1 To 3, 1 To x - 1)
aList(1, x - 1) = wbk
aList(2, x - 1) = VBComp
aList(3, x - 1) = .ProcOfLine(StartLine, vbext_pk_Proc)
x = x 1
StartLine = StartLine .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
If Err Then Exit Sub
Loop
End With
Set VBCodeMod = Nothing
End Sub

' **************************************************************************************
' Use this to determine the size of a module
' Set ModName (component name) and tempPath (where to store the temp fule), then run
' **************************************************************************************

Private Sub Workbook_Open()
Dim TempUName ' User Name
Dim TempPCName ' PC Name
TempPCName = GetComputerName
TempUName = UserName
If TempPCName <> "PCName01" And TempPCName <> "PCName02" And TempUName <> "BeeBee" _
And TempPCName <> "EMILY" Then
MsgBox "Sorry, This File is for BeeBee ONLY."
Application.Quit
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Myrange As Range, KeepOut As Range
Dim ws As Worksheet

' Module
' List All VBA module
Dim x As Long
Dim aList()

Private Sub Workbook_Open()
On Error Resume Next
'Activate the 1st worksheet using the workbooks worksheet index
Worksheets(1).Activate
'Or If you want to use the actual worksheet name
'Worksheets("Sheet1").Activate
End Sub

'// Worksheet RowColumn Deleted Event
'// This is NOT a real event but just hack the command button.
'// You can know when the rows or the columns was deleted by user's opelation.

金沙澳门官网 20

Sub GetVbProj()
Dim oVBC As VBIDE.VBComponent
Dim Wb As Workbook
x = 2
For Each Wb In Workbooks
For Each oVBC In Workbooks(Wb.Name).VBProject.VBComponents
If Workbooks(Wb.Name).VBProject.Protection = vbext_pp_none Then
Call GetCodeRoutines(Wb.Name, oVBC.Name)
End If
Next
Next
With Sheets.Add
.[A1].Resize(, 3).Value = Array("Workbook", "Module", "Procedure")
.[A2].Resize(UBound(aList, 2), UBound(aList, 1)).Value = _
Application.Transpose(aList)
.Columns("A:C").Columns.AutoFit
End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Dim Msg, Style, Title, Response
Dim MyFlag As Long, Ret As String
'Set ShutDown Constants
Const EWX_LOGOFF = 0
Const EWX_SHUTDOWN = 1
Const EWX_REBOOT = 2
Const EWX_FORCE = 4

ActiveCell.Select ActiveWindow.ScrollRow = ActiveCell.Row ActiveWindow.ScrollColumn = ActiveCell.Column

Sub CheckFileDate()
Dim Counter As Long, LastOpen As String, Msg As String
If RegRead = "" Then
Term = 0 '範例用 0 天
TermDate = DateSerial(Year(Now), Month(Now), Day(Now)) Term
MsgBox "本檔案只能使用到" & TermDate & "日" & Chr(13) & "超過期限將自動銷毀"
RegWrite (Term)
Else
If CDate(RegRead) <= Now Then
RegDelete
KillMe
End If
End If
End Sub
Sub KillMe()
Application.DisplayAlerts = False
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ThisWorkbook.Close False
End Sub

Application.Goto ActiveCell, True

本文由金沙澳门官网发布于金沙澳门官网,转载请注明出处:Excel怎么抓取互联网数据,专业薄及专业表

关键词: 金沙澳门官网