9 New Messages
Digest #2320
| 3c Re: Minimizing or controlling SMF Addin calculations in a macro by "zarathustra_winced@yahoo.com" zarathustra_winced |
Messages
Mon Aug 20, 2012 6:08 am (PDT) . Posted by:
"barbiegirl" pgh2377
I'm really trying to work with the GetWebData function, unfortunately I feel like a pre-school kid in a high school chemistry class...
I'm trying to obtain the "Recommendation Summary" from
http://finance.yahoo.com/q/ao?s=ORCL+Analyst+Opinion
I'm ok using the function I think up into where I need to input the extract part but I keep getting an error message. Any tricks of the trade with this subject?
I'm trying to obtain the "Recommendation Summary" from
http://finance.
I'm ok using the function I think up into where I need to input the extract part but I keep getting an error message. Any tricks of the trade with this subject?
Mon Aug 20, 2012 9:03 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I only use RCHGetWebData() as a last resort. It's much easier to use
RCHGetTableCell() for tabular data, or smfGetTagContent() for non-tabular
data. For example, one of the items you want is easy with:
=RCHGetTableCell("http://finance.yahoo.com/q/ao?s=ORCL ",1,"(this week):")
...or even:
=smfGetTagContent("http://finance.yahoo.com/q/ao?s=ORCL ","td",1,"(this
week):")
On Mon, Aug 20, 2012 at 6:08 AM, barbiegirl <pgh2377@yahoo.com > wrote:
> I'm really trying to work with the GetWebData function, unfortunately I
> feel like a pre-school kid in a high school chemistry class...
>
> I'm trying to obtain the "Recommendation Summary" from
>
> http://finance.yahoo.com/q/ao?s=ORCL+Analyst+Opinion
>
> I'm ok using the function I think up into where I need to input the
> extract part but I keep getting an error message. Any tricks of the trade
> with this subject?
>
RCHGetTableCell(
data. For example, one of the items you want is easy with:
=RCHGetTableCell(
...or even:
=smfGetTagContent(
week):")
On Mon, Aug 20, 2012 at 6:08 AM, barbiegirl <pgh2377@yahoo.
> I'm really trying to work with the GetWebData function, unfortunately I
> feel like a pre-school kid in a high school chemistry class...
>
> I'm trying to obtain the "Recommendation Summary" from
>
> http://finance.
>
> I'm ok using the function I think up into where I need to input the
> extract part but I keep getting an error message. Any tricks of the trade
> with this subject?
>
Mon Aug 20, 2012 7:30 am (PDT) . Posted by:
"bob_cutillo" bob_cutillo
Hi,
First, Thanks for a great service.
I want to capture website information from a password protected site. without password I am fine; Un-password protected is fine. Selection.FormulaArray = "=RCHGetHTMLTable(URL,Parm1,Parm1a,parm2,Parm2a)" but I am getting the text from my login page as a reply. How do I debug this? Is there a prior post that I haven't been able to find or an example that may show how to debug mine? Any ideas? I know that I haven't given you the website but I'm looking for a more generic view into how to debug this from view source code or another way.
Thank you.
First, Thanks for a great service.
I want to capture website information from a password protected site. without password I am fine; Un-password protected is fine. Selection.FormulaAr
Thank you.
Mon Aug 20, 2012 7:44 am (PDT) . Posted by:
"dguillett1" donaldb36
I think Randy will tell you that you need to "log on" first.
Don Guillett
Microsoft Excel Developer
SalesAid Software
dguillett1@gmail.com
From: bob_cutillo
Sent: Monday, August 20, 2012 9:30 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Password protected site
Hi,
First, Thanks for a great service.
I want to capture website information from a password protected site. without password I am fine; Un-password protected is fine. Selection.FormulaArray = "=RCHGetHTMLTable(URL,Parm1,Parm1a,parm2,Parm2a)" but I am getting the text from my login page as a reply. How do I debug this? Is there a prior post that I haven't been able to find or an example that may show how to debug mine? Any ideas? I know that I haven't given you the website but I'm looking for a more generic view into how to debug this from view source code or another way.
Thank you.
Don Guillett
Microsoft Excel Developer
SalesAid Software
dguillett1@gmail.
From: bob_cutillo
Sent: Monday, August 20, 2012 9:30 AM
To: smf_addin@yahoogrou
Subject: [smf_addin] Password protected site
Hi,
First, Thanks for a great service.
I want to capture website information from a password protected site. without password I am fine; Un-password protected is fine. Selection.FormulaAr
Thank you.
Mon Aug 20, 2012 8:12 am (PDT) . Posted by:
"bob_cutillo" bob_cutillo
Thank you for your help. I should have included that I had my browser, Foxfire, logged in already and even tried it from Excel ; Data / from Web. Although I could log in that way, I couldn't get anything back. I should have mentioned also that in the view source of the browser, I do see the information that I am trying to capture but haven't seen a way to get it into my spreadsheet. I have tried changing all of the parameters but it's just guessing right now. I was hoping to learn how to translate what I see on View source into what I need to put into my function call.
Thanks again.
--- In smf_addin@yahoogroups.com , "dguillett1" <dguillett1@...> wrote:
>
> I think Randy will tell you that you need to “log on†first.
>
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguillett1@...
>
> From: bob_cutillo
> Sent: Monday, August 20, 2012 9:30 AM
> To: smf_addin@yahoogroups.com
> Subject: [smf_addin] Password protected site
>
>
> Hi,
> First, Thanks for a great service.
>
> I want to capture website information from a password protected site. without password I am fine; Un-password protected is fine. Selection.FormulaArray = "=RCHGetHTMLTable(URL,Parm1,Parm1a,parm2,Parm2a)" but I am getting the text from my login page as a reply. How do I debug this? Is there a prior post that I haven't been able to find or an example that may show how to debug mine? Any ideas? I know that I haven't given you the website but I'm looking for a more generic view into how to debug this from view source code or another way.
>
> Thank you.
>
Thanks again.
--- In smf_addin@yahoogrou
>
> I think Randy will tell you that you need to “log on†first.
>
> Don Guillett
> Microsoft Excel Developer
> SalesAid Software
> dguillett1@.
>
> From: bob_cutillo
> Sent: Monday, August 20, 2012 9:30 AM
> To: smf_addin@yahoogrou
> Subject: [smf_addin] Password protected site
>
>
> Hi,
> First, Thanks for a great service.
>
> I want to capture website information from a password protected site. without password I am fine; Un-password protected is fine. Selection.FormulaAr
>
> Thank you.
>
Mon Aug 20, 2012 9:49 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Logging in with FireFox won't help. The add-in is using the XMLHTTP method
to get data from the Internet. That's the same method both IE and EXCEL
use. That means it shares the Temporary Internet Files and cookies of IE.
So you need to login with either the EXCEL Web Query process (preferred) or
with IE. Because it will create the security cookie that the XMLHTTP method
uses.
If you can't get to the data with the EXCEL Web Query process, the add-in
probably won't be able to get it either. Both require a bookmarkable URL
and that the source code of the web page actually contains the data.
If you can't see the data in the source code, it could be because the web
page is dynamically generated or that the data is presented in a frame. In
the former case, you'd need to write your own VBA routine to interact with
the IE object. In the latter case, you'd need to know the URL of the frame,
to see if you can extract the data from there.
On Mon, Aug 20, 2012 at 8:12 AM, bob_cutillo <jbkbob@gmail.com > wrote:
> Thank you for your help. I should have included that I had my browser,
> Foxfire, logged in already and even tried it from Excel ; Data / from Web.
> Although I could log in that way, I couldn't get anything back. I should
> have mentioned also that in the view source of the browser, I do see the
> information that I am trying to capture but haven't seen a way to get it
> into my spreadsheet. I have tried changing all of the parameters but it's
> just guessing right now. I was hoping to learn how to translate what I see
> on View source into what I need to put into my function call.
>
to get data from the Internet. That's the same method both IE and EXCEL
use. That means it shares the Temporary Internet Files and cookies of IE.
So you need to login with either the EXCEL Web Query process (preferred) or
with IE. Because it will create the security cookie that the XMLHTTP method
uses.
If you can't get to the data with the EXCEL Web Query process, the add-in
probably won't be able to get it either. Both require a bookmarkable URL
and that the source code of the web page actually contains the data.
If you can't see the data in the source code, it could be because the web
page is dynamically generated or that the data is presented in a frame. In
the former case, you'd need to write your own VBA routine to interact with
the IE object. In the latter case, you'd need to know the URL of the frame,
to see if you can extract the data from there.
On Mon, Aug 20, 2012 at 8:12 AM, bob_cutillo <jbkbob@gmail.
> Thank you for your help. I should have included that I had my browser,
> Foxfire, logged in already and even tried it from Excel ; Data / from Web.
> Although I could log in that way, I couldn't get anything back. I should
> have mentioned also that in the view source of the browser, I do see the
> information that I am trying to capture but haven't seen a way to get it
> into my spreadsheet. I have tried changing all of the parameters but it's
> just guessing right now. I was hoping to learn how to translate what I see
> on View source into what I need to put into my function call.
>
Mon Aug 20, 2012 3:40 pm (PDT) . Posted by:
"curls07" curls07
Hi Guys,
When I run a macro in a worksheet that contains rch get element functions, the functions recalculate every time the macro copies or pastes or otherwise performs and action on the spread sheet. The Macro has to wait for the elements to recalculate even though they aren't getting any new information.This makes my macro run much slower than it has to because of the unnecessary calculations.
Has anyone else had this problem and figured out some way to control or minimize it? I've experimented with manual calculation, but that makes greater problems with the calculations done by my macro.
I'm using the newest version of SMF addin in excel 2010 and I am using automatic calculation in my workbook.
I'm open to any ideas you guys may have. Thank you for your help and consideration.
When I run a macro in a worksheet that contains rch get element functions, the functions recalculate every time the macro copies or pastes or otherwise performs and action on the spread sheet. The Macro has to wait for the elements to recalculate even though they aren't getting any new information.
Has anyone else had this problem and figured out some way to control or minimize it? I've experimented with manual calculation, but that makes greater problems with the calculations done by my macro.
I'm using the newest version of SMF addin in excel 2010 and I am using automatic calculation in my workbook.
I'm open to any ideas you guys may have. Thank you for your help and consideration.
Mon Aug 20, 2012 4:53 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
In general, the functions are non-volatile unless YOU make them volatile.
For example, by making one of the parameters volatile.
In any case, related to a macro, simply turn off recalculation when you
start the macro, and turn it back on when your macro is done doing whatever
it needs to do. But if your macro NEEDS recalculation at each step, I think
that will be a problem.
On Mon, Aug 20, 2012 at 3:40 PM, curls07 <agc11d@gmail.com > wrote:
>
> When I run a macro in a worksheet that contains rch get element functions,
> the functions recalculate every time the macro copies or pastes or
> otherwise performs and action on the spread sheet. The Macro has to wait
> for the elements to recalculate even though they aren't getting any new
> information.This makes my macro run much slower than it has to because of
> the unnecessary calculations.
>
> Has anyone else had this problem and figured out some way to control or
> minimize it? I've experimented with manual calculation, but that makes
> greater problems with the calculations done by my macro.
>
> I'm using the newest version of SMF addin in excel 2010 and I am using
> automatic calculation in my workbook.
>
> I'm open to any ideas you guys may have. Thank you for your help and
> consideration.
>
>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
For example, by making one of the parameters volatile.
In any case, related to a macro, simply turn off recalculation when you
start the macro, and turn it back on when your macro is done doing whatever
it needs to do. But if your macro NEEDS recalculation at each step, I think
that will be a problem.
On Mon, Aug 20, 2012 at 3:40 PM, curls07 <agc11d@gmail.
>
> When I run a macro in a worksheet that contains rch get element functions,
> the functions recalculate every time the macro copies or pastes or
> otherwise performs and action on the spread sheet. The Macro has to wait
> for the elements to recalculate even though they aren't getting any new
> information.
> the unnecessary calculations.
>
> Has anyone else had this problem and figured out some way to control or
> minimize it? I've experimented with manual calculation, but that makes
> greater problems with the calculations done by my macro.
>
> I'm using the newest version of SMF addin in excel 2010 and I am using
> automatic calculation in my workbook.
>
> I'm open to any ideas you guys may have. Thank you for your help and
> consideration.
>
>
>
>
>
>
> ------------
>
> Yahoo! Groups Links
>
>
>
>
Mon Aug 20, 2012 5:04 pm (PDT) . Posted by:
"zarathustra_winced@yahoo.com" zarathustra_winced
I couldn't imagine calculating some of my worksheets with an rchgetelementnumber in each cell. It would be agonizingly slow. If you're using a macro to calculate your sheet in the first place, you can turn off automatic recalc with the vba command, get elements via the rchgetelementnumber function using code, store the elements in variables, write them all to cells on the sheet as values rather than functions, and then turn on recalculation. Much, much faster.
Sent via BlackBerry by AT&T
-----Original Message-----
From: Randy Harmelink <rharmelink@gmail.com >
Sender: smf_addin@yahoogroups.com
Date: Mon, 20 Aug 2012 16:53:53
To: <smf_addin@yahoogroups.com >
Reply-To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Minimizing or controlling SMF Addin calculations in a macro
In general, the functions are non-volatile unless YOU make them volatile.
For example, by making one of the parameters volatile.
In any case, related to a macro, simply turn off recalculation when you
start the macro, and turn it back on when your macro is done doing whatever
it needs to do. But if your macro NEEDS recalculation at each step, I think
that will be a problem.
On Mon, Aug 20, 2012 at 3:40 PM, curls07 <agc11d@gmail.com > wrote:
>
> When I run a macro in a worksheet that contains rch get element functions,
> the functions recalculate every time the macro copies or pastes or
> otherwise performs and action on the spread sheet. The Macro has to wait
> for the elements to recalculate even though they aren't getting any new
> information.This makes my macro run much slower than it has to because of
> the unnecessary calculations.
>
> Has anyone else had this problem and figured out some way to control or
> minimize it? I've experimented with manual calculation, but that makes
> greater problems with the calculations done by my macro.
>
> I'm using the newest version of SMF addin in excel 2010 and I am using
> automatic calculation in my workbook.
>
> I'm open to any ideas you guys may have. Thank you for your help and
> consideration.
>
>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
Sent via BlackBerry by AT&T
-----Original Message-----
From: Randy Harmelink <rharmelink@gmail.
Sender: smf_addin@yahoogrou
Date: Mon, 20 Aug 2012 16:53:53
To: <smf_addin@yahoogrou
Reply-To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Minimizing or controlling SMF Addin calculations in a macro
In general, the functions are non-volatile unless YOU make them volatile.
For example, by making one of the parameters volatile.
In any case, related to a macro, simply turn off recalculation when you
start the macro, and turn it back on when your macro is done doing whatever
it needs to do. But if your macro NEEDS recalculation at each step, I think
that will be a problem.
On Mon, Aug 20, 2012 at 3:40 PM, curls07 <agc11d@gmail.
>
> When I run a macro in a worksheet that contains rch get element functions,
> the functions recalculate every time the macro copies or pastes or
> otherwise performs and action on the spread sheet. The Macro has to wait
> for the elements to recalculate even though they aren't getting any new
> information.
> the unnecessary calculations.
>
> Has anyone else had this problem and figured out some way to control or
> minimize it? I've experimented with manual calculation, but that makes
> greater problems with the calculations done by my macro.
>
> I'm using the newest version of SMF addin in excel 2010 and I am using
> automatic calculation in my workbook.
>
> I'm open to any ideas you guys may have. Thank you for your help and
> consideration.
>
>
>
>
>
>
> ------------
>
> Yahoo! Groups Links
>
>
>
>
Tidak ada komentar:
Posting Komentar