Start Free Trial

asked on

Exccel 2010 - How to run multiple web queries in succession and record data in single table?

I'm attaching a couple of example docs for this one.

The first doc(URL-Builder.xlsm) shows a list of sample URL's that would be submitted via "data from Web" query using a fictional website

The second doc(Login.xlsm) shows the login script I am using (same fictional site)

Here is a URL of the method I was originally going to use to pull the data. I have a couple problems with this I'll explain later. http://www.familycomputerclub.com/scrpae-pull-data-from-websites-into-excel.html

Ok. So, as you can see, I have an autogenerated list of URLs/queries that dynamically changes when I change the Initial Date or Interval(Doc1). I have a way to log into the site that these URL's/queries will be submitted too. And I have one example of how to submit
a list of URL's to a website and return data to the spreadsheet. Now, my problem with the example I provided is that this person puts each returned dataset on a separate worksheet. I would like it all on one worksheet in one continuous table if possible. Second, the data that is returned includes headers/titles. My data would also include this information but it takes up two rows for me. I would need to remove that. Maybe that is done with a "cleanup" script after all the data is imported.

**I do not want to put the data on separate worksheets because I could have close to 200 worksheets via the example method all generically named. In addition I would still need to compile the data into single worksheet for it to be usable for my purposes.
URL-Builder.xlsm
Login.xlsm

A related Previously Asked Question, for reference:

"Excel 2010 - delete/add row based on cell value"

[ https://www.experts-exchange.com/questions/28142299/Excel-2010-delete-add-row-based-on-cell-value.html ]

This is a little difficult to code when we just have a dummy URL. The problem is I don't know if the real web site will allow the Excel query method without trying. Also, you have code to check if you are logged in, but the is using the IE browser. Some sites will treat the Excel query as a different entity and will not consider Excel logged in even if IE shows you are. You will have to test that part. For now, I have combined the two sheets into one as the code will work better that way. This should run each query on the main tab, put the data in the second tab until all queries are run. Give it a try and let us know.

-Bear
URL-Builder-2.xlsm

Bear:
I had the same concerns regarding the lack of the actual URL/site being used. In the related thread (I quoted above), futr_vision advises that the actual URL cannot be provided.

ASKER

True. I can not provide the actual URL. But I can assure you that the login and the queries work. I've been manually grabbing this data for years now and I am now trying to automate the process. Ill just drop the correct URLs in your code to test.

ASKER

Hmm. Error. See attached screengrab. I'm running Excel 2010 64-bit.
32Bit-Error.PNG

ASKER

Looks like a problem with the "Sleep 50" part of the code.

Sub or function not defined

Hi,

I have looked at Bear's Visual Basic for Applications code.

Within the "Q_28144042" code module (taken from the module I named in one of your previous questions!) you will see this line at the top (just after the few lines of comments):

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Open in new window

I believe you need to change this line (within a 64-bit environment) to read as follows:

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Open in new window

BFN,

fp.

ASKER

Thanks. I made the change and get a new error. See attached.
32Bit-Error2.PNG

Sorry, I have not used the 64-bit version of Microsoft Excel 2010 or 2013. After a quick search via Google.com, I think you should be able to resolve the second error message using:

Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Open in new window

I don't know if Bear has access to the 64-bit version of 2010 to assist futher.
I do not, unfortunately.

I do not have 64 bit Excel. I added the sleep to make the code a little better, but it is not a major part. At this stage, I would remove the declare statement and remove the sleep 50 line. It just is nice to let the code pause for a partial second before checking the status of the browser window, but not required. Remove those lines and see what you get.

-Bear

ASKER

Ok. So fanpages' fix took care of the error. Now when it runs(with the correct URL's in place.) I get IE to launch and go to the login page but it stops there. When I close the browser window I get the attached error.

Also, when I update the interval date in the worksheet I get a chime as if there is an error but i don't see any. I never got a chime before.

