Excel VBA 问题

想请教一下....小弟写了一个Macro, 用for-next statement 去上网search一啲野....

大概系用web-query去import一页data, 再用vlookup做一啲calculations, 如见冇用就delete rows, 再import过新一页, 一有啱用的就自动停.....

但loop咗>1000次之后, 佢越行越慢, 个file size 由300kb 变咗2mb, 个file经开关及运行一段时间,到最后更接近要hang....

请问有冇师兄知如何解决呢? tks

[ 本帖最后由 feelingbetter 於 2014-7-14 05:47 PM 编辑 ]

Loop 入面有无 set object?

如果有 next 之前要 set 番做 Nothing.

请问set object的意思是?


其实个code如下(web address因工作关系略去), 请问是哪里出问题呢?tks

****************************
Dim Webbaddress As String
Dim Begindate As String
Dim Enddate As String
Dim Issuenumber As String
Dim Issuecounter As Double
Dim Endissuenumber As Double

Begindate = Range("$F$4")
Enddate = Range("$F$5")
Issuenumber = Range("$F$6")
Endissuenumber = Range("$F$7")
For Issuecounter = Issuenumber To Endissuenumber

ActiveSheet.Range("27:300").Clear

Webbaddress = "************address removed************" & Begindate & "&d=" & Enddate & "&sort=chngdn&issue=" & Issuecounter

Range("$F$11") = Issuecounter

With ActiveSheet.QueryTables.Add(Connection:= _
Webbaddress _
, Destination:=Range("A27:M27"))
.Name = "*******address removed**********"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3,4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Range("E20").Select
'Exit procedure if criteria satisfied
If Range("$P$26").Value > 5 Then

Exit For

End If



Next Issuecounter
****************************

For Issuecounter = Issuenumber To Endissuenumber

呢句 d data type 错哂, 又 string 又 double... 虽然 vba 会同你转, 但都唔好咁。


With ActiveSheet.QueryTables.Add(Connection:= _
Webbaddress _
, Destination:=Range("A27:M27"))

呢句野点解 output 永远系 A27:M27 ?
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3,4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False

呢渣野应该几浪费资源, 改改佢试下

[ 本帖最后由 烟民贱过食屎9. 於 2014-7-16 12:13 AM 编辑 ]

运用refresh, 替代add

With Workbooks.Worksheets.QueryTables(1)
.Connection = _
"URL;address removed"
.WebTables = "3,4"
.Refresh BackgroundQuery:=False
End With

integer 才正确对不对? TKS

output destination冇特别意思, 因delete后有empty space才重用位置


btw...我谂我冇set object.....但如何才能set variables to nothing/kill variables去relieve memory呢?

yfly480 系正解, 你个 code 不断 create 新 query table, 无 free 番之前唔用个 d.
应考虑 reuse 同一个 query table, 或开新一个之前清左旧既.

明白, 但请问师兄可写出reuse的code吗? 因小弟初学不太明白yfly480师兄的code......tks

以上既提示应该足够你自已解决问题.

macro有录制新巨集功能, 自己玩下, 睇下create既code就知点写

其实个querytable是我用macro录出来的....所以不太明里面内容...

至於上面师兄指用refresh代替add的code, 我modify了一下放入去但唔work(出error msg), 看书又只有很少描述refresh的用法且不太明.....

有师兄可指点一下吗?tks

我试用了以下的code, 但有run 到有result 后所import的资料却show不了....why?


With ActiveSheet.QueryTables(1)
.Connection = Webbaddress
.Destination = Range("A27:M27")
.Name = "chldchg.asp?d1=2014-06-30&d=2014-07-09&sort=chngdn&issue=6409"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3,4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


定抑或应用add, 完了再用另一set code去delete all web query?
http://www.mrexcel.com/forum/exc ... ries-worksheet.html

Dim QTcount As Integer
QTcount = ActiveSheet.QueryTables.Count
If QTcount > 0 Then
For QTIndex = 1 To QTcount
ActiveSheet.QueryTables(QTIndex).Delete
Next
End If

用了以上的code好像ok了....谢谢各位~

.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3,4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False


Are these code unneccessary? will the VBA run faster without them?

Glad to hear the problem is solved.
Good for u.

For your previous post, the last sentence is very important and cannot be removed.
Other sentences are pretty optional but shouldn't have materialized performance impact.

Thanks a lot!

On the other hand, if I want to keep the macro running (while recording the issuenumbers with positive search results) instead of exiting the sub whenever a result shows, how can i achieve that?

I thought of using some cells (variable value=issuenumbers) to hold the result records, but as the querytable gets cleared up for the analysis of the next issuenumber, the variable value would get cleared up as well.....is there any method to get around this?