[已解决]请求把两段VBA代码糅合在一个工作表里使用

cqzcs

首先请问的是外部导入网上数据能否实现在一个工作表里同时使用2个网上链接或多个网上链接？如果可以，那么www.17500.cn/getData/3d.TXT和http://www.17500.cn/getData/p5.TXT如何实现在一个工作表里工作？目前我可以实现的是分别在2个单独的工作表里能够工作，我想使其能在一个工作表里一次性导入上面两个链接的数据。请老师出手调整下，谢谢！！！
附代码，一：
Private Sub CommandButton1_Click()

Range("A3:Q8000").Clear

k3dshijihao = "http://www.17500.cn/getData/3d.TXT"

d3s = "WData3D_All"

    Cells(2, 1) = "开奖期号"
    Cells(2, 2) = "开奖日期"
    Cells(2, 3) = "开"
    Cells(2, 4) = "奖"
    Cells(2, 5) = "号"

    Cells(2, 6) = "试"
    Cells(2, 7) = "机"
    Cells(2, 8) = "号"

    Cells(2, 9) = "机"
    Cells(2, 10) = "球"

    Cells(2, 11) = "投注总额"

    Cells(2, 12) = "单选注数"
    Cells(2, 13) = "金额"

    Cells(2, 14) = "组三注数"
    Cells(2, 15) = "金额"

    Cells(2, 16) = "组六注数"
    Cells(2, 17) = "金额"



    cz = k3dshijihao: czmc = d3s


     With ActiveSheet.QueryTables.Add(Connection:= _
         "TEXT;" & cz, Destination:=Range("A3"))
        .Name = czmc
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
Range("A" & (Application.Count(Range("a1:a8000")))).Select
End





End Sub

附代码，二：
Private Sub CommandButton1_Click()

Range("Z3:AP8000").Clear

k3dshijihao = "http://www.17500.cn/getData/p5.TXT"

d3s = "WData3D_All"

    Cells(2, 1) = "开奖期号"
    Cells(2, 2) = "开奖日期"
    Cells(2, 3) = "开"
    Cells(2, 4) = "奖"
    Cells(2, 5) = "号"
    Cells(2, 6) = " "
    Cells(2, 7) = " "
    Cells(2, 8) = "投注总额"

    Cells(2, 9) = "中奖注数"
    Cells(2, 10) = "单注奖金"

    cz = k3dshijihao: czmc = d3s


     With ActiveSheet.QueryTables.Add(Connection:= _
         "TEXT;" & cz, Destination:=Range("Z3"))
        .Name = czmc
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
Range("A" & (Application.Count(Range("Z1:Z8000")))).Select
End





End Sub

最佳答案

月排行榜 / 总排行榜

hwc2ycy

2013-3-15 16:15

1. Sub tset()
   
2.     k3dshijihao = "http://www.17500.cn/getData/3d.TXT"
   
3. 
   
4.     d3s = "WData3D_All"
   
5. 
   
6.     Cells(2, 1) = "开奖期号"
   
7.     Cells(2, 2) = "开奖日期"
   
8.     Cells(2, 3) = "开"
   
9.     Cells(2, 4) = "奖"
   
10.     Cells(2, 5) = "号"
    
11. 
    
12.     Cells(2, 6) = "试"
    
13.     Cells(2, 7) = "机"
    
14.     Cells(2, 8) = "号"
    
15. 
    
16.     Cells(2, 9) = "机"
    
17.     Cells(2, 10) = "球"
    
18. 
    
19.     Cells(2, 11) = "投注总额"
    
20. 
    
21.     Cells(2, 12) = "单选注数"
    
22.     Cells(2, 13) = "金额"
    
23. 
    
24.     Cells(2, 14) = "组三注数"
    
25.     Cells(2, 15) = "金额"
    
26. 
    
27.     Cells(2, 16) = "组六注数"
    
28.     Cells(2, 17) = "金额"
    
29. 
    
30.     cz = k3dshijihao: czmc = d3s
    
31. 
    
32.     With ActiveSheet.QueryTables.Add(Connection:= _
    
33.                                      "TEXT;" & cz, Destination:=Range("A3"))
    
34.         .Name = czmc
    
35.         .FieldNames = True
    
36.         .RowNumbers = False
    