If fanpages fix worked, then add this code:

    ' Wait until the browser is loaded
    Do Until objBrowser.readyState = READYSTATE_COMPLETE
        Sleep 50
    Loop

Open in new window

in right before

        objBrowser.navigate strURL

Open in new window

I missed getting that back into the new combined code.

I am not sure what the chime is coming from. Does the code run for you - i.e. does it update the spreadsheet? Error handling was suppressed in that code so it will not display a message. If it does not update the sheet, change this section:

Err_Worksheet_Change:

  On Error Resume Next
  
  Resume Exit_Worksheet_Change

Open in new window

to this

Err_Worksheet_Change:
    MsgBox "Procedure = URL Worksheet_Change" & vbNewLine & _
        "Error Number = " & Err.Number & vbNewLine & _
        "Error Message = " & Err.Description & vbNewLine, _
        vbCritical Or vbSystemModal, "VBA Module Error--Module:shtQ_28142299"

  On Error Resume Next
  
  Resume Exit_Worksheet_Change

Open in new window

That will display error messages again.

@fanpages - I really like how you have commented the code. Very nice. I am going to have to use your concept with the question's information up top in my replies. It is very nice for future reference.

ASKER

Ah. Exit_Worksheet_Change not defined(line 9 above). That should be Err_Worksheet_Change correct?

ASKER

For the SLeep 50 code it will be in two places now? Like this?

strURL = Range("C5").Value
    Set objBrowser = New InternetExplorer
    With objBrowser
        .Silent = True
        .navigate strURL
        .Visible = True
    End With
    
    ' Wait until the browser is loaded
    Do Until objBrowser.readyState = READYSTATE_COMPLETE
        Sleep 50
    Loop
    
     Do
    ' Wait till the Browser is loaded
    Loop Until oBrowser.readyState = READYSTATE_COMPLETE
    
    Set objHTMLDoc = objBrowser.document
    
    ' Check if we hit the login page.  If so, log in
    If objHTMLDoc.URL = "http://ABC.com/login" Then
        objHTMLDoc.all.email_address.Value = "username"
        objHTMLDoc.all.Password.Value = "password"
        objHTMLDoc.all.fsubmit.Click
        
        
            ' Wait until the browser is loaded
    Do Until objBrowser.readyState = READYSTATE_COMPLETE
        Sleep 50
    Loop

        ' You may not need this if the Excel Query Process works
        objBrowser.navigate strURL
    End If

Open in new window

ASKER

Still get the Error = 424 code when running the ExtractWebsiteData macro. The login screen launches in IE but nothing gets filled in or submitted. I close IE and I get that error code. The same one I posted in a previous message. I called the screengrab Error3

ASKER

Hmmm. Going back to the date change. If I reopen the workbook and change the interval I get an error. After dismissing the error the code still runs but I get date before 2010. All the way back to 2008. Attached is the error message.

Hi,

Firstly, thanks for the note about my commenting, Bear. Your own in-line commenting is far better than most I have seen too! :)

Brad (byundt) did previously contact me regarding adding a copyright statement within code submissions to the site, so tread with care, please.

Secondly, the "chime" (Beep) has always been in my code. It indicates the code has been successful (not that there has been a failure). If you recall, futr_vision, we initially used a version of the code that took much longer to run so I added the Beep so you would know all the rows had been updated.

Finally, for now, I do not see any attachments to your recent comments. Did you have trouble attaching them?

PS. I know we have gone over this already, but the fact that we do not have the actual web site address (URL) you wish to use is why issues are now present (as they could not be seen prior to the proposed code being posted by Bear).

BFN,

fp.

ASKER

Hmm. It was late. maybe I didn't attach it :) Here it is.

As for the chime I don't remember hearing it but there is a lot going on during the day so I may not have been paying attention.
Error4.PNG

ASKER

I think we may be able to ignore that error. I made an error in replacing the code from the msg above

The update seems to work fine now. I still get the 424 error and just noticed that the screengrab for that one didn't upload either. Here it is.
Error3.PNG

ASKER

Ok. New update. Looks like in the original code we had

    ' Wait until the browser is loaded
    Do Until objBrowser.readyState = READYSTATE_COMPLETE
        Sleep 50
    Loop

Open in new window

and

    Do
    ' Wait till the Browser is loaded
    Loop Until oBrowser.readyState = READYSTATE_COMPLETE

Open in new window

Looks like this is what was hanging up the login. I removed the second piece of code and it logged in and started running the update. I killed the process since it seemed like it was running awfully fast to check what was being grabbed. Looks like the menu is what it was grabbing. I've attached the file.

would it be helpful if I recorded a Macro to show you which table is being pulled?
Data-pull.xlsx

ASKER

Here is what the macro looks like. Looks like I only need to change the .Webtables value to "16"

Sub DataPull()
'
' DataPull Macro
'

'
    With ActiveSheet.QueryTables.Ad<wbr ></wbr>d(Connecti<wbr ></wbr>on:= _
        "URL;http://www.ABC.com" _
        , Destination:=Range("$A$1")<wbr ></wbr>)
        .Name = _
        "www.ABC.com"
        .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 = "16"
        .WebPreFormattedTextToColu<wbr ></wbr>mns = True
        .WebConsecutiveDelimitersA<wbr ></wbr>sOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition<wbr ></wbr> = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Open in new window

ASKER

Ok. So everything seems to run now. Changing the .WebTables value to "16" is now pulling the correct data but there are a few interesting things going on.

1. The function is skipping the first URL. It's almost as if it uses it to test if I am logged in and then once logged in it moves on to the next URL

2. The data that is pulled is one day less than the interval I indicate but i think this is an issue with how date ranges are used in my reporting system. A 15 day interval will only pull day up to but not including the final day. I'll figure this one out.

3. The data is pasted into the data tab oddly. The first query drops in correctly(columns A-P & Rows 1-812) but the second doesn't get added below that. it drops into columns Q-AF and Row 803 and contains the column titles. Query 3 starts in column AG and row 805. If go to the very end of the worksheet the column headers are listed again starting in column AW and row 1,048,576. I didn't check all the columns and rows in between thoroughly for other data.

futr_vision,

This is where not seeing the real data is very difficult. There was no way for us to know that needed to be a 16. Did this solve your problem and is the data loading correctly? If not, please post the current version of the workbook and if possible could you post what some sample data from the query? The code I added assumes that the first column will always have data. If this is not the case, then we will need to adjust the code.

Right now I am not really sure what is in the code and what is not. As I stated, if you can post the current version and what messages you are currently getting, it will help us make it work.

-Bear

futr_vision,

Sorry we cross posted there. If you could post the current version of the spreadsheet and some sample data, that would help. Thanks.

-Bear

ASKER

I have to be careful about what I give out but here is what the sample data looks like when I let the macro run through a handful of cycles. if you zoom way out you can see the data scattered throughout the worksheet. I've also attached the code for the "URL builder" and the data query.
Sample-Data-Output.xlsx
shtQ-28142299.cls
Q-28144042.bas

First off on the Interval problem. Some extra quote marks have made it into your code. In shtQ-28142299 change

Range([C5], Cells(lngLast_Row, 3)).Formula = "=""http://www.ABC.com?start_from="" & Text(A5, ""yyyy-mm-dd"") & ""&date_to="" & Text(B5, ""yyyy-mm-dd"")"""

Open in new window

To

Range([C5], Cells(lngLast_Row, 3)).Formula = "=""http://www.ABC.com?start_from="" & Text(A5, ""yyyy-mm-dd"") & ""&date_to="" & Text(B5, ""yyyy-mm-dd"")"

Open in new window

Nothing jumps out at me on the other problems. I will try and test more tonight.

-Bear

ASKER

I noticed that and fixed it. Same results.

Thanks for taking the time to check it out tonight!

OK. I made a couple minor change, but you will have to test with the actual URL. Let us know what you find.
URL-Builder-3.xlsm

ASKER

We might be getting closer. At least this data has some semblance of a pattern :)
Sample-Data-Output2.xlsx

Lets try a little debugging. Add in this:

        Debug.Print "rngDataCurrent=" & rngDataCurrent.AddressLocal & ":UsedRange=" & objDataSheet.UsedRange.AddressLocal

Open in new window

just after this:

Set rngDataCurrent = objDataSheet.Cells(objDataSheet.Rows.Count, 1).End(xlUp)

Open in new window

This will put some items in the immediate window (Cntrl-G or View >> Immediate Window). Run the code and then post the text from the immediate window (you can do a copy and paste).

ASKER

Thanks. Here is what was output.

rngDataCurrent=$A$223:UsedRange=$A$1:$HL$1794
rngDataCurrent=$A$445:UsedRange=$A$1:$IB$1794
rngDataCurrent=$A$673:UsedRange=$A$1:$IR$1794
rngDataCurrent=$A$895:UsedRange=$A$1:$JH$1794
rngDataCurrent=$A$1119:UsedRange=$A$1:$JX$1794
rngDataCurrent=$A$1358:UsedRange=$A$1:$KN$1794
rngDataCurrent=$A$1578:UsedRange=$A$1:$LD$1794
rngDataCurrent=$A$1794:UsedRange=$A$1:$LT$1794
rngDataCurrent=$A$2040:UsedRange=$A$1:$MJ$2040
rngDataCurrent=$A$2285:UsedRange=$A$1:$MZ$2285
rngDataCurrent=$A$2534:UsedRange=$A$1:$NP$2534
rngDataCurrent=$A$2803:UsedRange=$A$1:$OF$2803
rngDataCurrent=$A$3065:UsedRange=$A$1:$OV$3065
rngDataCurrent=$A$3338:UsedRange=$A$1:$PL$3338
rngDataCurrent=$A$3619:UsedRange=$A$1:$QB$3619
rngDataCurrent=$A$3886:UsedRange=$A$1:$QR$3886
rngDataCurrent=$A$4131:UsedRange=$A$1:$RH$4131
rngDataCurrent=$A$4380:UsedRange=$A$1:$RX$4380
rngDataCurrent=$A$4604:UsedRange=$A$1:$SN$4604
rngDataCurrent=$A$4833:UsedRange=$A$1:$TD$4833
rngDataCurrent=$A$5076:UsedRange=$A$1:$TT$5076
rngDataCurrent=$A$5341:UsedRange=$A$1:$UJ$5341
rngDataCurrent=$A$5576:UsedRange=$A$1:$UZ$5576
rngDataCurrent=$A$5782:UsedRange=$A$1:$VP$5782
rngDataCurrent=$A$6000:UsedRange=$A$1:$WF$6000
rngDataCurrent=$A$6236:UsedRange=$A$1:$WV$6236
rngDataCurrent=$A$6479:UsedRange=$A$1:$XL$6479
rngDataCurrent=$A$6730:UsedRange=$A$1:$YB$6730
rngDataCurrent=$A$7002:UsedRange=$A$1:$YR$7002
rngDataCurrent=$A$7319:UsedRange=$A$1:$ZH$7319
rngDataCurrent=$A$7588:UsedRange=$A$1:$ZX$7588
rngDataCurrent=$A$7903:UsedRange=$A$1:$AAN$7903
rngDataCurrent=$A$8232:UsedRange=$A$1:$ABD$8232
rngDataCurrent=$A$8593:UsedRange=$A$1:$ABT$8593

Open in new window

FYI. The number of rows of data can change from query to query. Also, the headers/titles of each column take up two rows for some of them. Not sure if either of these bits of information are important or not. Below is the HTML if you need to see it.

NOTE: There is a small chance I can get the HTML changed to a single row.

