none
Import specific multiple data from web sources into Excel at once RRS feed

  • Question

  • Hi!

    I have this script for Visual Basic made from <g class="gr_ gr_39 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="39" id="39">Asadulla</g> Javed (whom I thank for) which consents me to import data from thousands of web sources at once, just changing the values of "i".

    Sub SIF()

        Dim sConnect As String
        Dim rng As Range
        Dim <g class="gr_ gr_41 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" data-gr-id="41" id="41">i</g> As Long
        Dim <g class="gr_ gr_45 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="45" id="45">oQT</g> As QueryTable
        
        sConnect = "URL;" & "http://makoo.eu/tools/userInfo/index.php?user_id="
        
        Worksheets.Add
        
        
        
        For i = 1 To 1000
            Set rng = Range("a1").Offset(WorksheetFunction.CountA(Range("a:a")) + 1)
            
            Set oQT = ActiveSheet.QueryTables.Add(sConnect & i, rng)
            
            With <g class="gr_ gr_46 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="46" id="46">oQT</g>
                 .WebSelectionType = xlEntirePage
                 .Refresh False
            End With
            
            <g class="gr_ gr_44 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="44" id="44">oQT</g>.Delete
               
        Next i

    End Sub

    Now I'd ask how can I do if I would download data from specific "sources", for example, if, the "i" in the script is from 1 to 1000, but I would download only the sources for i = 24, 67, 145, 739, 891, etc, running the script a single time.

    It would be good if, at the level of codes, I can easily change the script depending on the data I need to import.

    Thank you in advance.

    Andrea.


    Tuesday, October 10, 2017 2:25 PM

Answers

  • Hi Andrea,

    >>For Each control variable must be Variant or Object

    Number in the array is Integer and 'i' was defined as Long type.

    You could change another parameter for For Each, like

    For Each j in arr

    Or you could change the "Dim i as Long" to "Dim i as Integer"

    >>One more thing, is it ok if I put thousands and thousands of values in the array? Is there a limit?

    The length of every dimension of an array is limited to the maximum value of the Integer data type, which is (2 ^ 31) - 1. However, the total size of an array is also limited by the memory available on your system.

    You could refer to below link for more information.

    https://msdn.microsoft.com/en-us/library/b388cb5s(v=vs.90).aspx

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 11, 2017 7:43 AM

All replies

  • Sorry for the weird codes, but they don't disappear even if I correct them.

    I'll try to paste an image of the script


    Tuesday, October 10, 2017 2:28 PM
  • Hi Andrea,

    You could add the source index you want into an array and then iterate through the array so it will only download sources you specified.

    Here is the example.

    arr = Array(24, 67, 145, 739, 891)
    
    For Each i In arr
    
            Set Rng = Range("a1").Offset(WorksheetFunction.CountA(Range("a:a")) + 1)
    
            Set oQT = ActiveSheet.QueryTables.Add(sConnect & i, Rng)
    
            With oQT
    
                 .WebSelectionType = xlEntirePage
    
                 .Refresh False
    
            End With
    
            oQT.Delete
    
    Next i

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 11, 2017 3:56 AM
  • Thank you!

    I tried to substitute the part in the script, but it gives me an error, saying:

    "For Each control variable must be Variant or Object"

    

    One more thing, is it ok if I put thousands and thousands of values in the array? Is there a limit?

    Thank you in advance!

    Andrea.

    Wednesday, October 11, 2017 7:14 AM
  • Hi Andrea,

    >>For Each control variable must be Variant or Object

    Number in the array is Integer and 'i' was defined as Long type.

    You could change another parameter for For Each, like

    For Each j in arr

    Or you could change the "Dim i as Long" to "Dim i as Integer"

    >>One more thing, is it ok if I put thousands and thousands of values in the array? Is there a limit?

    The length of every dimension of an array is limited to the maximum value of the Integer data type, which is (2 ^ 31) - 1. However, the total size of an array is also limited by the memory available on your system.

    You could refer to below link for more information.

    https://msdn.microsoft.com/en-us/library/b388cb5s(v=vs.90).aspx

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 11, 2017 7:43 AM
  • I tried to change <g class="gr_ gr_82 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" data-gr-id="82" id="82">i</g> from Long to Integer, but it gave me the same error.

    Now I've changed it to Variant and it works!

    Thank you very much!

    Andrea.

    Wednesday, October 11, 2017 8:49 AM