Solved

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

Posted on 2013-05-31
56
473 Views
Last Modified: 2013-06-10
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
0
Comment
Question by:futr_vision
  • 29
  • 17
  • 10
56 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39211898
A related Previously Asked Question, for reference:

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

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28142299.html ]
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39213584
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
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39213988
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.
0
 

Author Comment

by:futr_vision
ID: 39214176
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.
0
 

Author Comment

by:futr_vision
ID: 39214194
Hmm. Error. See attached screengrab. I'm running Excel 2010 64-bit.
32Bit-Error.PNG
0
 

Author Comment

by:futr_vision
ID: 39214398
Looks like a problem with the "Sleep 50" part of the code.

Sub or function not defined
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39214497
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.
0
 

Author Comment

by:futr_vision
ID: 39214757
Thanks. I made the change and get a new error. See attached.
32Bit-Error2.PNG
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39214926
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.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39215041
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
0
 

Author Comment

by:futr_vision
ID: 39215205
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.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39215255
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.
0
 

Author Comment

by:futr_vision
ID: 39215296
Ah. Exit_Worksheet_Change not defined(line 9 above). That should be Err_Worksheet_Change correct?
0
 

Author Comment

by:futr_vision
ID: 39215297
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

0
 

Author Comment

by:futr_vision
ID: 39215299
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
0
 

Author Comment

by:futr_vision
ID: 39215308
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.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39215535
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.
0
 

Author Comment

by:futr_vision
ID: 39215861
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
0
 

Author Comment

by:futr_vision
ID: 39215891
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
0
 

Author Comment

by:futr_vision
ID: 39215907
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
0
 

Author Comment

by:futr_vision
ID: 39215917
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

0
 

Author Comment

by:futr_vision
ID: 39215985
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.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39216010
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
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39216018
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
0
 

Author Comment

by:futr_vision
ID: 39216139
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
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39216270
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
0
 

Author Comment

by:futr_vision
ID: 39216354
I noticed that and fixed it. Same results.

Thanks for taking the time to check it out tonight!
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39217621
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
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:futr_vision
ID: 39217673
We might be getting closer. At least this data has some semblance of a pattern :)
Sample-Data-Output2.xlsx
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39217971
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).
0
 

Author Comment

by:futr_vision
ID: 39218582
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

0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39218742
I see one more thing to change.

Change:
            .Name = "WebQuery"

Open in new window

To
            .Name = "WebQuery_" & rngDataCurrent.Row

Open in new window

0
 

Author Comment

by:futr_vision
ID: 39218933
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

0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39220868
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
0
 

Author Comment

by:futr_vision
ID: 39220952
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.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39221920
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.
0
 

Author Comment

by:futr_vision
ID: 39224097
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.
0
 

Author Comment

by:futr_vision
ID: 39224192
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
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39224527
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
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39224845
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.
0
 

Author Comment

by:futr_vision
ID: 39225165
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
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39225305
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?
0
 

Author Comment

by:futr_vision
ID: 39225326
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.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39225372
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
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39225789
Just realized I forgot a very important line in the code.  Try this instead.
URL-Builder-5.xlsm
0
 

Author Comment

by:futr_vision
ID: 39227863
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
0
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 500 total points
ID: 39227896
I see a couple mistakes that I made.  Try this.
URL-Builder-6.xlsm
0
 

Author Comment

by:futr_vision
ID: 39227939
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

0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39227946
Yes that would start it at A2.
0
 

Author Closing Comment

by:futr_vision
ID: 39228851
Super patient and very knowledgeable. An excellent experience!
0
 

Author Comment

by:futr_vision
ID: 39228877
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
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39228905
Well done Bear!
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39229395
Bear: Double points if you are interested...

"Update worksheet to show progression of"
[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28150679.html ]
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39230702
@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. . . . .
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39233467
:)

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

Hope you see you in another question soon.
0
 

Author Comment

by:futr_vision
ID: 39234338
Thanks to both of you. You've been a great help!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now