<tr	style="text-align: center; COLOR: #0076C8; BACKGROUND-COLOR: #F4FAFF; font-weight: bold">
	<td valign="top" align="left">Data1</td>
        <td valign="top" align="left">Data2</td>
        <td valign="top" align="left">Data3</td>
        <td valign="top" align="left">Data4</td>		
	<td valign="top" align="left">Data5</td>
	<td valign="top" align="left">Data6</td>
	<td valign="top" align="left">Data7</td>
	<td valign="top" align="left">Data8</td>
	<td valign="top" align="left">Data9</td>
	<td valign="top" align="left">Data10</td>
	<td valign="top" align="left">Data11</td>
	<td valign="top" align="left">Data12</td>
	<td valign="top" align="left">percent of <br /> Data13 </td>
        <td valign="top" align="left">Data14</td>
        <td valign="top" align="left">percent of <br /> Data15 </td>                             
        <td valign="top" align="left">Data16</td>
</tr>

Open in new window

I see one more thing to change.

Change:

            .Name = "WebQuery"

Open in new window

To

            .Name = "WebQuery_" & rngDataCurrent.Row

Open in new window

ASKER

Codes in place. Looks like the same deal.

rngDataCurrent=$A$223:UsedRange=$A$1:$LT$1794
rngDataCurrent=$A$445:UsedRange=$A$1:$MJ$1794
rngDataCurrent=$A$673:UsedRange=$A$1:$MZ$1794
rngDataCurrent=$A$895:UsedRange=$A$1:$NP$1794
rngDataCurrent=$A$1119:UsedRange=$A$1:$OF$1794
rngDataCurrent=$A$1358:UsedRange=$A$1:$OV$1794
rngDataCurrent=$A$1578:UsedRange=$A$1:$PL$1794
rngDataCurrent=$A$1794:UsedRange=$A$1:$QB$1794
rngDataCurrent=$A$2040:UsedRange=$A$1:$QR$2040
rngDataCurrent=$A$2285:UsedRange=$A$1:$RH$2285
rngDataCurrent=$A$2534:UsedRange=$A$1:$RX$2534
rngDataCurrent=$A$2803:UsedRange=$A$1:$SN$2803
rngDataCurrent=$A$3065:UsedRange=$A$1:$TD$3065
rngDataCurrent=$A$3338:UsedRange=$A$1:$TT$3338
rngDataCurrent=$A$3619:UsedRange=$A$1:$UJ$3619
rngDataCurrent=$A$3886:UsedRange=$A$1:$UZ$3886
rngDataCurrent=$A$4131:UsedRange=$A$1:$VP$4131
rngDataCurrent=$A$4380:UsedRange=$A$1:$WF$4380
rngDataCurrent=$A$4604:UsedRange=$A$1:$WV$4604
rngDataCurrent=$A$4833:UsedRange=$A$1:$XL$4833
rngDataCurrent=$A$5076:UsedRange=$A$1:$YB$5076
rngDataCurrent=$A$5341:UsedRange=$A$1:$YR$5341
rngDataCurrent=$A$5576:UsedRange=$A$1:$ZH$5576
rngDataCurrent=$A$5782:UsedRange=$A$1:$ZX$5782
rngDataCurrent=$A$6000:UsedRange=$A$1:$AAN$6000
rngDataCurrent=$A$6236:UsedRange=$A$1:$ABD$6236
rngDataCurrent=$A$6479:UsedRange=$A$1:$ABT$6479
rngDataCurrent=$A$6730:UsedRange=$A$1:$ACJ$6730
rngDataCurrent=$A$7002:UsedRange=$A$1:$ACZ$7002
rngDataCurrent=$A$7319:UsedRange=$A$1:$ADP$7319
rngDataCurrent=$A$7588:UsedRange=$A$1:$AEF$7588
rngDataCurrent=$A$7903:UsedRange=$A$1:$AEV$7903
rngDataCurrent=$A$8232:UsedRange=$A$1:$AFL$8232
rngDataCurrent=$A$8593:UsedRange=$A$1:$AGB$8593
rngDataCurrent=$A$8954:UsedRange=$A$1:$AGR$8954
rngDataCurrent=$A$9304:UsedRange=$A$1:$AHH$9304
rngDataCurrent=$A$9688:UsedRange=$A$1:$AHX$9688
rngDataCurrent=$A$10006:UsedRange=$A$1:$AIN$10006
rngDataCurrent=$A$10355:UsedRange=$A$1:$AJD$10355
rngDataCurrent=$A$10716:UsedRange=$A$1:$AJT$10716
rngDataCurrent=$A$11126:UsedRange=$A$1:$AKJ$11126
rngDataCurrent=$A$11526:UsedRange=$A$1:$AKZ$11526
rngDataCurrent=$A$11947:UsedRange=$A$1:$ALP$11947
rngDataCurrent=$A$12331:UsedRange=$A$1:$AMF$12331
rngDataCurrent=$A$12750:UsedRange=$A$1:$AMV$12750
rngDataCurrent=$A$13210:UsedRange=$A$1:$ANL$13210
rngDataCurrent=$A$13665:UsedRange=$A$1:$AOB$13665
rngDataCurrent=$A$14143:UsedRange=$A$1:$AOR$14143
rngDataCurrent=$A$14639:UsedRange=$A$1:$APH$14639
rngDataCurrent=$A$15095:UsedRange=$A$1:$APX$15095
rngDataCurrent=$A$15557:UsedRange=$A$1:$AQN$15557
rngDataCurrent=$A$16009:UsedRange=$A$1:$ARD$16009
rngDataCurrent=$A$16465:UsedRange=$A$1:$ART$16465
rngDataCurrent=$A$16941:UsedRange=$A$1:$ASJ$16941
rngDataCurrent=$A$17409:UsedRange=$A$1:$ASZ$17409
rngDataCurrent=$A$17946:UsedRange=$A$1:$ATP$17946
rngDataCurrent=$A$18523:UsedRange=$A$1:$AUF$18523
rngDataCurrent=$A$19093:UsedRange=$A$1:$AUV$19093
rngDataCurrent=$A$19643:UsedRange=$A$1:$AVL$19643
rngDataCurrent=$A$20208:UsedRange=$A$1:$AWB$20208
rngDataCurrent=$A$20764:UsedRange=$A$1:$AWR$20764
rngDataCurrent=$A$21316:UsedRange=$A$1:$AXH$21316
rngDataCurrent=$A$21861:UsedRange=$A$1:$AXX$21861

