Crazy knowledge base

Excel 宏编程实例

‘Public Function Shipping(Price)

‘Shipping = Price * 0.1

‘MsgBox “测试函数”

‘End Function

‘Sub cmdSmallFont_Click()

‘ cmdSmallFont_Click Macro

‘ 宏由 FtpDown 录制,时间: 2011-2-9

‘    With Selection.Font

‘        .Name = “Arial”

”        .Size = 22

‘    End With

‘    Selection.Font.Italic = True

‘End Sub

‘MsgBox objXMLDom.selectNodes(“/data/dataset/segment”).Item(0).Attributes.getNamedItem(“date”).Text

‘Worksheets(1).Range(“A1:F1”).Interior.Color = &HFF00

‘MsgBox Worksheets(1).Range(“A1”).Value

‘MsgBox segment.childNodes.Item(0).Attributes.getNamedItem(“date”).Text

Sub cmdRenderBaiduData()

‘   Copyright (c) nimads 2011

‘   written by Jovi Gu

Dim objXMLDom As New MSXML.DOMDocument

Dim objXMLNodeList As MSXML.IXMLDOMNodeList

Dim objXMLNode As MSXML.IXMLDOMNode

Dim bSuccess As Boolean

Dim url As String

Dim i, x, y As Integer

i = 1

objXMLDom.async = False

objXMLDom.validateOnParse = False

url = “http://127.0.0.1/test.xml”

t1 = InputBox(“请输入日期:” & Chr(13) & Chr(10) & “日期格式:(yyyy-mm-dd)”, “请输入日期”)

If t1 = “” Then t1 = Date

bSuccess = objXMLDom.Load(url)

If bSuccess Then

If objXMLDom.selectNodes(“/data/dataset”).Length > 0 Then

For Each dataset In objXMLDom.selectNodes(“/data/dataset”)

x = 3

y = 1

For Each segment In dataset.childNodes

Worksheets(i).Cells(x, y).Value = segment.Attributes(0).Text

tday = Weekday(segment.Attributes(0).Text)

If tday = 7 Or tday = 1 Then

Worksheets(i).Rows(x).Interior.Color = RGB(100, 200, 255)

Else

Worksheets(i).Rows(x).Interior.Color = RGB(255, 255, 255)

End If

y = y + 1

For Each node In segment.childNodes

‘fill data is area

Worksheets(i).Cells(1, y).Value = node.Attributes(0).Text

‘fill data is click

Worksheets(i).Cells(x, y).Value = node.Attributes(1).Text

y = y + 1

‘fill data is show

Worksheets(i).Cells(x, y).Value = node.Attributes(2).Text

y = y + 1

‘fill data is cost

Worksheets(i).Cells(x, y).Value = node.Attributes(3).Text

y = y + 1

Next

y = 1

x = x + 1

Next

i = i + 1

Next

MsgBox “数据导入成功。”, vbOKOnly, “数据导入成功”

Else

MsgBox “数据源解析错误。”, vbCritical, “错误”

End If

Else

MsgBox “数据源读取错误。”, vbCritical, “错误”

End If

End Sub

Excel 宏编程实例
滚动到顶部