(Msg. 1) Posted: Sat Mar 10, 2007 8:55 pm
Post subject: Modifying a macro Archived from groups: microsoft>public>excel>querydao (more info?)
Hi, I've got a workbook with 27 web queries. I've set up a macro to modify a
portion of those web query URLs. Each query has my account # 207853. I'd
like to pass on this file to others, to where they can pull up their
information with their separate acct #.
Now, I can tell them to go into Tools, Macros & select the macro, then edit
it, and do a control E-E to replace my number with theirs and the next time
they open the file, their data downloads.
Only thing is I'd like to make it even easier, by just plugging their acct #
in a cell and it modifies the macro file automatically, so that they don't
have to go putzing around the macro
(Msg. 2) Posted: Sat Mar 10, 2007 8:55 pm
Post subject: Re: Modifying a macro [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Sat, 10 Mar 2007 20:55:20 GMT, "Bob Smith"
<usirsclt_No_Spamola_ RemoveThis @earthlink.net> wrote:
>Hi, I've got a workbook with 27 web queries. I've set up a macro to modify a
>portion of those web query URLs. Each query has my account # 207853. I'd
>like to pass on this file to others, to where they can pull up their
>information with their separate acct #.
>
>Now, I can tell them to go into Tools, Macros & select the macro, then edit
>it, and do a control E-E to replace my number with theirs and the next time
>they open the file, their data downloads.
>
>Only thing is I'd like to make it even easier, by just plugging their acct #
>in a cell and it modifies the macro file automatically, so that they don't
>have to go putzing around the macro
>
>Is that doable?
>
Yes. Post the code (or relevent parts thereof) that you have now.
(Msg. 3) Posted: Thu Mar 29, 2007 3:03 pm
Post subject: Re: Modifying a macro [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
"Dick Kusleika" <dkusleika.RemoveThis@gmail.com> wrote in message
news:pu96v2h2is08ds4joart0kut8ka42mpin4@4ax.com...
> On Sat, 10 Mar 2007 20:55:20 GMT, "Bob Smith"
> <usirsclt_No_Spamola_.RemoveThis@earthlink.net> wrote:
>
>>Hi, I've got a workbook with 27 web queries. I've set up a macro to modify
>>a
>>portion of those web query URLs. Each query has my account # 207853. I'd
>>like to pass on this file to others, to where they can pull up their
>>information with their separate acct #.
>>
>>Now, I can tell them to go into Tools, Macros & select the macro, then
>>edit
>>it, and do a control E-E to replace my number with theirs and the next
>>time
>>they open the file, their data downloads.
>>
>>Only thing is I'd like to make it even easier, by just plugging their acct
>>#
>>in a cell and it modifies the macro file automatically, so that they don't
>>have to go putzing around the macro
>>
>>Is that doable?
>>
>
> Yes. Post the code (or relevent parts thereof) that you have now.
>
> --
> Dick
Hi Dick,
Thanks for the reply. Sorry for the delay in getting back to you. Here's the
VB code out of the macro for the first 3 replications of the file.
Sub ChangeNumber()
'
' ChangeNumber Macro
' Macro recorded 3/10/2007 by Robert Smith
'
(Msg. 4) Posted: Thu Mar 29, 2007 3:09 pm
Post subject: Re: Modifying a macro [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Thu, 29 Mar 2007 18:12:14 GMT, "Bob Smith"
<usirsclt_No_Spamola_.TakeThisOut@earthlink.net> wrote:
The user put their ID in Z1 (change as desired) and runs the macro, and all
the URLs are updated to show the correct web query. You'll need to modify
the .Conection= part like I did above for all of the With Blocks in your
code.
(Msg. 5) Posted: Thu Mar 29, 2007 7:02 pm
Post subject: Re: Modifying a macro [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Thanks for that quick reply Dick. It's appreciated.
Now, maybe this is a dumb question, but would the new user have to plug in
their # in every sheet that replicates with the server? Is it possible to
just have them plug in their number in one worksheet, instead of 27 sheets?
Thanks, Bob
"Dick Kusleika" <dkusleika.TakeThisOut@gmail.com> wrote in message
news:7t6o03pkv8uukhmvsjamj64rkmmvu6ftnr@4ax.com...
> On Thu, 29 Mar 2007 18:12:14 GMT, "Bob Smith"
> <usirsclt_No_Spamola_.TakeThisOut@earthlink.net> wrote:
>
>> Range("A1").Select
>> With Selection.QueryTable
>> .Connection = _
>>
>> "URL;http://players.buzztime.com/bt_profile.php?pid=207853&tn=overall"
>> .WebSelectionType = xlSpecifiedTables
>> .WebFormatting = xlWebFormattingNone
>> .WebTables = """statsSummaryTable"",17"
>> .WebPreFormattedTextToColumns = True
>> .WebConsecutiveDelimitersAsOne = True
>> .WebSingleBlockTextImport = False
>> .WebDisableDateRecognition = False
>> .WebDisableRedirections = False
>> .Refresh BackgroundQuery:=False
>> End With
>
> You need to create a variable that reads the value of a cell, then use
> that
> variable in your URL.
>
> Dim sPID As String
>
> sPID = Range("Z1").Value
>
> With Range("A1").QueryTable
> .Connection = _
> "URL;http://players.buzztime.com/bt_profile.php?pid=" & sPID &
> "&tn=overall"
> .WebSelectionType = xlSpecifiedTables
> .WebFormatting = xlWebFormattingNone
> .WebTables = """statsSummaryTable"",17"
> .WebPreFormattedTextToColumns = True
> .WebConsecutiveDelimitersAsOne = True
> .WebSingleBlockTextImport = False
> .WebDisableDateRecognition = False
> .WebDisableRedirections = False
> .Refresh BackgroundQuery:=False
> End With
>
> The user put their ID in Z1 (change as desired) and runs the macro, and
> all
> the URLs are updated to show the correct web query. You'll need to modify
> the .Conection= part like I did above for all of the With Blocks in your
> code.
>
> --
> Dick Kusleika
> Microsoft MVP-Excel
> http://www.dailydoseofexcel.com
(Msg. 6) Posted: Fri Mar 30, 2007 12:12 pm
Post subject: Re: Modifying a macro [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Thu, 29 Mar 2007 22:17:43 GMT, "Bob Smith"
<usirsclt_No_Spamola_.RemoveThis@earthlink.net> wrote:
>Thanks for that quick reply Dick. It's appreciated.
>
>Now, maybe this is a dumb question, but would the new user have to plug in
>their # in every sheet that replicates with the server? Is it possible to
>just have them plug in their number in one worksheet, instead of 27 sheets?
>
>>
>> Dim sPID As String
>>
>> sPID = Range("Z1").Value
>>
This will get the ID from the active worksheet and that same ID will be used
for all of the web queries - sPID doesn't change once its value is assigned.
If you want to get it from a specific sheet that may not be the activesheet,
change this line to
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office