Jumat, 17 Januari 2014

[smf_addin] Digest Number 2931

13 New Messages

Digest #2931
1a
Re: Windows 8.1 Add In Location for Skydrive by "ridgebacksexcel" ridgebacksexcel
1d
Re: Windows 8.1 Add In Location for Skydrive by "Randy Harmelink" rharmelink
1f
Re: Windows 8.1 Add In Location for Skydrive by "ridgebacksexcel" ridgebacksexcel
2c
Re: Note on YAHOO Data Response Failures by "Kermit W. Prather" kermitpra
3a
Re: Need help with RCHGetHTMLTable by "Kermit W. Prather" kermitpra
3b
Re: Need help with RCHGetHTMLTable by "Randy Harmelink" rharmelink

Messages

Thu Jan 16, 2014 5:20 am (PST) . Posted by:

"ridgebacksexcel" ridgebacksexcel

Randy,

I did locate your macro and ran it to no affect. I also did the find and replace which does the same thing as edit the link.

This only fixes the problem one time. The next time you open the Excel file it breaks the link. It must be a Microsoft thing as you suggested.

I also put the Add In folder in the same place as the spreadsheets in the Skydrive folder on my computer. I was really hoping that would work but for some reason the add in will not function when located in the Skydrive folder. It must be blocking it somehow. Located anywhere else on my computer if runs fine.

So it seems the only fix although not perfect is this:

I have my spreadsheets running your add in located in My Documents folder OUTSIDE of the Skydrive folder. The links on my desktop point to this folder. When Excel runs it also opens the add in normally. No edit links required. Saving the file saves it to My Documents folder outside of Skydrive. I then have to also "save as" a copy to my Skydrive folder. I also have to only open the file from My Documents OUTSIDE of the skydrive folder. This is the workaround. Not ideal but I do not know another way.

But at least I have a copy of these important Excel files on the cloud now where they are safe.

Thanks for the work on the add in. It is awesome and saves me tons of work.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> There is an smfFixLinks macro in the add-in. I have it assigned to a button
> on my EXCEL toolbar, so I can run it any time I open a workbook with
> location errors.
>
> It just does a "Find and Replace all" on the hard-coded location that EXCEL
> leaves on unresolved links.
>
> See the entry in the LINKS area of the group for some information on
> location errors and unresolved links.
>
> On Wed, Jan 15, 2014 at 4:24 AM, ridgebacksexcel <tmallen2@...>wrote:
>
> > >>>Can you just use the smfFixLinks macro?<<<
> >
> > What is this? I am not familiar with it.
> >
>

Thu Jan 16, 2014 8:25 am (PST) . Posted by:

mikemcq802

I am able to run spreadsheets directly from Skydrive and use the SMF add-in just fine - no location problems. I only tried with SMF on my local drive. I did not try with SMF on Skydrive. I am using Windows 8.1.

I first ran the spreadsheet from my local drive. It worked as expected without prompts for add-in location.

I then used cut/paste to move the spreadsheet to my Skydrive.

Starting the spreadsheet from Skydrive works exactly the same as when starting it when it was local. I get no prompt for location errors.

I also used "Save As" in Excel to create the copy on Skydrive and that also worked fine.

Thu Jan 16, 2014 8:29 am (PST) . Posted by:

mikemcq802

Oh, I should have noted I am using Excel 2013 on the Windows 8.1 machine that I used for this Skydrive test. A Surface Pro 2 in case anyone cares.

I am still using Excel 2010 on my main Windows7 desktop.

Thu Jan 16, 2014 8:31 am (PST) . Posted by:

"Randy Harmelink" rharmelink

I think the issue is that EXCEL won't resolve links when the link leads to
a remote drive. So if the add-in is located there, it can't resolve the
links when it opens the workbook, leaving you with location errors.

But, as you noted elsewhere, it may vary by EXCEL version. Even Windows 8.x
may make a difference...

On Thu, Jan 16, 2014 at 9:25 AM, <mikemcq802@yahoo.com> wrote:

