We help IT Professionals succeed at work.
Get Started

VBA to set-up multiple web query

909 Views
Last Modified: 2008-09-17
I have a sheet with several columns, and around 200 rows.  I need to setup a web query for each name/url (see below)
I want to return each query on a new sheet, renamed to the corresponding name in column D
Additionaly I'd like each sheet to show the corresponding Row that it was made from to now show in Row 1 of the new sheet.

I am concerned that this may be troublesome, as the web query shows 5 posible tables to return.  I want only two of those tables, specifically the 3rd and 4th ones.  

Example:
Here's what my List sheet basically looks like:
Row 2 shows my folmulas, and 3 shows what the results would display as.

Name: 'List!'
   A              B        C      D                                          E                                            F
1 =now()-1 <<format as YYYY-MM-DD
2 Last         First   ID      Full                                       Adjusted ID                            URL
3 Smith       Joe    123    =concatenate(B3, " ", A3)    =TEXT(C3,REPT("0",6))           =concatenate("www.",D3,E3,A$1$,".com ")
4 Eves        Jane  4563  Jane Eves                             0004563                                 www.JaneEves0045632008-09-17.com
.......

So I want new sheets for each name..(there may be duplicate names, which should be skipped)
The new sheet should look like this:

Name: 'Joe Smith!'
   A                 B              C               D                E                F              G               H               I                J
1  ='List!'A3   ='List!'B3  ='List!'C3   ='List!'D3   ='List!'E3    ='List!'F3
2
3 Web query (table 3)                                                                                                           Web query (table 4)
4 results....    results.... results....   results....   results....    results....  results....                   results....  results....  
5 results....    results.... results....   results....   results....    results....  results....                   results....  results....
6 results....    results.... results....   results....   results....    results....  results....                   ....
7 .....

I don't have much on this, here's code that webtubbs provided to show me how to create a new sheet for each name in the list, also, this will only create one sheet for each name, which is what I want, it prevents duplicate sheets if a name appears twice in my list:

BTW, I've looked into into getting direct access to the Oracle database where the data is stored...long story, basically I'd need to wait for months for another team to write the SQL, and design the reports I need.   Despite the fact I could do it myself in a couple days if they'd even give me read-only access....sometimes I wonder if this company knows what it is doing....

Thanks eveyone!

Sub AddSheets()
 
     Dim rngSheetNames As Range
     Set rngSheetNames = Range("C7:C10")
     Dim cell As Range, ws As Worksheet
     Application.ScreenUpdating = False
     Application.DisplayAlerts = False
     For Each cell In rngSheetNames
          Set ws = Worksheets.Add
          On Error Resume Next
          ws.Name = cell
          If Err <> 0 Then
              ws.Delete
          End If
          On Error GoTo 0
     Next
     Application.DisplayAlerts = True
     Application.ScreenUpdating = True
 
End Sub

Open in new window

Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE