• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create Array: Without Knowing the Length

AAP

AAP

Member
Hi,
How do I create an array without knowing beforehand its length?
I am uploading a file in which I am fetching data from a website but I don't know how many rows it would require because website changes data everyday. If I add more http links for various products then it would be more difficult to know beforehand how many I would require. Take a look into my file and kindly advice the optimal way.
Many thanks
 

Attachments

  • Array_Looping.xlsb
    310.4 KB · Views: 4

Hi !

Within the webpage, number of rows is well known !
So once you get the number you need, just use in code Redim statement …
​
 
Marc L said:
Hi !

Within the webpage, number of rows is well known !
So once you get the number you need, just use in code Redim statement …
​
Click to expand...
Hi,
I cannot see the number of rows in the HTML, can you help me reading?
 

If data are in an HTML table, see within its rows object …

Set a variable object on the table and explore in Locals VBE window
with a break point in code or within step by step mode (via F8 key).
​
 
Marc L said:
If data are in an HTML table, see within its rows object …

Set a variable object on the table and explore in Locals VBE window
with a break point in code or within step by step mode (via F8 key).
​
Click to expand...

Thanks, but not all HTML comes in table. Then how do I check?
 
Hi Amit ,

I am not able to understand why you need to know the number of items on the page ; can you clarify ?

I put in 2 links and the code lists all the items in both links one after the other correctly.

I think the bigger problem you need to solve is to know how many pages are available in each link , and access the pages one by one , since this depends on the default selection of number of items per page ; this is 12 by default , and depending on the total number of items available , the number of pages will be decided.

Narayan
 
NARAYANK991 said:
Hi Amit ,

I am not able to understand why you need to know the number of items on the page ; can you clarify ?

I put in 2 links and the code lists all the items in both links one after the other correctly.

I think the bigger problem you need to solve is to know how many pages are available in each link , and access the pages one by one , since this depends on the default selection of number of items per page ; this is 12 by default , and depending on the total number of items available , the number of pages will be decided.

Narayan
Click to expand...

Hi Narayan,

As you will see the page shows by default 12 items but it has loaded maximum items on the webpage. The reason, because I added Size=384 at the end of the URL which gives me maximum possible item on a single page. This way I save time to go through pages by pages. Now Problem for me to know how many items have appeared on the webpage so I capture their input into the arrays instead of interacting between excel and webpage html document.
 
Hello Friends,

I have figured out a solution to my problem by following chunk of advices from all Guru's (Shrivallabha,Narayan and Marc). I really really thankful to you :)

Best/Amit
 
Hi Amit ,

Thanks for the clarification.

I am still not able to understand why you would want to output to an array instead of capturing the data in your worksheet.

What do you want to do after you have captured all of the web data in an array ?

Narayan
 
NARAYANK991 said:
Hi Amit ,

Thanks for the clarification.

I am still not able to understand why you would want to output to an array instead of capturing the data in your worksheet.

What do you want to do after you have captured all of the web data in an array ?

Narayan
Click to expand...

Please correct me if I am wrong. I recently learned working in arrays through my previous post. I also learned that if we have big data and we need to do calculations on that data it is better to do everything without interacting with the worksheet to speed up the process.
Basically, I go through approx 50 webpages in a go, capture the data, do some string manupulations. I used to do this by going through each and every cell as you can see in the uploaded book and then do the string manupulation.
With my new knowledge of array, I capture the data right into arrays, do the string manupulations and then paste back into excel.
The whole reason to save some time.

Please advice.
 
A help again, I created a variable Long [a] which counts and extends as the range extends. But when I use preserve data it throws error. Is my preserve data statement is wrong?
Counter variable is for the first loop i.e counter = 1 and Else for other loops .

Code: 
If counter = 1 Then
ReDim Data(1 To a, 1 To 10) As Variant
Else
ReDim Preserve Data(1 To a, 1 To 10) As Variant
End If
 
Hi Amit ,

If I understand your requirement , the various stages are :

1. Access the website

2. Retrieve data into Excel

3. Manipulate the data

4. Write the data into the worksheet.

You need to see where the maximum time is taken. Assuming that it is in step 3 , I don't think there will be any overhead in doing the following :

1. Access the website

2. Retrieve data into the worksheet

3. Transfer data into array

4. Manipulate the data using the array

5. Write the array data back into the worksheet

Why don't you try and see ?

Narayan
 
NARAYANK991 said:
Hi Amit ,

If I understand your requirement , the various stages are :

1. Access the website

2. Retrieve data into Excel

3. Manipulate the data

4. Write the data into the worksheet.

You need to see where the maximum time is taken. Assuming that it is in step 3 , I don't think there will be any overhead in doing the following :

1. Access the website

2. Retrieve data into the worksheet

3. Transfer data into array

4. Manipulate the data using the array

5. Write the array data back into the worksheeit

Why don't you try and see ?

Narayan
Click to expand...

Hi Narayan,
I have not tested yet but I believe step 2 that is I most concerned about.
If you look into the code of my uploaded worksheet and suggest any idea for step 2. For step 3 I did what you suggested.
Many Thanks
 
Hi Amit ,

My point is I may be concerned about something ; do the facts suggest that my concern is warranted ?

Have you completed your application using any one method and seen how much time it takes to run from start to finish ?

Narayan
 
Before to remote Internet Explorer as it's the slowest way,
if data are in an HTML table, try QueryTable (Data, From Web) …

Fastest than these is to reproduce the http request use by webbrowser !

Any method needs observation, a well understanding of webpage
(HTML and object model) before to scrap any data !

With a link and a crystal clear explanation of need and issue …

Just to answer to initial post, two ways :
• create a very big array to load all data
• create an array for known rows for each webpage …

With a respect of HTML & object model there is a way to directly
copy web data without the need of an array (clipboardData).
​
 
You must log in or register to reply here.
Back
Top