¼ÐÃD:
[µo°Ý]
¸ü¤Jºô¶¸ê®Æªº°ÝÃD
[¥´¦L¥»¶]
§@ªÌ:
yuch8663
®É¶¡:
2013-6-30 21:02
¼ÐÃD:
¸ü¤Jºô¶¸ê®Æªº°ÝÃD
½Ð°Ý¦b³o²Õµ{¦¡¸Ì·Q±N¤é´Á³¡¤À§ï¬°ÅܼÆ`´N·|¥X²{ "¥²¶·¬O±`¼Æ¹Bºâ¦¡"ªº¿ù»~°T®§¡AÀ³¸Ó¦p¦óקï?ÁÂÁ¡C
Sub Test()
Sheets("sheet1").Select
'http://www.xe.com/currencytables/?from=USD&date=2013-06-23
Dim a, b, c As String
a = Sheets("sheet1").Range("p1").Text ' M°_ºâ¦~
b = Sheets("sheet1").Range("p2").Text ' M°_ºâ¤ë
c = Sheets("sheet1").Range("p3").Text ' M°_ºâ¤é
Const url As String = "http://www.xe.com/currencytables/?from=USD&date=" & a & "-" & b & "-" & c ' ¥¿½Tªº
Cells.Clear
Set ie = CreateObject("internetexplorer.application") '¨Ï¥Î¦¹¤è¦¡¥i¥H§K°£ "³]©w¤Þ¥Î¶µ¥Ø"
With ie
.Visible = False 'True¬°¶}±Òie, False¬°¤£¶}±Òie
.Navigate url
Do While .ReadyState <> 4 'µ¥«Ýºô¶¶}±Ò
DoEvents
Loop
.ExecWB 17, 2 'Select All
.ExecWB 12, 2 'Copy selection
Range("A1").Activate
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
False, NoHTMLFormatting:=True
End With
' Columns("A:B").Delete ' ±N¶×¤J®É A¡BB ¨âÄæ²¾°£¡A 쥻 C:I ªºÄæ¦ì¥þ³¡¥ª¾a¡A ¦¨¬° A:I
ie.Quit
' MsgBox "¸ê®Æ½Æ»sµ²§ô" ' Â÷¶}«eÅã¥Ü¤@¤pµøµ¡´£¿ô¡A«ö¥¦«á§Yµ²§ô¡C
Dim qyt As QueryTable
For Each qyt In Worksheets("sheet1").QueryTables
qyt.Delete
Next
End Sub
[attach]15341[/attach]
§@ªÌ:
HSIEN6001
®É¶¡:
2013-6-30 22:13
¦^´_
1#
yuch8663
°Ñ¦Ò![attach]15343[/attach]
§@ªÌ:
GBKEE
®É¶¡:
2013-7-1 07:20
¦^´_
1#
yuch8663
Const ³¯z¦¡
»yªk
[Public | Private] Const constname [As type] = expression
Const ³¯z¦¡ªº»yªk¨ã¦³¤U¦C´X¶µ³æ¤¸¡G
³æ¤¸ ´yz
Public ¿ï¾Ü©Ê¤Þ¼Æ¡C¼Ò²Õ¼h¦¸©Ò¨Ï¥ÎªºÃöÁä¦r¡A¥Î¨Ó«Å§i¦b©Ò¦³¼Ò²Õùتº©Ò¦³µ{§Ç¤¤³£¥i¥H¨Ï¥Î±`¼Æ¡C ¤£¥i¦bµ{§Ç¤¤¨Ï¥Î¦¹¶µ«Å§i¡C
Private ¿ï¾Ü©Ê¤Þ¼Æ¡C¼Ò²Õ¼h¦¸ªºÃöÁä¦r¡A¥Î¨Ó«Å§i¥u¦b¤w«Å§iªº¼Ò²Õùؤ~¥i¥H¨Ï¥Î±`¼Æ¡C¤£¥i¦bµ{§Ç¤¤¨Ï¥Î¦¹¶µ«Å§i¡C
Constname ¥²nªº¤Þ¼Æ¡C±`¼Æªº¦WºÙ¡A¿í´`ÅܼƩR¦W³W®æ¡C
Type ¿ï¾Ü©Ê¤Þ¼Æ¡C±`¼Æªº¸ê®Æ«¬ºA¡A¥i¥H¬O Byte¡BBoolean¡BInteger¡BLong¡BCurrency¡BSingle¡BDouble¡BDecimal (¥Ø«e¥¼¤ä´©¡^¡BDate¡BString¡A©Î¬O Variant¡C¨Ï¥Î As type ¤l¥y¨Ó«Å§i±`¼Æ¡C
Expression ¥²nªº¤Þ¼Æ¡C¤å¦r¡B¨ä¥¦±`¼Æ©Î¥ô¦óºâ³N¤ÎÅÞ¿è¹Bºâ¤lªº²Õ¦X¡A¦ý Is «h°£¥~¡C
½Ðª`·N
±`¼Æ¬Ò¹w³]¬°¨p¦³ªº¡Aµ{§Ç¤¤ªº±`¼Æ³£¬O¨p¦³ªº¡A±`¼Æªº¥i¨£©Ê¤£¥i¥H§ïÅÜ¡C¦b¤@¯ë¼Ò²ÕùØ¡A¼Ò²Õ¼h¦¸±`¼Æªº¦³®Ä½d³ò¥i¥H¥Î Public ÃöÁä¦r¨Ó§ïÅÜ¡CµM¦Ó¡A¦bª«¥óÃþ§O¼Ò²ÕùØ¡A±`¼Æ¥u¯à¬°¨p¦³ÅܼơA¥BµLªk¥Ñ Public ÃöÁä¦r¨Ó§ïÅܨä¥i¨£©Ê¡C
Yn§â¦hÓ±`¼Æ«Å§i¦¡©ñ¦b¤@¦æùØ¡A«h¥Î³r¸¹¨Ó¤À¹j¨CÓ±`¼Æ«ü©w¦¡¡C·í¦hÓ±`¼Æ«Å§i©ñ¦b¦P¤@¦æùØ¡AY¨Ï¥Î Public ©Î Private ÃöÁä¦r¡A«h¾A¥Î©ó¥þ³¡¦P¤@¦æªº±`¼Æ¡C
'*******************************
«ü©wµ¹±`¼Æªº¹Bºâ¦¡¤£¥i¥H¬OÅܼơB¨Ï¥ÎªÌ¦Ûq¨ç¼Æ©Î Visual Basic ¤º«Øªº¨ç¼Æ (¨Ò¦p Chr¡^¡C
'********************************
ªþµù ±`¼Æ¥i¥H¨Ï±zªºµ{¦¡¸û®e©öקï¡C©MÅܼƤ£¤@¼Ëªº¬O¡A¦bµ{¦¡°õ¦æ®É¡A±`¼Æ¥i¥HÁקK¤£¤p¤ß¿ù»~ªº×§ï¡C
¦pªG¨S¦³©ú½Tªº¨Ï¥Î As type ¨Ó«Å§i±`¼Æªº«¬ºA¡A«h±`¼Æ·|¿ï¥Î³Ì¾A¦X expression ªº«¬ºA¡C
¦b Sub¡BFunction ©Î Property µ{§Çùةҫŧiªº±`¼Æ¡A¥u¾A¥Î¦b¸Óµ{§Çªº°Ï°ìùØ¡C¦bµ{§Ç¥~©Ò«Å§iªº±`¼Æ¡A«h¬O¦b«Å§i¥¦ªº¼Ò²Õ¤¤©Ò©w¸qªº¡C¦b¥ô¦ó¥i¥H¨Ï¥Î¹Bºâ¦¡ªº¦a¤è¡A´N¥i¥H¨Ï¥Î±`¼Æ¡C
½Æ»s¥N½X
¥iקï¦p¤U
url = "http://www.xe.com/currencytables/?from=USD&date=" & a & "-" & b & "-" & c ' ¥¿½Tªº
½Æ»s¥N½X
§@ªÌ:
sunnyso
®É¶¡:
2013-7-1 08:00
·PÁ¤À¨É, ¦n¦n®ø¤Æ¤@¤U
§@ªÌ:
HSIEN6001
®É¶¡:
2013-7-1 09:03
¦^´_
3#
GBKEE
½Ð±Ð,Ãö©ó Public ¤Î Call
Call ¼Ò²Õ¦WºÙ.µ{§Ç¦WºÙ
NOTE:¦bµ{§Ç¤¤¥i¥H©I¥s¦P¤@¼Ò²Õ¤¤ªº¥ô¦óµ{§Ç,¦ý¹ï©ó¨ä¥¦¼Ò²Õ¤¤ªºµ{§Ç¥u¯à©I¥s³]©w¬°Publicªº±M®×¤Î¼Ò²Õµ{§Ç¡C
¤W±³o¬q»¡©ú¬O®Ñ¤W¼gªº¡A¦³ÂI§Ë¤£À´¡C
§Ú¦b°õ¦æ®É,Áö¤£¦P¼Ò²Õ,«o¥i©I¥s¤£¦P¼Ò²Õ,«o¨S³]©wPublic ªº¼Ò²Õ
½Ð°Ý³o·|¦³ °¸º¸ bugªº°ÝÃD?!
§í©Î.....§Úªº½d¨Ò,³o¼Ë¨â¦¸ call µ{§Çªº¼gªk¬O¿ùªº
¦]¬° "¦³®É" ·|¥X²{²Ä¤G¦¸©I¥s®É,¤u§@ªí¤Ï¶Âªºµe±
¥t¥~¤@°Ý:
Y§Q¥Î IE¤U¸üºô¶¸ê®Æ®É,¤u§@ºÞ²zûªº³B²zµ{§Ç¸Ì
®É±`¦³¥¼Ãö³¬ªºiexplore.exe
³o¸Ó«ç»ò³B²z??
[attach]15344[/attach]
§@ªÌ:
GBKEE
®É¶¡:
2013-7-1 09:42
¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-7-1 10:25 ½s¿è
¦^´_
1#
yuch8663
Sub Test()
Dim url As String
'url = "URL;http://www.xe.com/zh-HK/currencytables/?from=USD&date=" & a & "-" & b & "-" & c ' ¥¿½Tªº
'A,B,C -> YYYY-MM-DD
url = "URL;http://www.xe.com/zh-HK/currencytables/?from=USD&date=" & Format(Date - 1, "YYYY-MM-DD")
'¤é´Á¶·¤p©ó·í¤é
With Sheets("Sheet1")
.Cells.Clear
For Each e In .QueryTables
e.Delete
Next
With .QueryTables.Add(Connection:=url, Destination:=.[a1])
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """historicalRateTbl"""
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End With
End Sub
½Æ»s¥N½X
¦^´_
5#
HSIEN6001
Sub EX¾ã¶¤U¸ü(¼g¤Jªí®æ, ¼g¤J¦ì¸m, theURL) ¨S¥[µù¬O
Public
´N¬O¤½¥Îµ{§Ç ¥iª½±µ©I¥s
Private
Sub EX¾ã¶¤U¸ü(¼g¤Jªí®æ, ¼g¤J¦ì¸m, theURL) ¥[µù¬°¨p¥Îµ{§Ç
¨p¥Îµ{§Ç ¥ÎRUN ©I¥s
Run "A¤U¸ü¤½¥Îµ{¦¡.EX¾ã¶¤U¸ü", "¼È®É", Sheets("¼È®É").Range("B" & Rows.Count).End(xlUp).Offset(1), "
http://siis.twse.com.tw/server-java/STAMAK03?TYPEK=otc&isnew=&colorchg=1&year=102&smonth=01&emonth=12&step=1&order=12
"
¤u§@ºÞ²zû¤¤¦³¨SÃö³¬ªºIE,¥i¯à¬O¦bVBA¤¤¶}±Ò«á¨SÃö³¬§a
§@ªÌ:
HSIEN6001
®É¶¡:
2013-7-1 10:27
¥»©«³Ì«á¥Ñ HSIEN6001 ©ó 2013-7-1 10:31 ½s¿è
¦^´_
6#
GBKEE
¹ï!
Ãþ¦ü³o¼ËªºVBA¶}±ÒªºIE
§Ú²q...¬O§_¨S¦³§ä¨ìtable®ÉªºOn Error Resume Next
©Ò¯d¤U¨Óªº,²Ö¿n¤Ó¦h´N¼vÅT«áÄò¤U¸ü
¥u¯à¤â°Ê¤@ÓÓ±j¨î "µ²§ô³B²zµ{§Ç" or «¶}¾÷
¦³¨S¦³µ{§Ç¥i¥HÃö³¬¨º¨Ç´Ý¯d....
With CreateObject("InternetExplorer.Application")
.Visible = False
.navigate "http://...."
Do Until .readyState = READYSTATE_COMPLETE
DoEvents
Loop
Set A = .document.getElementsByTagName("table")
With Workbooks.Add
On Error Resume Next '***¦³¨Çtable¨SRows¸ê®Æ·|²£¥Í¿ù»~ ¤£²z·|¥¦,µ{¦¡Ä~Äò¨«
For ii = 11 To A.Length - 1
For i = 0 To A(ii).Rows.Length - 1
k = k + 1 '
For j = 0 To 2
Cells(k + 1, j + 1) = A(ii).Rows(i).Cells(j).innerText
Next
Next
Next
End With
.Quit 'Ãö³¬ºô¶
End With
½Æ»s¥N½X
********************************
"¦³®É" ·|¥X²{²Ä¤G¦¸©I¥s®É,¤u§@ªí¤Ï¶Âªºµe±.....¨º´N¤£¬O¦³¨S¦³¥[µùPublicªº°ÝÃD
§ÚÁÙ¨S§ä¨ì¬°¦ó "¦³®É" ¤u§@ªí·|¤Ï¶Âªºì¦]
§@ªÌ:
GBKEE
®É¶¡:
2013-7-1 10:34
¦^´_
7#
HSIEN6001
ÁöµM¬O On Error Resume Next '***¦³¨Çtable¨SRows¸ê®Æ·|²£¥Í¿ù»~ ¤£²z·|¥¦,µ{¦¡Ä~Äò¨«
'
'
¦ý³Ì«áÁÙ¬O·| .Quit 'Ãö³¬ºô¶
·Ó¹D²z¤£·|´Ý¯dIEªº.
n¥ÎVBA Ãö³¬´Ý¯dIE,ÁÙ¨S¸Õ¹L.
§@ªÌ:
stillfish00
®É¶¡:
2013-7-1 13:07
¦^´_
8#
GBKEE
¥i¯à¤UÂ_ÂI«á«öF8°±¤î¡A¤]·|´Ý¯d¡C
¤£©¯ªº¬OIE¤£¯à¥ÎGetObject¨ú±oª«¥ó¨Óµ²§ô±¼¡A
©³¤U¬O§ä¨ìªº¥t¥~¤@ºØ¤èªk¨Ñ°Ñ¦Ò
Sub QuitIE()
Dim oShellWindows As Object, obj As Object
Set oShellWindows = CreateObject("Shell.Application").Windows
If oShellWindows.Count > 0 Then
For Each obj In oShellWindows
If StrComp(Right(obj.FullName, 12), "iexplore.exe", vbTextCompare) = 0 Then obj.Quit
Next
End If
End Sub
½Æ»s¥N½X
§@ªÌ:
HSIEN6001
®É¶¡:
2013-7-1 14:18
¦^´_
9#
stillfish00
ì¨Ó¬O¤¤Â_«á¯d¤U¨Óªº
SO...
¦pªG§ÚªºÅã¥Ü³o¼Ëiexplore.exe *32
À³¸Ó¬O§ï¬°³o¼Ë?!
Sub QuitIE()
Dim oShellWindows As Object, obj As Object
Set oShellWindows = CreateObject("Shell.Application").Windows
If oShellWindows.Count > 0 Then
For Each obj In oShellWindows
If StrComp(Right(obj.FullName, 16), "iexplore.exe *32", vbTextCompare) = 0 Then obj.Quit
Next
End If
End Sub
½Æ»s¥N½X
But èè´ú¸Õ...¨S¤ÏÀ³
§@ªÌ:
stillfish00
®É¶¡:
2013-7-1 14:36
¥»©«³Ì«á¥Ñ stillfish00 ©ó 2013-7-1 14:48 ½s¿è
¦^´_
10#
HSIEN6001
FullName¤¤¬Oµ{§Çªº§¹¾ã¸ô®|¡A¹³§Úªº¬O "C:\Program Files\Internet Explorer\iexplore.exe"
¦A¥h¤ñ¸û«á±ªº¦r§À¬Ý¬O¤£¬OIEªºµ{§Ç¦Ó¤w¡AÀ³¸Ó¤£¥Î¥[ *32§a
¥t¥~§Úı±o§Anª`·Nªº¬OQueryTableY¥u¬O¥Î¨Ó¨ú±o¤@¦¸©Ê¸ê®Æ¡A³Ì¦nÁÙ¬O¶¶«K§â³s½u§R°£¡A¤£µM¨C¦¸³£·|·s¼W³s½u¡A¥H«á¸Ó¤u§@ªí´N¦³«Ü¦h¸ê®Æ³s½u...¡A¥i¯à³y¦¨¶}ÀÉÅܺCªº°ÝÃD¡C
§@ªÌ:
HSIEN6001
®É¶¡:
2013-7-1 15:35
¦^´_
11#
stillfish00
QueryTable ¦n¼Ëª¾¹DC! ..^_^..
¤j²¤À³¥Î´XºØ¤è¦¡±Æ°£:
(1)
Public Sub §R°£¥»¬¡¶Ã¯¤¤©Ò¦³ªº©w¸q¦WºÙ()
For Each c In ActiveWorkbook.Names
c.Delete
Next
End Sub
*****************
(2) ¤U¸ü¸ê®Æ to ·s¼W¬¡¶Ã¯
*****************
(3)
·s¼W"¼È®É"¤u§@ªí
¥Î§¹Delete¸Ó¤u§@ªí
¤£¯d³s½u QueryTable
*****************
(4)
Dim QueryTable_Name As String
.
.
.
QueryTable_Name = .Name
.
.Names(QueryTable_Name).Delete
---------------------
¤£ª¾¹D¬°¬Æ»ò,§Ú C:\
¦@¦³¨âÓProgram Files
Program Files
Program Files (x86)
Sub QuitIE()
Dim oShellWindows As Object, obj As Object
Set oShellWindows = CreateObject("Shell.Application").Windows
FullName = "C:\Program Files (x86)\Internet Explorer\iexplore.exe"
If oShellWindows.Count > 0 Then
For Each obj In oShellWindows
If StrComp(Right(obj.FullName, 12), "iexplore.exe", vbTextCompare) = 0 Then obj.Quit
Next
End If
End Sub
½Æ»s¥N½X
³o¼Ë¹ï¤£¹ï?!!
(¹ï¤£°_!!Yµ{§Ç«Ü¦n¯º,§Ú¬O¯uªº¥~¦æ,¦³®ÉÀH«K®M¥Î¨Óªº)
§O¨£©Ç!! ..^__^..
§@ªÌ:
HSIEN6001
®É¶¡:
2013-7-1 15:41
§Ñ¤F!!
(ªþ¹Ï)
¤U¸ü®É,¤u§@ªí¤Ï¶Âªº²{¶H¬O³o¼Ë
[attach]15345[/attach]
¦³¨S¦³¤Hª¾¹D³oÓ°ÝÃDªº²£¥Íì¦]
§@ªÌ:
yuch8663
®É¶¡:
2013-7-1 17:24
ÁÂÁªO¤W¦U¦ì¤j¤j¸ÑÃD¡A·|¦bµZ¿i°Ñ¸Ô¡C
Åwªï¥úÁ{ ³Â»¶®a±Ú°Q½×ª©ª© (http://forum.twbts.com/)