37.         .FillAdjacentFormulas = False
    
38.         .PreserveFormatting = True
    
39.         .RefreshOnFileOpen = False
    
40.         .RefreshStyle = xlInsertDeleteCells
    
41.         .SavePassword = False
    
42.         .SaveData = True
    
43.         .AdjustColumnWidth = True
    
44.         .RefreshPeriod = 0
    
45.         .TextFilePromptOnRefresh = False
    
46.         .TextFilePlatform = xlWindows
    
47.         .TextFileStartRow = 1
    
48.         .TextFileParseType = xlDelimited
    
49.         .TextFileTextQualifier = xlTextQualifierDoubleQuote
    
50.         .TextFileConsecutiveDelimiter = True
    
51.         .TextFileTabDelimiter = False
    
52.         .TextFileSemicolonDelimiter = False
    
53.         .TextFileCommaDelimiter = False
    
54.         .TextFileSpaceDelimiter = True
    
55.         .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    
56.         .TextFileTrailingMinusNumbers = True
    
57.         .Refresh BackgroundQuery:=False
    
58.     End With
    
59. 
    
60.     Range("Z3:AP8000").Clear
    
61. 
    
62.     k3dshijihao = "http://www.17500.cn/getData/p5.TXT"
    
63. 
    
64.     d3s = "WData3D_All"
    
65. 
    
66.     Cells(2, 1) = "开奖期号"
    
67.     Cells(2, 2) = "开奖日期"
    
68.     Cells(2, 3) = "开"
    
69.     Cells(2, 4) = "奖"
    
70.     Cells(2, 5) = "号"
    
71.     Cells(2, 6) = " "
    
72.     Cells(2, 7) = " "
    
73.     Cells(2, 8) = "投注总额"
    
74. 
    
75.     Cells(2, 9) = "中奖注数"
    
76.     Cells(2, 10) = "单注奖金"
    
77. 
    
78.     cz = k3dshijihao: czmc = d3s
    
79. 
    
80. 
    
81.     With ActiveSheet.QueryTables.Add(Connection:= _
    
82.                                      "TEXT;" & cz, Destination:=Range("Z3"))
    
83.         .Name = czmc
    
84.         .FieldNames = True
    
85.         .RowNumbers = False
    
86.         .FillAdjacentFormulas = False
    
87.         .PreserveFormatting = True
    
88.         .RefreshOnFileOpen = False
    
89.         .RefreshStyle = xlInsertDeleteCells
    
90.         .SavePassword = False
    
91.         .SaveData = True
    
92.         .AdjustColumnWidth = True
    
93.         .RefreshPeriod = 0
    
94.         .TextFilePromptOnRefresh = False
    
95.         .TextFilePlatform = xlWindows
    
96.         .TextFileStartRow = 1
    
97.         .TextFileParseType = xlDelimited
    
98.         .TextFileTextQualifier = xlTextQualifierDoubleQuote
    
99.         .TextFileConsecutiveDelimiter = True
    
100.         .TextFileTabDelimiter = False
     
101.         .TextFileSemicolonDelimiter = False
     
102.         .TextFileCommaDelimiter = False
     
103.         .TextFileSpaceDelimiter = True
     
104.         .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 1, 1, 1)
     
105.         .TextFileTrailingMinusNumbers = True
     
106.         .Refresh BackgroundQuery:=False
     
107.     End With
     
108. End Sub
     

复制代码

跳转到最佳答案楼层

hwc2ycy

两个过程都无误的话，你就先调用就成了。

跟写入几个工作表无关，只要注意写入的区域。

hwc2ycy

1. Sub tset()
   
2.     k3dshijihao = "http://www.17500.cn/getData/3d.TXT"
   
3. 
   
4.     d3s = "WData3D_All"
   
5. 
   
6.     Cells(2, 1) = "开奖期号"
   
7.     Cells(2, 2) = "开奖日期"
   
8.     Cells(2, 3) = "开"
   
9.     Cells(2, 4) = "奖"
   
10.     Cells(2, 5) = "号"
    
11. 
    
12.     Cells(2, 6) = "试"
    
13.     Cells(2, 7) = "机"
    
14.     Cells(2, 8) = "号"
    
15. 
    
16.     Cells(2, 9) = "机"
    
17.     Cells(2, 10) = "球"
    