>
> I am able to run spreadsheets directly from Skydrive and use the SMF
> add-in just fine - no location problems. I only tried with SMF on my local
> drive. I did not try with SMF on Skydrive. I am using Windows 8.1.
>
> I first ran the spreadsheet from my local drive. It worked as expected
> without prompts for add-in location.
>
> I then used cut/paste to move the spreadsheet to my Skydrive.
>
> Starting the spreadsheet from Skydrive works exactly the same as when
> starting it when it was local. I get no prompt for location errors.
>
> I also used "Save As" in Excel to create the copy on Skydrive and that
> also worked fine.
>

Thu Jan 16, 2014 9:48 am (PST) . Posted by:

mikemcq802

No, I think they have just not been thorough in their install. They also said they couldn't run an xls from Skydrive even with SMF installed locally - which I can do. I subsequently did another test and can also run an xls from Skydrive with SMF installed on Skydrive. You just have to ensure Excel is pointing to the correct location for the add-in - let me explain.

Skydrive just looks like another folder on your local machine. You end up with files named something like "c:\users\mike\skydrive&#92;smf addin\...xla". Excel does not have any trouble with that (nor would any other application).

If you want SMF and your own xls on Skydrive then you must make sure the add-in is installed in Excel naming the Skydrive location. You can check your install folder using File, Options, Add-ins, then highlight SMF to check its install folder.

To change the folder you must un-check SMF and add it back in with the correct location.

You will get links errors if your install folder does not match what is in your spreadsheet. But, once you use Excel to update the links location it will be fine. You can save and re-run the xls without further prompt. You will know you have this correct when you look at an smf function in your xls and it does not have any folder at all prefixing the function (relying on the Excel add-in install folder location).

However, if you get the warning about wrong links and you correct them by naming a folder that is NOT the Excel install folder for the add-in - you will perpetually be asked to correct them each time you start the xls. Which is what I believe is happening to them.

Thu Jan 16, 2014 12:45 pm (PST) . Posted by:

"ridgebacksexcel" ridgebacksexcel

Ok this is what I was looking to find out. It makes sense. I will try this later today or tomorrow when I get time. I think the key is uninstalling the add in from Excel and re-installing it in the new location. I missed that.

Thanks. I will report back on it.

--- In smf_addin@yahoogroups.com, <mikemcq802@...> wrote:
>
> No, I think they have just not been thorough in their install. They also said they couldn't run an xls from Skydrive even with SMF installed locally - which I can do. I subsequently did another test and can also run an xls from Skydrive with SMF installed on Skydrive. You just have to ensure Excel is pointing to the correct location for the add-in - let me explain.
>
> Skydrive just looks like another folder on your local machine. You end up with files named something like "c:\users\mike\skydrive&#92;smf addin\...xla". Excel does not have any trouble with that (nor would any other application).
>
> If you want SMF and your own xls on Skydrive then you must make sure the add-in is installed in Excel naming the Skydrive location. You can check your install folder using File, Options, Add-ins, then highlight SMF to check its install folder.
>
> To change the folder you must un-check SMF and add it back in with the correct location.
>
> You will get links errors if your install folder does not match what is in your spreadsheet. But, once you use Excel to update the links location it will be fine. You can save and re-run the xls without further prompt. You will know you have this correct when you look at an smf function in your xls and it does not have any folder at all prefixing the function (relying on the Excel add-in install folder location).
>
> However, if you get the warning about wrong links and you correct them by naming a folder that is NOT the Excel install folder for the add-in - you will perpetually be asked to correct them each time you start the xls. Which is what I believe is happening to them.
>

Thu Jan 16, 2014 7:05 am (PST) . Posted by:

cats1017

I looked in the files folder and I couldn't find an example of using an array formula - is there an example file in there?

Thu Jan 16, 2014 7:12 am (PST) . Posted by:

mikemcq802

Yep, look in:

Files
Then, Templates and Examples folder
Then, RCHGetYahooQuotes folder
Then, SMF-Template-RCHGetYahooQuotes.xls

Thu Jan 16, 2014 8:13 am (PST) . Posted by:

"Kermit W. Prather" kermitpra

Look at my posting today showing 2 solutions to my latest problem. (its pasted below)

The only tricky part of the array is the sequence of how you enter the array.
1. You must know the number of columns and rows you need to define for the array, defining too many is generally okay, defining too few you will lose part of the information
2. Left click your mouse on the starting cell of the array
3. While holding the left click move the mouse to decide the array's columns and rows
4. Release the left mouse click
5. Now without hitting the enter key – type in or paste the formula into the first cell of the array. Again, DO NOT hit the enter key
6. Select CTRL + Shift + Entry

Your array should be define and the content inserted.

If you are like me you don't create arrays everyday and we forget the process.

Good luck,


Using Mike and Randy suggested solutions worked but got slightly different results.

Thanks both of you.

Mike's Solution
=RCHGetHTMLTable("http://www.dividendchannel.com/dividend-rank/?a= <http://www.dividendchannel.com/dividend-rank/?a=&issuer=&symbol=&sortby=&reverse=&rpp=20&start=0> &issuer=&symbol=&sortby=&reverse=&rpp=20&start=0","dividendchannel.com/safe25",-1,"",1)

Results:

Top Ranked Dividend Stocks List - Page 1 of 161






Rank
Symbol
Dividend

Recent Yield*


#1
CIM
Q
0.36
11.9%


#2
MFA
Q
0.8
11.2%



Randy's solution:
=RCHGetHTMLTable("http://www.dividendchannel.com/dividend-rank/?a= <http://www.dividendchannel.com/dividend-rank/?a=&issuer=&symbol=&sortby=&reverse=&rpp=20&start=0> &issuer=&symbol=&sortby=&reverse=&rpp=20&start=0",">Recent Yield",-1,"",1)

Results:


Rank
Symbol
Dividend

Recent Yield*

#1
CIM
Q
0.36
11.9%

#2
MFA
Q
0.80
11.2%


I really appreciate both solutions. I am happy with either one.
On the webpage the header row has the frequency and dividend as a merged cell.
I'm not sure there is a way to change column heading to Freq / Dividend.

Any suggestion on how I fix that problem?

My solution was to hide the actual header row and add my own header row above the array.
It works but probably not the best solution.

Mike and Randy, Thanks again

Kermit



From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of cats1017@yahoo.com
Sent: Thursday, January 16, 2014 10:05 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] RE: Note on YAHOO Data Response Failures


I looked in the files folder and I couldn't find an example of using an array formula - is there an example file in there?

Thu Jan 16, 2014 7:15 am (PST) . Posted by:

"Kermit W. Prather" kermitpra

Using Mike and Randy suggested solutions worked but got slightly different results.

Thanks both of you.

Mike's Solution
=RCHGetHTMLTable("http://www.dividendchannel.com/dividend-rank/?a= <http://www.dividendchannel.com/dividend-rank/?a=&issuer=&symbol=&sortby=&reverse=&rpp=20&start=0> &issuer=&symbol=&sortby=&reverse=&rpp=20&start=0","dividendchannel.com/safe25",-1,"",1)

Results:

Top Ranked Dividend Stocks List - Page 1 of 161






Rank
Symbol
Dividend

Recent Yield*


#1
CIM
Q
0.36
11.9%


#2
MFA
Q
0.8
11.2%



Randy's solution:
=RCHGetHTMLTable("http://www.dividendchannel.com/dividend-rank/?a= <http://www.dividendchannel.com/dividend-rank/?a=&issuer=&symbol=&sortby=&reverse=&rpp=20&start=0> &issuer=&symbol=&sortby=&reverse=&rpp=20&start=0",">Recent Yield",-1,"",1)

Results:


Rank
Symbol
Dividend

Recent Yield*

#1
CIM
Q
0.36
11.9%

