Sabtu, 30 April 2016

[smf_addin] Digest Number 3687

2 Messages

Digest #3687
1a
Re: Wharton Ratio Analysis Model by "Randy Harmelink" rharmelink
1b
Re: Wharton Ratio Analysis Model by "Kermit W. Prather" kermitpra

Messages

Fri Apr 29, 2016 2:17 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I'm not willing to do it, sorry. It would be a lot of tedious work for
something I wouldn't use.

On Fri, Apr 29, 2016 at 1:42 AM, andre.heggli@
​...wrote:

>
> Hi, I thought I would adapt it using the plug-in so that it updates the
> information based on each stock. The info in there is just hardcoded as an
> example.
>
> Would you know how to do it? I think it could be quite a useful tool.
>
>

Fri Apr 29, 2016 3:31 pm (PDT) . Posted by:

"Kermit W. Prather" kermitpra

Do you have URL for where you derived the spreadsheet?
Need more information to know where to start.
I may not have the skills but willing to take a look at the task.

So gather all the info you have a put it in a posting. Just the spreadsheet is of no value.

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Friday, April 29, 2016 4:42 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Wharton Ratio Analysis Model


Hi, I thought I would adapt it using the plug-in so that it updates the information based on each stock. The info in there is just hardcoded as an example.

Would you know how to do it? I think it could be quite a useful tool.

For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
READ MORE....
Diposting oleh EXCEL FEED LEARNING di 02.04 0 komentar

Jumat, 29 April 2016

[smf_addin] Digest Number 3686[2 Attachments]

6 Messages

Digest #3686

Messages

Thu Apr 28, 2016 6:58 am (PDT) . Posted by:

cs22kz

Hi, I would like to use the ratio analysis template from the wharton coursera course on Financial Analyis.

Would someone know how to make it work with the plug-in?

Best regards
A


Attachment(s) from
1 of 1 File(s)

Thu Apr 28, 2016 4:06 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

As long as you can find a source for each input data item, you should be
able to use the add-in to populate the workbook. But that won't be a small
task...

Do they type in each item? From where? If online, just pull it
automatically.

On Thu, Apr 28, 2016 at 5:43 AM, andre.heggli@
​...wrote:

>
> Hi, I would like to use the ratio analysis template from the wharton
> coursera course on Financial Analyis.
>
> Would someone know how to make it work with the plug-in?
>
> Best regards
> A
>
> Attachment(s) from andre.heggli@hotmail.com [smf_addin] | View
> attachments on the web
> <https://groups.yahoo.com/neo/groups/smf_addin/attachments/1022083464;_ylc=X3oDMTJyOWxzOGl0BF9TAzk3MzU5NzE0BGdycElkAzE4MDk0NjIwBGdycHNwSWQDMTcwNTYzMjE5OARzZWMDYXR0YWNobWVudARzbGsDdmlld09uV2ViBHN0aW1lAzE0NjE4NTE5MTc->
>
> 1 of 1 File(s)
> coursera Wharton valuation model.xlsx
> <https://xa.yimg.com/kq/groups/18094620/1267079590/name/coursera%20Wharton%20valuation%20model%2Exlsx>
> ------------------------------
>
>

Fri Apr 29, 2016 1:42 am (PDT) . Posted by:

cs22kz

Hi, I thought I would adapt it using the plug-in so that it updates the information based on each stock. The info in there is just hardcoded as an example.

Would you know how to do it? I think it could be quite a useful tool.

Thu Apr 28, 2016 8:06 am (PDT) . Posted by:

cs22kz

Hi, I wanted to try using the ratio analysis model from the Wharton Course on financial analysis, but not sure I use the plug-in correctly.

Would someone be willing to have a look at the spreadsheet and see if they could adapt it to the plug-in?





Best regards


Attachment(s) from
1 of 1 File(s)

Thu Apr 28, 2016 7:16 pm (PDT) . Posted by:

"Ron Spruell" hashky

I must be doing something wrong. I put in the JSON URL:"http://www.cefconnect.com/api/v3/DailyPricing?props=Ticker,Name,DistributionRateNAV,LastUpdated,Discount,DistributionRatePrice,ReturnOnNAV,CategoryId,CategoryName,IsManagedDistribution,Price,PriceChange,NAV,NAVPublished,Cusip/

