Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Find the count of each users questions vbs script or excel macro

Posted on 2012-12-29
18
Medium Priority
?
354 Views
Last Modified: 2013-01-29
I need help with a script that can go to each page like this

I have the user names in a file or column A in excel

http://tinyurl.com/c2z59lw

Then check the questions tab and get the count alone to column "B"

Please login to the site with these credentials to enter to the page

User name : EE-Test
Pass : EE-Test

thanks
0
Comment
Question by:bsharath
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
18 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38729850
Hi, bsharath.

Please see attached. The code is...
Option Explicit

Sub Check_URLS()
Dim i           As Long
Dim xResult     As Variant
Dim xCell       As Range
Dim xLast_Row   As Long

Sheets("Check URL's").Activate

xLast_Row = Range("A1").SpecialCells(xlLastCell).Row

If xLast_Row < 2 Then
    MsgBox ("No data found - run cancelled.")
    Exit Sub
End If

Range("B2:B" & xLast_Row).ClearContents

For Each xCell In Range("A2:A" & xLast_Row)
    
    If xCell <> "" Then
        i = i + 1
        xResult = Get_URL(xCell.Value)
    Else
        xResult = -1
    End If
    
    xCell.Offset(0, 1) = xResult
    
    Application.ScreenUpdating = True
    
Next

End Sub

Function Get_URL(xUrl As String) As Variant
Dim xTempSheet  As Worksheet
Dim xConnection As String
Dim xFound      As Range

Application.ScreenUpdating = False

Set xTempSheet = Sheets.Add
xConnection = "x" & Format(Now(), "hhmmss")

On Error Resume Next

    With xTempSheet.QueryTables.Add(Connection:="URL;" & xUrl, Destination:=Range("$A$1"))
        .Name = xConnection
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

On Error GoTo 0

If xTempSheet.Range("A1").SpecialCells(xlLastCell).Row = 1 Then
    Application.DisplayAlerts = False
        xTempSheet.Delete
    Application.DisplayAlerts = True
    Get_URL = -1
    Exit Function
End If

Set xFound = xTempSheet.Range("A:A").Find(What:="No of Questions : ", LookIn:=xlFormulas, LookAt:=xlPart _
                , SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        
If xFound Is Nothing Then
    Get_URL = 0
Else
    Get_URL = Mid(xFound, 19, 9999)
End If

Application.DisplayAlerts = False
    xTempSheet.Delete
Application.DisplayAlerts = True

End Function

Open in new window

Regards,
Brian.
Check-URL-s-V.xlsm
0
 
LVL 11

Author Comment

by:bsharath
ID: 38729876
Can i have the column with just the names

Mike Atherton

should be
Mike%20Atherton

I tried with this one name and i get 0 where as it has 6 questions
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38729888
bsharath,

Sorry, I'm not clear about what you mean. Please post a sample entry

Edit: I tried "http://www.techyv.com/detail/Mike%20Atherton" and got 6 entries.

Thanks,
Brian.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 11

Author Comment

by:bsharath
ID: 38729896
This URL
http://tinyurl.com/bs4jtua

has 6 questions but excel shows as "0"
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38729902
bsharath,,

Yes, I got six as well. Did you use the same URL as I did or the Tinyurl?

Edit: Please post the file you used.

Thanks,
Brian.
0
 
LVL 11

Author Comment

by:bsharath
ID: 38729921
Please check attached file
Check-URL-s-V.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38729941
bsharath,

That worked fine for me. I wonder is this possibly a log-in issue? To check this, please do the following...
 - On the Ribbon's menu bar, click on "Data".
 - On the Data tab, click on "From Web".
 - In the Address box, enter  "http://www.techyv.com/detail/Mike%20Atherton" (no quotes!) and xlixk on Import.

Do you get the user's details or a log-in prompt?

Thanks,
Brian.
0
 
LVL 11

Author Comment

by:bsharath
ID: 38729962
I get a login prompt
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38729968
Thanks, bsharath, that explains our different results.

For the moment, use that log-in prompt to enter your user-id and password and then test the file. (The log-in should last until you restart Excel.) I'll look into passing the password.

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38730625
bsharath,

There are a number of (insecure) ways of passing user-id and password in a URL, however I can't get them to work on Techyv. I don't know whether this is caused by my web ignorance or with the site itself.

Assuming that the code is otherwise OK, you may wish to raise a new question in a more appropriate topic here or directly in Techyv.

Regards,
Brian.
0
 
LVL 11

Author Comment

by:bsharath
ID: 38730637
Please refer this

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27976953.html

this code logs into the site. May be of help, not sure
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38730723
bsharath,

Thanks for the interesting link. I dropped the code for creating a new question and my version successfully logs into Techyv. However, this doesn't make any difference in Excel - loading the page there still shows the user as logged off.

Consistently, if I have logged on from Excel (using "From Web") then the results are correct. However, once I've logged out from there then I get the zero results.

If it's not acceptable to you to do a manual log-on, I'd recommend that you suggest in one of your open questions in which Robert is involved that he get involved here. Not only could he give you the definitive answer on logging on, but he may also have a faster way of getting the data back.

Regards,
Brian.
0
 
LVL 11

Author Comment

by:bsharath
ID: 38730906
Thanks Brian just left a note to Robert. Lets see if he can add help into this
0
 
LVL 11

Author Comment

by:bsharath
ID: 38771493
ID: 38729850
As in this
can you run the excel and send back with the results please

Also i will be already logged in in IE before i run this macro
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38771747
bsharath,

As in this
can you run the excel and send back with the results please.

Sorry, I don't understand this.

Also i will be already logged in in IE before i run this macro
That works with some sites, but, IIRC, not your one. Unfortunately the EE-Test login no longer works so I can't check this again.

Regards,
Brian.
0
 
LVL 11

Author Comment

by:bsharath
ID: 38771758
please try password EE-test

What i meant was the above excel can you run the macro so i can see if the results are right

As for me all i get are 0's
0
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 38771851
bsharath,

Yes, that one works, thanks.

As for me all i get are 0's
It's exactly as I mentioned earlier...
Consistently, if I have logged on from Excel (using "From Web") then the results are correct.
Had you done this immediately before running the macro?

My memory was correct - logging in from EE makes no difference. (Many sites have the facility to remember your password but not, apparently, your site.)

Please see the attached which includes the results.

Brian.
Check-URL-s-V---Results.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38831586
Thanks, bsharath.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 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