#2
MFA
Q
0.80
11.2%


I really appreciate both solutions. I am happy with either one.
On the webpage the header row has the frequency and dividend as a merged cell.
I'm not sure there is a way to change column heading to Freq / Dividend.

Any suggestion on how I fix that problem?

My solution was to hide the actual header row and add my own header row above the array.
It works but probably not the best solution.

Mike and Randy, Thanks again

Kermit


From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Wednesday, January 15, 2014 8:40 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Need help with RCHGetHTMLTable


As noted, not a good search term. And you're going backward from it, when it's actually outside of the table? I usually try to use a column heading within the table, if I can make it unique, and then go backwards and forwards from it. For example:

=RCHGetHTMLTable("http://www.dividendchannel.com/dividend-rank/?a= <http://www.dividendchannel.com/dividend-rank/?a=&issuer=&symbol=&sortby=&reverse=&rpp=20&start=0> &issuer=&symbol=&sortby=&reverse=&rpp=20&start=0",">Recent Yield",-1,"",1)

On Wed, Jan 15, 2014 at 5:45 PM, Kermit W. Prather <kermitp@tampabay.rr.com> wrote:

Randy, I thought I knew how to do this but have been fighting it for a few hours. I know the link is correct because if I enter it into a cell and click on it I get the proper webpage displayed.


I created the array starting in Cell a3 thru e30 with the formula below. I checked and each cell has this value. So it appears the array was created.
But all the cells display nothing.
They look like this: empty. So my formula most be wrong but I am stump as to what formula to put in the array

I tried several other version of he formula and all came up the same blank table. Apparently, I am not understanding how to read the HTML source and enter the array formula correctly.


=RCHGetHTMLTable("http://www.dividendchannel.com/dividend-rank/?a= <http://www.dividendchannel.com/dividend-rank/?a=&issuer=&symbol=&sortby=&reverse=&rpp=20&start=0> &issuer=&symbol=&sortby=&reverse=&rpp=20&start=0","Top Ranked Dividend Stocks List",-1,"",1)


Thu Jan 16, 2014 8:15 am (PST) . Posted by:

"Randy Harmelink" rharmelink

The only way to do those things is to build the table yourself using the
RCHGetTableCell() function to retrieve each individual item. Then you can
customize whatever you need.

On Thu, Jan 16, 2014 at 8:15 AM, Kermit W. Prather
<kermitp@tampabay.rr.com>wrote:

>
> *I really appreciate both solutions. I am happy with either one. *
>
> *On the webpage the header row has the frequency and dividend as a merged
> cell. *
>
> *I'm not sure there is a way to change column heading to Freq / Dividend.*
>
>
>
> *Any suggestion on how I fix that problem?*
>
>
>
> *My solution was to hide the actual header row and add my own header row
> above the array.*
>
> *It works but probably not the best solution. *
>
>
>

Thu Jan 16, 2014 9:56 am (PST) . Posted by:

"Kermit W. Prather" kermitpra

I was setting up a button to assign the smfForceRecalculation macro so I can
force a recalculation when I want to refresh my spreadsheet.

I normally have more than one open spreadsheet with function using the
SMF-Addin thus my question.

Does the smfForceRecalculation refresh all open spreadsheets or just the
active one?

Thanks,
Kermit

Thu Jan 16, 2014 11:58 am (PST) . Posted by:

"Randy Harmelink" rharmelink

It recalculates ALL open workbooks.

On Thu, Jan 16, 2014 at 10:56 AM, Kermit W. Prather <kermitp@tampabay.rr.com
> wrote:

>
> I was setting up a button to assign the smfForceRecalculation macro so I
> can force a recalculation when I want to refresh my spreadsheet.
>
>
>
> I normally have more than one open spreadsheet with function using the
> SMF-Addin thus my question.
>
>
>
> Does the smfForceRecalculation refresh all open spreadsheets or just the
> active one?
>
>
>

Tidak ada komentar:

Posting Komentar