[發問] 請問網址可以用變數代替嗎？

cji3cj6xu6

請問網址可以用變數代替嗎？
可以的話，不曉得語法該如何寫。

謝謝～

stillfish00

回復 1# cji3cj6xu6

1. Dim 變數 as string
   
2. 變數 = "http://forum.twbts.com/"

複製代碼

cji3cj6xu6

Dear stillfish00大大，

Thanks

cji3cj6xu6

以下，跑了之後會發生 .Refresh BackgroundQuery:=False 錯誤，但若是直接將網址放上則ok，請問要如何修改，
謝謝～

Dim WebAddress as string
WebAddress = "http://tw.stock.yahoo.com/q/q?s=1101"

Range("a2").Select
    'With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://tw.stock.yahoo.com/q/q?s=" & [a1] & "", Destination:=Selection) '新增查詢
     With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;WebAddress", Destination:=Selection) '新增查詢
     
      
         
        .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 = "6"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
        .Name = .ResultRange.Cells(3, 1)
    End With

GBKEE

回復 4# cji3cj6xu6

1. Option Explicit
   
2. Sub EX()
   
3.     Dim WebAddress As String
   
4.     WebAddress = "http://tw.stock.yahoo.com/q/q?s=1101"
   
5.     Range("a2").Select
   
6.      With ActiveSheet.QueryTables.Add(Connection:= _
   
7.         "URL;" & WebAddress, Destination:=Selection) '新增查詢
   
8.         .FieldNames = True
   
9.         .RowNumbers = False
   
10.         .FillAdjacentFormulas = False
    
11.         .PreserveFormatting = True
    
12.         .RefreshOnFileOpen = False
    
13.         .BackgroundQuery = True
    
14.         .RefreshStyle = xlInsertDeleteCells
    
15.         .SavePassword = False
    
16.         .SaveData = True
    
17.         .AdjustColumnWidth = True
    
18.         .RefreshPeriod = 0
    
19.         .WebSelectionType = xlSpecifiedTables
    
20.         .WebFormatting = xlWebFormattingNone
    
21.         .WebTables = "6"
    
22.         .WebPreFormattedTextToColumns = True
    
23.         .WebConsecutiveDelimitersAsOne = True
    
24.         .WebSingleBlockTextImport = False
    
25.         .WebDisableDateRecognition = False
    
26.         .WebDisableRedirections = False
    
27.         .Refresh BackgroundQuery:=False
    
28.         .Name = .ResultRange.Cells(3, 1)
    
29.     End With
    
30. End Sub

複製代碼

cji3cj6xu6

可以 work 了，謝謝G大～