Modifying a macro "Bob Smith" <usirsclt_No_Spamola_[ at ]earthlink.net> 3/10/2007 8:55:20 PM 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? TIA

Re: Modifying a macro Dick Kusleika <dkusleika[ at ]gmail.com> 3/10/2007 9:49:56 PM On Sat, 10 Mar 2007 20:55:20 GMT, "Bob Smith" <usirsclt_No_Spamola_[ at ]earthlink.net> wrote: [Quoted Text] >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

Re: Modifying a macro "Bob Smith" <usirsclt_No_Spamola_[ at ]earthlink.net> 3/29/2007 6:12:14 PM "Dick Kusleika" <dkusleika[ at ]gmail.com> wrote in message news:pu96v2h2is08ds4joart0kut8ka42mpin4[ at ]4ax.com... [Quoted Text] > On Sat, 10 Mar 2007 20:55:20 GMT, "Bob Smith" > <usirsclt_No_Spamola_[ at ]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 ' ' 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 Range("A7").Select With Selection.QueryTable .Connection = _ "URL;http://players.buzztime.com/bt_profile.php?pid=207853&tn=trivia" .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = """statsTable""" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Sheets("Abused News").Select With Selection.QueryTable .Connection = _ "URL;http://players.buzztime.com/bt_profile.php?pid=207853&tn=trivia&dn=t7&d=23&stp=av" .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = """statsTable""" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With Let me know whether you'd like to see the whole file - about 300K large. Best regards, Bob

Re: Modifying a macro Dick Kusleika <dkusleika[ at ]gmail.com> 3/29/2007 8:09:00 PM On Thu, 29 Mar 2007 18:12:14 GMT, "Bob Smith" <usirsclt_No_Spamola_[ at ]earthlink.net> wrote: [Quoted Text] > 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

Re: Modifying a macro "Bob Smith" <usirsclt_No_Spamola_[ at ]earthlink.net> 3/29/2007 10:17:43 PM 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[ at ]gmail.com> wrote in message news:7t6o03pkv8uukhmvsjamj64rkmmvu6ftnr[ at ]4ax.com... [Quoted Text] > On Thu, 29 Mar 2007 18:12:14 GMT, "Bob Smith" > <usirsclt_No_Spamola_[ at ]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

Re: Modifying a macro Dick Kusleika <dkusleika[ at ]gmail.com> 3/30/2007 5:12:54 PM On Thu, 29 Mar 2007 22:17:43 GMT, "Bob Smith" <usirsclt_No_Spamola_[ at ]earthlink.net> wrote: [Quoted Text] >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 sPID = Sheets("MySheet").Range("Z1").Value changing MySheet to your sheet name. -- Dick Kusleika Microsoft MVP-Excel http://www.dailydoseofexcel.com