Need a little help easy probably..

Ok Im playing around with excel.. and

in vb I have

VB Code:

'more code.. above this
 
If Range("U2") = "" Then GoTo Line1 Else
    With ActiveSheet.QueryTables.Add(Connection:="URL;[""item""]", Destination:=dest)
        .RefreshStyle = xlOverwriteCells
 
        .AdjustColumnWidth = False
       
    With ActiveSheet.QueryTables(1).Parameters(1)
    .SetParam xlRange, Range("U2")
    End With
        .BackgroundQuery = True
        .Refresh
    End With
 
'more code below.

Now the code above is to grab a full http address from each cell one at a time and return in my excel page.. this works if I were to.. make the cells single digits like..
http://www.example.com/testpg?tp=1&lala=12
http://www.example.com/testpg?tp=2&lala=12
http://www.example.com/testpg?tp=3&lala=12

If I were to only use 1, 2, 3 for the [""item''] part..

like so..

VB Code:

With ActiveSheet.QueryTables.Add(Connection:="URL;[url]http://www.example.com/testpg?tp=[/url][""item""]&lala=12", Destination:=dest)

It works fine..

But if I use the [""item'''] part to grab the cell with the entire...
http://www.example.com/testpg?tp=1&lala=12

it doesnt work.. and I've checked the http: url link after its tried and what it does is turns my url into this...

http://www.example.com/testpg?tp%3D1%26lala%3D12

causing it to not find the correct URL.

My quest is why does it turn ='s signs into %3D and & signs into %26

and how do I prevent it from doing this?

Thanks much apprieciated!!!!!!!!!!!!!

( I posted this elsewhere once and found a note to post it in the classic section.. sorry guys new as of today)

It changes them to make them compatible with the HTTP protocol since you are using web links.

hmm.. so the site im trying to query doesnt like the %3D and the %26 in the url does that mean Im SOL?

Just use a replace to replace %3D with the = and the %26 with the &...you might find a few others too, like ? (can't remember what the number is for it though...I think it's actually the ASCII code but can't be sure)

Smux Im completely new to VB

I know asp php cgi etc etc...

What do you mean exactly use a replace?

the site uses

http://www.examplesite.com/sample.php?id=1&eg=two

runs that look fine..
but doesnt like when my vb converts it too
http://www.examplesite.com/sample.php?id%3D1%26eg%3Dtwo

and honestly I have no idea why VB seems to change it anyhow.

So whats the "replace" your talking about.

Thanks for any help!!

----------- SECONDS LATER...

I see I just searched VB and replace...
http://www.w3schools.com/vbscript/func_replace.asp

so your talking the replace function..
ok..

So where would it go??

VB Code:

If Range("U2") = "" Then GoTo Line1 Else
 
' would the replace function go here?? in the line below or can it go anywhere sorry guys I know how people react to complete newbies. lol
    With ActiveSheet.QueryTables.Add(Connection:="URL;[""item""]", Destination:=dest)
        .RefreshStyle = xlOverwriteCells
 
        .AdjustColumnWidth = False
       
    With ActiveSheet.QueryTables(1).Parameters(1)
    .SetParam xlRange, Range("U2")
    End With
        .BackgroundQuery = True
        .Refresh
    End With

Are you doing from within Excel?

yes excel and Microsoft Visual Basic 6.0 so it says.

If it is then it should be two completely separate programs. If you access the vb part by starting Excel first and opening the VBA IDE then its only Excel and VBA.

Going by your code so far it looks like VBA.

Whats the value inside of dest?

you mean the link? the Url ?

http://www.wagerline.com/Handicappin...rtid=1&picks=1

I need the = and the & to be as they are..
cept my vb code or something for some reason turns it into..

='s sign being %3D and & sign bring %26

so ive been told to use the replace function..

all I looking for is where would you place the replace function.. in the line?

or at the start of the code?

actually heres my whole code.. lemme know where the replace should go thanks.

VB Code:

Sub stuff_Lookup()
'clear previous data
    Sheets("Sheet1").Select
    Range("A300:G472").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("U2").Select
' dont laugh but this is my complete total guess on where to put the replace function????????
    Item = Replace(Item, "%26", "&")
 
 
    
'Begin the query
    Set dest = Range("A300:G472")
    For Each cell In [A1:A2]
        cell.Select
        Range("U2").Value = cell.Value
        
        
        
        
        
        
'Check for end of WTN list
        If Range("U2") = "" Then GoTo Line1 Else
    With ActiveSheet.QueryTables.Add(Connection:="URL;[""item""]", Destination:=dest)
        .RefreshStyle = xlOverwriteCells
 
        .AdjustColumnWidth = False
       
    With ActiveSheet.QueryTables(1).Parameters(1)
    .SetParam xlRange, Range("U2")
    End With
        .BackgroundQuery = True
        .Refresh
    End With
Line1:
    Range("U2").Select
    Set dest = dest.Offset(35, 0)
    Next cell
End Sub

so you would see in SHEET1 cell A1

http://www.wagerline.com/Handicappin...rtid=1&picks=1

then in SHEET 1 cell A2

http://www.wagerline.com/Handicappin...rtid=2&picks=1

and in sheet1 cell A3 etc etc etc...

http://www.wagerline.com/Handicappin...rtid=3&picks=1

I know I could make like http://www.wagerline.com/Handicappin....aspx?sportid=[""blabla""]&picks=[""blablainfo2""]

but I just want it to pick off the pages in the A column.. one by one.

it grabs em.. and posts them below on the page.. then runs to the next grabs it and posts it underneath etc etc...

I use it before a series of games are about to start.. but this way I see all the pages at once.. not having to flip page after page..

I also have a ton of excel formulas which auto calculate the hundreds of results instantaniously..

I know I know ... not a rocket science topic but .. its a helper for past time sunday afternoon hobby, lying on a couch and watching a game or two... lol

Isnt "item" no longer a variable as its enclosed in double quotes?
Also, Item is a keyword for collections and would be better in the long run to avoid using.

yep see I actually foudn the code on another site it suited my purpose for the past couple months... but now I know how cool vb Could be.. its like your always looking for or finding more "automation" lol..

so more or less once I figure this wildcard = %3D and & %26 thing out.. all i'll have to do is open the program and it auto downloads like 300 pages without me having to touch a thing.. and possibly after that it could be programmed to even save the file and do the process everyday.. hands free..

So yes and no I kinda have an idea how it all works but dont lol.

so whats my remedy for the replace %3D with = and %26 with &

any ideas? or are ya just toying with me like a cat with a semi dead mouse lol.
Anticipation is killing me on the answer.

.. oh wait I know what your waiting for.. Ok yes you are the VB GOD. hows that?

I dont believe that "item" is the actual value as it needs to be passed like so.

VB Code:

With ActiveSheet.QueryTables.Add(Connection:="URL;[" & Chr(34) & Item & Chr(34) & "]", Destination:=dest)

If you record a macro creating the webquery you can see how to set all its properties correctly. No need to worry about replaced characters either. ;)

VB Code:

With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;http://www.wagerline.com/Handicapping/topHandicappers.aspx?sportid=1&picks=1" _
    , Destination:=Range("A300:G472"))
    .Name = "0o0o0"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .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:= True
End With

Actually I found it...

the code that i found, where I originally stared all this..


http://www.business-spreadsheets.com/forum.asp?t=57



But now what Im trying to do is.. instead of having.. like his example...

http://www.stuff.com/support/api/stuff.cgi?item=[""item""]",

and posting names of the items in
For Each cell In [G14:G64]

I would rather have the entire
http://www.stuff.com/support/api/stuff.cgi?item=1
http://www.stuff.com/support/api/stuff.cgi?item=2
http://www.stuff.com/support/api/stuff.cgi?item=3
http://www.stuff.com/support/api/stuff.cgi?item=4
http://www.stuff.com/support/api/stuff.cgi?item=5
and so on ..
in the the cells G14:G64

but thats where this code turns a regular URL into a friggen %3D and %26 bs..

why!!!!!!!!!!

just wanna know whats happening and why cant it take a whole address.

here instead of going to that page I'll bring the code here. Not my code but the guys example.

VB Code:

Sub stuff_Lookup()
'clear previous data
    Sheets("Scrubber").Select
    Range("I14:S100").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("A9").Select
'Begin the query
    Set dest = Range("I14")
    For Each cell In [G14:G64]
        cell.Select
        Range("A9").Value = cell.Value
'Check for end of WTN list
        If Range("A9") = "" Then GoTo Line1 Else
    With ActiveSheet.QueryTables.Add(Connection:="URL;[url]http://www.stuff.com/support/api/stuff.cgi?item=[/url][""item""]", Destination:=dest)
        .RefreshStyle = xlOverwriteCells
        .WebFormatting = xlWebFormattingNone
        .AdjustColumnWidth = False
        .PreserveFormatting = True
    With ActiveSheet.QueryTables(1).Parameters(1)
    .SetParam xlRange, Range("A9")
    End With
        .BackgroundQuery = True
        .Refresh
    End With
Line1:
    Range("A1").Select
    Set dest = dest.Offset(1, 0)
    Next cell
End Sub

try it.. you'll see how it works.. But i need the whole http://address'es in cells G15 :G64

why? cause I have more varibles than this guy used in the urls..
besides item=
.. I have

Sport= and Game Number = and id =


am I really SOL on this one .. trying to use this script


Thanks to anyone who can give a fix.. so i dont get %3D instead of = and %26 instead of & using the whole http://urls in the cells.

:eek2:

Does nobody use If functions??? if you used the If function in place of some other more complex functions, you would be able to understand the code better

Quote:

---

"Does nobody use If functions??? if you used the If function in place of some other more complex functions, you would be able to understand the code better"

---

Shoot! any suggestions... IF sounds great what do i need to do??


....Well Im burnt out..

Anyone ever use a script, know of a script or thought of a script that will

take a list of Urls in an Excel Page.. say cells A1 to A300.. so 300 urls..

Run through each url and pull the page into the same or a second excel page??

Just that simple.. but seems to be a Hugely impossible task.

just simply said 300 web Queries from a list of 300 urls in the A column in excel..

and pulls em in on the same page down below .. or on a second page.

Anyone?

lol damn thats a full 13 hours in this chair trying to get it to work.
check my first post time above. Im completely stoked.

( ya ya I could do 300 manually entered.. but the links and url change each day)

It sounds to me like you're messing about with other people's code with no real knowledge of how they work, and you should be prepared for failure if this is the case because no matter how much help we give (short of actually writing the damn thing for you, which we won't do unless we're *really* bored or *really* helpful :-)) you'll still not have a working program unless you're the luckiest person in the world.

If the program is VBA and is running as part of Excel, this isn't the right place to be asking questions about VBA...but if you are running VB6, then this is the right place :-)

replace is a simple function...imagine the string "ABXDEF"...If you did newstring = replace("ABXDEF","X","C") it would put "ABCDEF" into the variable newstring. In much the same way you would use replace("http://www.someurl.com?what%3Dthis%26who%3Ddat","%3D","=") to get "http://www.someurl.com?what=this&who=dat" (don't forget you'd need 2 replaces, one for = and one for & ...I've only listed one above)

replace can work with a string both as input or output, so you don't have to put the data in each time :-)

Again, read post 12 and 13. Everything worked fine for me when its formatted correctly without the ""item"" part as its not valid.

ok.. I feel like an imigrant here..

Where does the replace line go?

also..

say I want

newstring = replace("what goes in here if the cell value url changes","%26","&")

Or
would..

newstring = replace("%26","%26","&")
work?

Also.. the Btch doesnt like % signs.

Is the replace line universal for any url it uses?? or do I have to add a varible or "newstring" into the Connection:="URL;

Thats it thats all my last question..

is this really spoon feeding? just two simple tips. I dont need code written for me.. just read back my posts.. smux answered first question..
I need to use replace.

my second question where would it go?

thats it.

I read elsewhere on the forum guys who are running there houses with vba code.. lol Im just finding very hard to believe I cant get out two straight answers.. only one.

And dude showing me alternative ways above and not just simply answering my two questions.. lol its nuts.

k guys no disrespect at all. Just give me the answer to the second question and im good. And wont need VBA forums anymore. I just came for a simple question how to change the %3D and %26 back to a regular URL.

thanks tho guys much apprieciated.

No, any level of questions are al valid and acceptable on VBF. :)

Yes, the code on #13 does only one but like I was mentioning, if you recorda macro of you creating the webquery the way you want you can generate the code that will load all 300+ records. Working with this other code is a bit much to try to completely fix as shown by the minimal code needed for just one record. :)

ok then moving down that avenue.. does a macro record...

copy pasting the contents of cell A2 into the
"URL;http://www.wagerline.com/Handicapping/topHandicappers.aspx?sportid=1&picks=1" _
, Destination:=Range("A300:G472"))

line and then A3 and copy pasting that into the

"URL;http://www.wagerline.com/Handicapping/topHandicappers.aspx?sportid=1&picks=1" _
, Destination:=Range("A300:G472"))

etc etc???

will a recording a macro record in excel and also vba?

the example you gave lol is so basic Ive done tha a million times I just wasnt banking on rewriting the whole thing over. Just needed to change %3D and %26

ok guys im gettin nowhere here. just Consuming alot of forum space.

Thanks for all your suggestions....... or replies.
:wave:

See the problem for me, at least, is that I dont get the changing characters. Macro recording copies most GUI actions including copy/paste. Not sure what you mean by recording VBA?

recieve thy answers you shall not.
Thanks yoda.

Static sends me the answer by email.

a whopping total of 25 charactors.

Fixed simple. No answering questions with questions, no hassle.

you guys are brutal.