in Cell D7.
I put in "{" in D8.
I don't get the site broken down as it should be. Under Find Nest I get 32767.
Ron

From: "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com>
To: smf_addin@yahoogroups.com
Sent: Wednesday, April 20, 2016 4:28 PM
Subject: Re: [smf_addin] CEFconnect.com [Solved]

  There are so many ways to go about it, that it depends so much on what you're trying to extract and how you're planning to use it. For example, try this:

1. Open the SMF-Quick-Webpage-Examination.xls template.
2. Go to the "By Cascading Value" worksheet
3. Enter your URL in the "Web Page" entry cell
4. Enter "{" into the "String" entry cell

The result should be the entire file broken up line by line. I see 553 different lines.

Then, you could add columns for each field you want to extract, using the column header to do the extraction from that column of data, using the smfStrExtr() function.

Depends on whether you want to build a table, or do it ticker by ticker. However, if you do build the table, you can always use VLOOKUP() to find the individual pieces of data you're interested in.

On Wed, Apr 20, 2016 at 1:42 PM, hashky@yahoo.com [smf_addin] <smf_addin@yahoogroups.com> wrote:

Is there an easier way to do the following?

I got the spreadsheet going.  Below are the typical equations for text and value fields.  As I looked at the data, it seems that "{" and "}" designated a change database records.  So I did a string extract (smfStrExtr) to limit the returns to text between the brackets.  This way I am assured that I have the information is for the right  Ticker.