18. 
    
19.     Cells(2, 11) = "投注总额"
    
20. 
    
21.     Cells(2, 12) = "单选注数"
    
22.     Cells(2, 13) = "金额"
    
23. 
    
24.     Cells(2, 14) = "组三注数"
    
25.     Cells(2, 15) = "金额"
    
26. 
    
27.     Cells(2, 16) = "组六注数"
    
28.     Cells(2, 17) = "金额"
    
29. 
    
30.     cz = k3dshijihao: czmc = d3s
    
31. 
    
32.     With ActiveSheet.QueryTables.Add(Connection:= _
    
33.                                      "TEXT;" & cz, Destination:=Range("A3"))
    
34.         .Name = czmc
    
35.         .FieldNames = True
    
36.         .RowNumbers = False
    
37.         .FillAdjacentFormulas = False
    
38.         .PreserveFormatting = True
    
39.         .RefreshOnFileOpen = False
    
40.         .RefreshStyle = xlInsertDeleteCells
    
41.         .SavePassword = False
    
42.         .SaveData = True
    
43.         .AdjustColumnWidth = True
    
44.         .RefreshPeriod = 0
    
45.         .TextFilePromptOnRefresh = False
    
46.         .TextFilePlatform = xlWindows
    
47.         .TextFileStartRow = 1
    
48.         .TextFileParseType = xlDelimited
    
49.         .TextFileTextQualifier = xlTextQualifierDoubleQuote
    
50.         .TextFileConsecutiveDelimiter = True
    
51.         .TextFileTabDelimiter = False
    
52.         .TextFileSemicolonDelimiter = False
    
53.         .TextFileCommaDelimiter = False
    
54.         .TextFileSpaceDelimiter = True
    
55.         .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    
56.         .TextFileTrailingMinusNumbers = True
    
57.         .Refresh BackgroundQuery:=False
    
58.     End With
    
59. 
    
60.     Range("Z3:AP8000").Clear
    
61. 
    
62.     k3dshijihao = "http://www.17500.cn/getData/p5.TXT"
    
63. 
    
64.     d3s = "WData3D_All"
    
65. 
    
66.     Cells(2, 1) = "开奖期号"
    
67.     Cells(2, 2) = "开奖日期"
    
68.     Cells(2, 3) = "开"
    
69.     Cells(2, 4) = "奖"
    
70.     Cells(2, 5) = "号"
    
71.     Cells(2, 6) = " "
    
72.     Cells(2, 7) = " "
    
73.     Cells(2, 8) = "投注总额"
    
74. 
    
75.     Cells(2, 9) = "中奖注数"
    
76.     Cells(2, 10) = "单注奖金"
    
77. 
    
78.     cz = k3dshijihao: czmc = d3s
    
79. 
    
80. 
    
81.     With ActiveSheet.QueryTables.Add(Connection:= _
    
82.                                      "TEXT;" & cz, Destination:=Range("Z3"))
    
83.         .Name = czmc
    
84.         .FieldNames = True
    
85.         .RowNumbers = False
    
86.         .FillAdjacentFormulas = False
    
87.         .PreserveFormatting = True
    
88.         .RefreshOnFileOpen = False
    
89.         .RefreshStyle = xlInsertDeleteCells
    
90.         .SavePassword = False
    
91.         .SaveData = True
    
92.         .AdjustColumnWidth = True
    
93.         .RefreshPeriod = 0
    
94.         .TextFilePromptOnRefresh = False
    
95.         .TextFilePlatform = xlWindows
    
96.         .TextFileStartRow = 1
    
97.         .TextFileParseType = xlDelimited
    
98.         .TextFileTextQualifier = xlTextQualifierDoubleQuote
    
99.         .TextFileConsecutiveDelimiter = True
    
100.         .TextFileTabDelimiter = False
     
101.         .TextFileSemicolonDelimiter = False
     
102.         .TextFileCommaDelimiter = False
     
103.         .TextFileSpaceDelimiter = True
     
104.         .TextFileColumnDataTypes = Array(1, 2, 1, 1, 1, 1, 1, 1, 1, 1)
     
105.         .TextFileTrailingMinusNumbers = True
     
106.         .Refresh BackgroundQuery:=False
     
107.     End With
     
108. End Sub
     

复制代码