Open in new window

A few things here.

1. There is something about the data that your are pulling in from the website. The debug.prints show the code is moving to the spot in Excel that I want. Therefore, it is something in the Web Query and without having access to the real data, I am hamstrung.
2. Maybe fanpages cann see something I am missing
3. I doubt this will help but you could try changing

.WebSingleBlockTextImport = False

Open in new window

To

.WebSingleBlockTextImport = True

Open in new window

4. It will take a couple days before I can get back to this, but I might try to recode to extract from the browser window rather than the Excel Web Query.

-Bear

ASKER

Nah. That didn't fix it. What I am curious about is why Query 1 doesn't enter data starting in A1. Why does it start all the way out in FU1?

FYI: The data provided in the samples above is the actual data. All I did was change the header/field name/title and the data in few of the rows. Number of columns and number of rows were not altered in any way.

Thought: The data returned includes a single blank row before the "total" row. Might this be causing the issue? If you look at the sample data you will see that the next set of data is dropped in on the same row as the "total" row of the previous set. I am completely fine with removing the blank row and the total row. In fact, now that I look at it, those two rows need to be removed from all data return via a query.

Sorry to make this so complex.

The data is ending up in FU1 because of something on your website. The Debug.print statements show me the code is telling it to start in the far left column. The blank rows could have something to do with it, but I can't tell without seeing it in actiona. Right now I would need to step through the code as it is building the report. Seeing the data after the fact does not help. You could set the header row to off for all queries and see if that helps.

ASKER

turn them off here?