Typical Ticker VMO
Text
=(smfStrExtr(smfStrExtr(RCHGetWebData("http://www.cefconnect.com/api/v3/DailyPricing?props=Ticker,Name,DistributionRateNAV,LastUpdated,Discount,DistributionRatePrice,ReturnOnNAV,CategoryId,CategoryName,IsManagedDistribution,Price,PriceChange,NAV,NAVPublished,Cusip/","""Ticker"":"""&B93&"""",400,-1),"{","}"),"""Name"":",","))

Values
=smfConvertData(smfStrExtr(smfStrExtr(RCHGetWebData("http://www.cefconnect.com/api/v3/DailyPricing?props=Ticker,Name,DistributionRateNAV,LastUpdated,Discount,DistributionRatePrice,ReturnOnNAV,CategoryId,CategoryName,IsManagedDistribution,Price,PriceChange,NAV,NAVPublished,Cusip/","""Ticker"":"""&B93&"""",400,-1),"{","}"),"""DistributionRatePrice";":",","))

There is probably very little interest in the group in closed end funds.  They have more risk than open end funds.  If anyone wants a copy I will post.

#yiv5584094062 #yiv5584094062 -- #yiv5584094062ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5584094062 #yiv5584094062ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5584094062 #yiv5584094062ygrp-mkp #yiv5584094062hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv5584094062 #yiv5584094062ygrp-mkp #yiv5584094062ads {margin-bottom:10px;}#yiv5584094062 #yiv5584094062ygrp-mkp .yiv5584094062ad {padding:0 0;}#yiv5584094062 #yiv5584094062ygrp-mkp .yiv5584094062ad p {margin:0;}#yiv5584094062 #yiv5584094062ygrp-mkp .yiv5584094062ad a {color:#0000ff;text-decoration:none;}#yiv5584094062 #yiv5584094062ygrp-sponsor #yiv5584094062ygrp-lc {font-family:Arial;}#yiv5584094062 #yiv5584094062ygrp-sponsor #yiv5584094062ygrp-lc #yiv5584094062hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5584094062 #yiv5584094062ygrp-sponsor #yiv5584094062ygrp-lc .yiv5584094062ad {margin-bottom:10px;padding:0 0;}#yiv5584094062 #yiv5584094062actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5584094062 #yiv5584094062activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5584094062 #yiv5584094062activity span {font-weight:700;}#yiv5584094062 #yiv5584094062activity span:first-child {text-transform:uppercase;}#yiv5584094062 #yiv5584094062activity span a {color:#5085b6;text-decoration:none;}#yiv5584094062 #yiv5584094062activity span span {color:#ff7900;}#yiv5584094062 #yiv5584094062activity span .yiv5584094062underline {text-decoration:underline;}#yiv5584094062 .yiv5584094062attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv5584094062 .yiv5584094062attach div a {text-decoration:none;}#yiv5584094062 .yiv5584094062attach img {border:none;padding-right:5px;}#yiv5584094062 .yiv5584094062attach label {display:block;margin-bottom:5px;}#yiv5584094062 .yiv5584094062attach label a {text-decoration:none;}#yiv5584094062 blockquote {margin:0 0 0 4px;}#yiv5584094062 .yiv5584094062bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv5584094062 .yiv5584094062bold a {text-decoration:none;}#yiv5584094062 dd.yiv5584094062last p a {font-family:Verdana;font-weight:700;}#yiv5584094062 dd.yiv5584094062last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5584094062 dd.yiv5584094062last p span.yiv5584094062yshortcuts {margin-right:0;}#yiv5584094062 div.yiv5584094062attach-table div div a {text-decoration:none;}#yiv5584094062 div.yiv5584094062attach-table {width:400px;}#yiv5584094062 div.yiv5584094062file-title a, #yiv5584094062 div.yiv5584094062file-title a:active, #yiv5584094062 div.yiv5584094062file-title a:hover, #yiv5584094062 div.yiv5584094062file-title a:visited {text-decoration:none;}#yiv5584094062 div.yiv5584094062photo-title a, #yiv5584094062 div.yiv5584094062photo-title a:active, #yiv5584094062 div.yiv5584094062photo-title a:hover, #yiv5584094062 div.yiv5584094062photo-title a:visited {text-decoration:none;}#yiv5584094062 div#yiv5584094062ygrp-mlmsg #yiv5584094062ygrp-msg p a span.yiv5584094062yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5584094062 .yiv5584094062green {color:#628c2a;}#yiv5584094062 .yiv5584094062MsoNormal {margin:0 0 0 0;}#yiv5584094062 o {font-size:0;}#yiv5584094062 #yiv5584094062photos div {float:left;width:72px;}#yiv5584094062 #yiv5584094062photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv5584094062 #yiv5584094062photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5584094062 #yiv5584094062reco-category {font-size:77%;}#yiv5584094062 #yiv5584094062reco-desc {font-size:77%;}#yiv5584094062 .yiv5584094062replbq {margin:4px;}#yiv5584094062 #yiv5584094062ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv5584094062 #yiv5584094062ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5584094062 #yiv5584094062ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5584094062 #yiv5584094062ygrp-mlmsg select, #yiv5584094062 input, #yiv5584094062 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv5584094062 #yiv5584094062ygrp-mlmsg pre, #yiv5584094062 code {font:115% monospace;}#yiv5584094062 #yiv5584094062ygrp-mlmsg * {line-height:1.22em;}#yiv5584094062 #yiv5584094062ygrp-mlmsg #yiv5584094062logo {padding-bottom:10px;}#yiv5584094062 #yiv5584094062ygrp-msg p a {font-family:Verdana;}#yiv5584094062 #yiv5584094062ygrp-msg p#yiv5584094062attach-count span {color:#1E66AE;font-weight:700;}#yiv5584094062 #yiv5584094062ygrp-reco #yiv5584094062reco-head {color:#ff7900;font-weight:700;}#yiv5584094062 #yiv5584094062ygrp-reco {margin-bottom:20px;padding:0px;}#yiv5584094062 #yiv5584094062ygrp-sponsor #yiv5584094062ov li a {font-size:130%;text-decoration:none;}#yiv5584094062 #yiv5584094062ygrp-sponsor #yiv5584094062ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv5584094062 #yiv5584094062ygrp-sponsor #yiv5584094062ov ul {margin:0;padding:0 0 0 8px;}#yiv5584094062 #yiv5584094062ygrp-text {font-family:Georgia;}#yiv5584094062 #yiv5584094062ygrp-text p {margin:0 0 1em 0;}#yiv5584094062 #yiv5584094062ygrp-text tt {font-size:120%;}#yiv5584094062 #yiv5584094062ygrp-vital ul li:last-child {border-right:none !important;}#yiv5584094062

Thu Apr 28, 2016 7:31 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Hmmm. Mine looks like this:

*Web Page:*
http://www.cefconnect.com/api/v3/DailyPricing?props=Ticker,Name,DistributionRateNAV,LastUpdated,Discount,DistributionRatePrice,ReturnOnNAV,CategoryId,CategoryName,IsManagedDistribution,Price,PriceChange,NAV,NAVPublished,Cusip/
*String:* {

*Find Next* *Start* *Web page content*
*--* *1* [{"Ticker":"MFM","Name":"MFS Municipal
Income","DistributionRateNAV":5.09,"LastUpdated":"2016-04-27T00:00:00","Discount":-3.97,"DistributionRatePrice":5.30,"ReturnOnNAV":7.43,"CategoryId":1,"CategoryName":"Tax-Free
Income-High
Yield","IsManagedDistribution":false,"Price":7.25000,"PriceChange":-0.06,"NAV":7.550000,"NAVPublished":"2016-04-27T00:00:00"},{"Ticker":"CXE","Name":"MFS
High Income
Municipal","DistributionRateNAV":5.44,"LastUpdated":"2016-04-27T00:00:00","Discount":-4.54,"DistributionRat
*2* *2* {"Ticker":"MFM","Name":"MFS Municipal
Income","DistributionRateNAV":5.09,"LastUpdated":"2016-04-27T00:00:00","Discount":-3.97,"DistributionRatePrice":5.30,"ReturnOnNAV":7.43,"CategoryId":1,"CategoryName":"Tax-Free
Income-High
Yield","IsManagedDistribution":false,"Price":7.25000,"PriceChange":-0.06,"NAV":7.550000,"NAVPublished":"2016-04-27T00:00:00"},{"Ticker":"CXE","Name":"MFS
High Income
Municipal","DistributionRateNAV":5.44,"LastUpdated":"2016-04-27T00:00:00","Discount":-4.54,"DistributionRate
*353* *354* {"Ticker":"CXE","Name":"MFS High Income
Municipal","DistributionRateNAV":5.44,"LastUpdated":"2016-04-27T00:00:00","Discount":-4.54,"DistributionRatePrice":5.70,"ReturnOnNAV":7.58,"CategoryId":1,"CategoryName":"Tax-Free
Income-High
Yield","IsManagedDistribution":false,"Price":5.26000,"PriceChange":-0.03,"NAV":5.510000,"NAVPublished":"2016-04-27T00:00:00"},{"Ticker":"CMU","Name":"MFS
High Yield Municipal
Trust","DistributionRateNAV":5.58,"LastUpdated":"2016-04-27T00:00:00","Discount":-1.91,"Distri
*358* *711* {"Ticker":"CMU","Name":"MFS High Yield Municipal
Trust","DistributionRateNAV":5.58,"LastUpdated":"2016-04-27T00:00:00","Discount":-1.91,"DistributionRatePrice":5.68,"ReturnOnNAV":7.49,"CategoryId":1,"CategoryName":"Tax-Free
Income-High
Yield","IsManagedDistribution":false,"Price":4.85570,"PriceChange":-0.01,"NAV":4.950000,"NAVPublished":"2016-04-27T00:00:00"},{"Ticker":"NMZ","Name":"Nuveen
Muni High Inc
Opp","DistributionRateNAV":6.58,"LastUpdated":"2016-04-27T00:00:00","Discount":2.02,"Distribu
*363* *1073* {"Ticker":"NMZ","Name":"Nuveen Muni High Inc
Opp","DistributionRateNAV":6.58,"LastUpdated":"2016-04-27T00:00:00","Discount":2.02,"DistributionRatePrice":6.45,"ReturnOnNAV":6.91,"CategoryId":1,"CategoryName":"Tax-Free
Income-High
Yield","IsManagedDistribution":false,"Price":14.14000,"PriceChange":0.00,"NAV":13.860000,"NAVPublished":"2016-04-27T00:00:00"},{"Ticker":"MAV","Name":"Pioneer
Muni High Inc
Adv","DistributionRateNAV":6.69,"LastUpdated":"2016-04-27T00:00:00","Discount":11.71,"Distribution

On Thu, Apr 28, 2016 at 7:13 PM, Ron Spruell hashky@
​...wrote:

>
>
> I must be doing something wrong. I put in the JSON URL:
> "
> http://www.cefconnect.com/api/v3/DailyPricing?props=Ticker,Name,DistributionRateNAV,LastUpdated,Discount,DistributionRatePrice,ReturnOnNAV,CategoryId,CategoryName,IsManagedDistribution,Price,PriceChange,NAV,NAVPublished,Cusip/
>
> in Cell D7.
>
> I put in "{" in D8.
>
> I don't get the site broken down as it should be. Under Find Nest I get
> 32767.
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
READ MORE....
Diposting oleh EXCEL FEED LEARNING di 01.59 0 komentar

Kamis, 28 April 2016

[belajar-excel] Digest Number 3837

7 Messages

Digest #3837

Messages

Wed Apr 27, 2016 2:13 am (PDT) . Posted by:

"Nang Agus"

wa alaikumsalam warahmatullah.
Bagaimana kalau menggunakan nama aktenya saja, bukan nama dinas ke workbook
seperti pesan mas KId di sini
<http://b-excel.blogspot.co.id/2013/06/belajarvba-001-vbe-visual-basic-editor.html>

coba gunakan cekrip:
Dim w As Worksheet
For Each w In ActiveWorkbook.Worksheets
w.Visible = xlSheetVisible
Next w

salam

KenangAgus

2016-04-27 13:27 GMT+07:00 Darwis Arief d_arief72@yahoo.com [belajar-excel]
<belajar-excel@yahoogroups.com>:

>
>
> Aslm,
> Para pakar Excel yg budiman,
> Aku punya Skrip untuk unhide sheets sbb:
> Sheets("AA-1").Visible = xlSheetVisible
> Saya ingin mengganti menjadi:
> Sheets("*"&"-1").Visible = xlSheetVisible namun terjadi Debug
> Saya menginginkan demikian berhubung nama sheet sering berubah sesuai
> inputan dicell tertentu.
>
> Adakah Skrip yang salah?
> Mohon pencerahannya.
>
> Wassalam,
> Arief
>
>
>

Wed Apr 27, 2016 3:04 am (PDT) . Posted by:

"Darwis Arief" d_arief72

Makasih masukannya mas KenangAgus.Kami sdh menggunakan skrip itu diatasx jd ada bbrp sheet sj yg sy ingin tampilkn yg laen msh sy hide. Wassalam,Arief

Sent from Yahoo Mail on Android

On Wed, Apr 27, 2016 at 17:13, Nang Agus nangagus@gmail.com [belajar-excel]<belajar-excel@yahoogroups.com> wrote:  

wa alaikumsalam warahmatullah.
Bagaimana kalau menggunakan nama aktenya saja, bukan nama dinas ke workbook seperti pesan mas KId di sini

coba gunakan cekrip:
Dim w As Worksheet
For Each w In ActiveWorkbook.Worksheets
w.Visible = xlSheetVisible
Next w

salam

KenangAgus

2016-04-27 13:27 GMT+07:00 Darwis Arief d_arief72@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

 

Aslm,Para pakar Excel yg budiman,Aku punya Skrip untuk unhide sheets sbb:Sheets("AA-1").Visible = xlSheetVisibleSaya ingin mengganti menjadi:Sheets("*"&"-1").Visible = xlSheetVisible namun terjadi Debug
Saya menginginkan demikian berhubung nama sheet sering berubah sesuai inputan dicell tertentu.
Adakah Skrip yang salah?Mohon pencerahannya.
Wassalam,Arief

Wed Apr 27, 2016 6:44 pm (PDT) . Posted by:

"Nang Agus"

Coba tambahkan cekrip untuk menyembunyikan:

Private Sub Workbook_Open()
Dim w As Worksheet
For Each w In ActiveWorkbook.Worksheets
If w.Name <> "1" Then w.Visible = xlSheetVeryHidden
Next w
End Sub

yang disembunyikan adalah semua sheet kecuali sheet dengan nama "1"
salam

2016-04-27 16:39 GMT+07:00 Darwis Arief d_arief72@yahoo.com [belajar-excel]
<belajar-excel@yahoogroups.com>:

>
>
> Makasih masukannya mas KenangAgus.
> Kami sdh menggunakan skrip itu diatasx jd ada bbrp sheet sj yg sy ingin
> tampilkn yg laen msh sy hide.
> Wassalam,
> Arief
>
> Sent from Yahoo Mail on Android
> <https://overview.mail.yahoo.com/mobile/?.src=Android>
>
>
>
> wa alaikumsalam warahmatullah.
> Bagaimana kalau menggunakan nama aktenya saja, bukan nama dinas ke
> workbook seperti pesan mas KId di sini
> <http://b-excel.blogspot.co.id/2013/06/belajarvba-001-vbe-visual-basic-editor.html>
>
> coba gunakan cekrip:
> Dim w As Worksheet
> For Each w In ActiveWorkbook.Worksheets
> w.Visible = xlSheetVisible
> Next w
>
> salam
>
> KenangAgus
>
> 2016-04-27 13:27 GMT+07:00 Darwis Arief d_arief72@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>>
>>
>> Aslm,
>> Para pakar Excel yg budiman,
>> Aku punya Skrip untuk unhide sheets sbb:
>> Sheets("AA-1").Visible = xlSheetVisible
>> Saya ingin mengganti menjadi:
>> Sheets("*"&"-1").Visible = xlSheetVisible namun terjadi Debug
>> Saya menginginkan demikian berhubung nama sheet sering berubah sesuai
>> inputan dicell tertentu.
>>
>> Adakah Skrip yang salah?
>> Mohon pencerahannya.
>>
>> Wassalam,
>> Arief
>>
>>
>
>

Wed Apr 27, 2016 11:25 pm (PDT) . Posted by:

"Darwis Arief" d_arief72

Terima ksh banyak atas bantuannya.Semoga ilmunya menjadi amal jariah disisi Allah SWT. Amin.WslmArief

Sent from Yahoo Mail on Android

On Thu, Apr 28, 2016 at 9:43, Nang Agus nangagus@gmail.com [belajar-excel]<belajar-excel@yahoogroups.com> wrote:  

Coba tambahkan cekrip untuk menyembunyikan:

Private Sub Workbook_Open()
Dim w As Worksheet
For Each w In ActiveWorkbook.Worksheets
If w.Name <> "1" Then w.Visible = xlSheetVeryHidden
Next w
End Sub

yang disembunyikan adalah semua sheet kecuali sheet dengan nama "1"
salam

2016-04-27 16:39 GMT+07:00 Darwis Arief d_arief72@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

 
Makasih masukannya mas KenangAgus.
Kami sdh menggunakan skrip itu diatasx jd ada bbrp sheet sj yg sy ingin tampilkn yg laen msh sy hide. Wassalam,Arief

Sent from Yahoo Mail on Android

 

wa alaikumsalam warahmatullah.

Bagaimana kalau menggunakan nama aktenya saja, bukan nama dinas ke workbook seperti pesan mas KId di sini

coba gunakan cekrip:
Dim w As Worksheet
For Each w In ActiveWorkbook.Worksheets
w.Visible = xlSheetVisible
Next w

salam

KenangAgus

2016-04-27 13:27 GMT+07:00 Darwis Arief d_arief72@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

 

Aslm,Para pakar Excel yg budiman,Aku punya Skrip untuk unhide sheets sbb:Sheets("AA-1").Visible = xlSheetVisibleSaya ingin mengganti menjadi:Sheets("*"&"-1").Visible = xlSheetVisible namun terjadi Debug
Saya menginginkan demikian berhubung nama sheet sering berubah sesuai inputan dicell tertentu.
Adakah Skrip yang salah?Mohon pencerahannya.
Wassalam,Arief

Wed Apr 27, 2016 6:04 am (PDT) . Posted by:

"Insan Biasa"

Terimakasih Mr. Kid untuk arahannya. Saya sudah pelajari comand SQL nya dan

saya sudah berhasil melakukan penggabungan data sheet dengan *fitur get
external data *excel. Command SQL yang saya gunakan adalah :

SELECT * FROM [dt1$] Where Nomor LIKE '[0-9]%';
UNION ALL
SELECT * FROM [dt2$] Where Nomor LIKE '[0-9]%';
UNION ALL
SELECT * FROM [dt3$] Where Nomor LIKE '[0-9]%';
UNION ALL
SELECT * FROM [dt4$] Where Nomor LIKE '[0-9]%';
UNION ALL
SELECT * FROM [dt5$] Where Nomor LIKE '[0-9]%';
UNION ALL
SELECT * FROM [dt6$] Where Nomor LIKE '[0-9]%';

Kendala : - Jika menggunakan 3 sheet atau lebih muncul notif error *"Data
Could not be retrieved from the database. Check the database or contcat
your database administrator. Make sure the external database is available,
and then try the opertion again"*

jika hanya mengumpulkan 2 sheet saja atau menggunakan, data berhasil di
gabungkan. mohon pencerahannya kembali Mr. Kid.

2016-04-24 7:48 GMT+07:00 'Mr. Kid' mr.nmkid@gmail.com [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> Belajar query-nya bisa disini <http://www.w3schools.com/sql/>.
>
> Regards,
> Kid
>
> 2016-04-21 13:39 GMT+07:00 Insan Biasa dodybireuen@gmail.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>>
>>
>> Terimakasih Mr Kid untuk pencerahannya, saya sudah coba menggunakan fitur
>> Get External Data, tapi saya masih terkendala dengan command text nya.
>> Saya masih gagal paham dengan perintah berikut :
>>
>> *SELECT 'dt1' AS [sumber],a.[kol1],a.[kol2],a.[kol3],a.[kol4] FROM [dt1$]
>> AS a*
>> *UNION ALL*
>> *SELECT 'dt2' AS [sumber],a.[kol1],a.[kol2],a.[kol3],a.[kol4] FROM [dt2$]
>> AS a*
>>
>> Jujur saya baru kali ini bersentuhan dengan yang namanya fitur Get
>> External Data, dan dari yang sudah saya pelajari akan sangat banyak manfaat
>> dari fitur Get External Data ini. Saya mohon bantuan Mr Kid untuk perintah
>> (bunyi query) yang harus saya isikan pada bagian *Command Text*, jika
>> saya punya header seperti berikut :
>>
>> - No
>> - Tanggal
>> - Nomor Antrian
>> - Counter
>> - Pukul
>> - Waktu Mulai
>> - Waktu Selesai
>> - Waktu Service
>> - Segment
>> - Handphone
>> - Nama Pelanggan
>> - Note
>> - Detail Transaksi
>> - Status
>> - Petugas
>> - IPK
>> - Transaksi
>> - Revenue
>>
>> dan setiap sheet saya beri nama masing masing petugas seperti berikut :
>> - Andri Sagita
>> - Ahmad Afdhal
>> - Putra M
>> - Nofri Rizal
>> - Joeliardi
>> - Mohd Achtar
>>
>> Saya sudah mencoba lakukan dengan metode Get External Data seperti arahan
>> MR Kid Sebelumnya namun pada langkah akhir muncul notifikasi error *"No
>> valeu given for one or more required parameters"*
>>
>> Mohon bantuan dan pencerahannya kembali Mr Kid
>>
>>
>
>

Wed Apr 27, 2016 6:09 am (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Coba diperiksa lagi tabel yang dibutuhkan setiap baris query nya.
1. Ada atau tidak tabel yang dirujuk setiap baris query itu di dalam file
yang dikoneksi ?
2. Apakah jumlah kolom seluruh tabelnya sama dengan susunan kolom yang
ber-datatype sama ?
3. mbok yao mendefinisikan setiap nama kolomnya dibanding menggunakan * ?

File terlampir menggabungkan 16 tabel (16 sheets) dan baik-baik saja kok.
Unduh dan Extract here di drive D

Regards,
Kid

2016-04-27 18:14 GMT+07:00 Insan Biasa dodybireuen@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> Terimakasih Mr. Kid untuk arahannya. Saya sudah pelajari comand SQL nya
> dan saya sudah berhasil melakukan penggabungan data sheet dengan *fitur
> get external data *excel. Command SQL yang saya gunakan adalah :
>
> SELECT * FROM [dt1$] Where Nomor LIKE '[0-9]%';
> UNION ALL
> SELECT * FROM [dt2$] Where Nomor LIKE '[0-9]%';
> UNION ALL
> SELECT * FROM [dt3$] Where Nomor LIKE '[0-9]%';
> UNION ALL
> SELECT * FROM [dt4$] Where Nomor LIKE '[0-9]%';
> UNION ALL
> SELECT * FROM [dt5$] Where Nomor LIKE '[0-9]%';
> UNION ALL
> SELECT * FROM [dt6$] Where Nomor LIKE '[0-9]%';
>
> Kendala : - Jika menggunakan 3 sheet atau lebih muncul notif error *"Data
> Could not be retrieved from the database. Check the database or contcat
> your database administrator. Make sure the external database is available,
> and then try the opertion again"*
>
> jika hanya mengumpulkan 2 sheet saja atau menggunakan, data berhasil di
> gabungkan. mohon pencerahannya kembali Mr. Kid.
>
>
>
>
> 2016-04-24 7:48 GMT+07:00 'Mr. Kid' mr.nmkid@gmail.com [belajar-excel] <
> belajar-excel@yahoogroups.com>:
>
>>
>>
>> Belajar query-nya bisa disini <http://www.w3schools.com/sql/>.
>>
>> Regards,
>> Kid
>>
>> 2016-04-21 13:39 GMT+07:00 Insan Biasa dodybireuen@gmail.com
>> [belajar-excel] <belajar-excel@yahoogroups.com>:
>>
>>>
>>>
>>> Terimakasih Mr Kid untuk pencerahannya, saya sudah coba menggunakan
>>> fitur Get External Data, tapi saya masih terkendala dengan command text nya.
>>> Saya masih gagal paham dengan perintah berikut :
>>>
>>> *SELECT 'dt1' AS [sumber],a.[kol1],a.[kol2],a.[kol3],a.[kol4] FROM
>>> [dt1$] AS a*
>>> *UNION ALL*
>>> *SELECT 'dt2' AS [sumber],a.[kol1],a.[kol2],a.[kol3],a.[kol4] FROM
>>> [dt2$] AS a*
>>>
>>> Jujur saya baru kali ini bersentuhan dengan yang namanya fitur Get
>>> External Data, dan dari yang sudah saya pelajari akan sangat banyak manfaat
>>> dari fitur Get External Data ini. Saya mohon bantuan Mr Kid untuk perintah
>>> (bunyi query) yang harus saya isikan pada bagian *Command Text*, jika
>>> saya punya header seperti berikut :
>>>
>>> - No
>>> - Tanggal
>>> - Nomor Antrian
>>> - Counter
>>> - Pukul
>>> - Waktu Mulai
>>> - Waktu Selesai
>>> - Waktu Service
>>> - Segment
>>> - Handphone
>>> - Nama Pelanggan
>>> - Note
>>> - Detail Transaksi
>>> - Status
>>> - Petugas
>>> - IPK
>>> - Transaksi
>>> - Revenue
>>>
>>> dan setiap sheet saya beri nama masing masing petugas seperti berikut :
>>> - Andri Sagita
>>> - Ahmad Afdhal
>>> - Putra M
>>> - Nofri Rizal
>>> - Joeliardi
>>> - Mohd Achtar
>>>
>>> Saya sudah mencoba lakukan dengan metode Get External Data seperti
>>> arahan MR Kid Sebelumnya namun pada langkah akhir muncul notifikasi error *"No
>>> valeu given for one or more required parameters"*
>>>
>>> Mohon bantuan dan pencerahannya kembali Mr Kid
>>>
>>>
>>
>
>

Thu Apr 28, 2016 2:00 am (PDT) . Posted by:

"Ardi Anugrah" abirawa12

Dear Pakar Excel,
Mohon pencerahannya agar pada sheet "hasil" secara otomatis update ketika saya update di sheet "Data".  saya pernah mencoba menggunakan kombinasi vlookup, index dan match tetapi dalam satu tanggal (Sheet "Hasil") keluarnya tetap 1 unit, padahal di sheed data dalam tanggal yang sama ada 2 atau 3 unit.
terima kasih atas pencerahan dan sharingnya.
 Best Regards,
M.Ardi Anugrah | abirawa12@yahoo.com | ardi.anugrah@gmail.com
============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com
berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------
READ MORE....
Diposting oleh EXCEL FEED LEARNING di 02.04 0 komentar
Langganan: Komentar (Atom)