' Only load Field names if we are at the top
            If rngDataCurrent.AddressLocal = "$A$1" Then
                .FieldNames = True
            Else
                .FieldNames = False
            End If

Open in new window

If I do a single query and tell it to start in A1 it starts in A1 without any issues.

ASKER

I think I figured out what is happening. If I kill the macro after one iteration the data gets dropped into the worksheet starting in A1. When it loops through a second time it doesn't put the below the previous data but instead pushes the first set of data over 16 columns and then places the data in what it believes to be the first available row in column A. Third iteration is the same thing. It adds another 16 columns pushing the previous data to the right and then starts on what it believes to be the next available row in column A. The first row of the new query is in line with the final row of the previous query. I hope I explained that clearly. If not here is a simplified spreadsheet.
diagram.xlsx

I think that helps. Try changing

        Set rngDataCurrent = objDataSheet.Cells(objDataSheet.Rows.Count, 1).End(xlUp)

Open in new window

To

        Set rngDataCurrent = objDataSheet.Cells(objDataSheet.Rows.Count, 1).End(xlUp).Offset(1, 0)

Open in new window

We need to reset the other things back. I have attached a sample book.
URL-Builder-4.xlsm

Maybe fanpages cann see something I am missing

I am still subscribed/monitoring. If you are having issues after the latest update, I will look further.

ASKER

Oh yeah! We're getting somewhere. Data is lining up. I'm attaching an updated diagram of what the data looks like now.

I am thinking there are a couple ways to attack the remaining pieces that would work best for me.

1. Suppress the top and bottom two rows of data before they are placed in the worksheet. (Is this even possible?)

2. Write the data to the worksheet and then remove the top and bottom rows and shift the data up two rows. We would need to start on row 3 for this to work correctly. I would place my own column header in row 1.

Once this is ironed out is there any way to wrap all the data in a table?
diagram2.xlsx

Once this is ironed out is there any way to wrap all the data in a table?

Is that the last requirement, or will you have another "Columbo moment" ("...oh, just one more thing...") after that?

ASKER

I see what you are saying. That was actually part of the original requirement. ;)

".. I would like it all on one worksheet in one continuous table if possible."

I know. That was so long ago its easy to overlook. Ha ha!

If I had to add a "Columbo moment" it would likely be after this works. I think at that point I'd just open another question if I can't figure it out.

I truly do appreciate all the help. Much better than the experts that just drop a link as a response and tell you to figure it out.

I am not sure what you mean by one continuous table. Originally, I took that to mean the data was one solid chunk of data, but your latest statement makes that sound different. Are you wanting this as a named range? More detail is needed.

Try this latest version to see if it does what you need.
URL-Builder-5.xlsm

Just realized I forgot a very important line in the code. Try this instead.
URL-Builder-5.xlsm

ASKER

Ha! I made that first update and had headed back here to say nothing changed before I saw your second message. That small update did something but it isn't pretty. Here is a diagram of what the data looks like after I let it run for 3 queries. It's moving data but not deleting anything.
diagram3.xlsx

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

That appears to have fixed the issue! Is it pretty easy to start all of this at A2? My guess is this is all I need to do.

Set rngDataCurrent = objDataSheet.Range("A2")

Open in new window

Yes that would start it at A2.

ASKER

Super patient and very knowledgeable. An excellent experience!

ASKER

Got a follow up question to this project. You can find it here. I'll continue to add questions for this project as the y come up so you can earn points.

Columbo question

Well done Bear!

Bear: Double points if you are interested...

"Update worksheet to show progression of"
[ https://www.experts-exchange.com/questions/28150679/Update-worksheet-to-show-progression-of.html ]

@fanpages - Thanks for the kudos. It means a lot coming from a well respected expert like yourself!

The next question does not seem as nearly as complicated as this one or the other two we have helped on (one for you, one for me). I just jumped in over there. . . . .

:)

I always believe in credit where it is due, & you deserve recognition here, Bear.

Hope you see you in another question soon.

ASKER

Thanks to both of you. You've been a great help!