Hot Fox

sábado, 31 de outubro de 2009

Re: ]] XL-mania [[ Parsing data dari file external

Dear Mr. SPono,

file terlampir menggunakan file .txt yang di-create dari sheet excel yang Anda sertakan. Letak file dalam disk masih di-set satu folder dengan file excel. output pada sheet baru.

semua disusun dengan VBA, ada penjelasan prosesnya, jadi bisa sebagai bahan ajar.

file txt2xl.zi adalah hasil kompresi dengan winrar sebagai zip file (.zip). Rename ke .zip lalu extract dengan winrar atau winzip. Akan tercipta folder txt2xl yang berisi 1 file contoh (contoh.txt) dan 1 file excel (re-parsing data ke excel.xls).

mungkin lain kali file attachment di-convert dulu ke xl2003 ke bawah, karena tidak semua anggota menggunakan xl2007. Dengan begitu, semakin banyak anggota yang bisa urun solusi. Okay Sir....

Thank You.
Kid.

2009/10/27 chusk soepono <csoepono@yahoo.com>

Dear XLs Expert,
langsung aja ya, mohon bantuannya untuk membuat tabel data base (Excel) yang datanya diambil dari file luar (txt). File ini adalah hasil log dari system/mesin yg diupdate dalam periode tertentu (overwrite).
Apa memungkinkan Parsing dari file external ke dalam excel (boleh dengan macro)?. Ataukah harus menggunakan applikasi lain?
atas bantuannya kami sampaikan terima kasih

Salam hormat,
SPono

__._,_.___

Messages in this topic (3) Reply (via web post) | Start a new topic

Messages

+-:: XL-mania ::::::::::::::::::::----------------------------------+
| Moderasi akan lambat minggu ini, momods pergi ke tempat jauh      |
| http://www.facebook.com/group.php?gid=37671048001&ref=mf          |
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message, melecehkan, tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan winzip jika perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/           |
+-------------------------------------------------------------------+
| Berikan testimoni di : http://www.friendster.com/xlmania atau... |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html           |
+-------------------------------------------------------------------+
| Message lama ada di :                                             |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id] |
| http://www.mail-archive.com/xl-mania@yahoogroups.com              |
+-------------------------------------------------------------------+

MARKETPLACE

Mom Power: Discover the community of moms doing more for their families, for the world and for each other

Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Daily Digest | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe

__,_._,___

Re: ]] XL-mania [[ sumproduct mengambil beberapa karakter

Dear Pak Eko Nur,

Salah satu alternatifnya, memanfaatkan kemampuan beberapa fungsi yang mampu bekerja menggunakan wildcard character *
Semoga jadi cerah....

Kid.

2009/10/30 Eko Nur <s.eko.nur@musashi.co.id>

Dear Pakar XL,

Perlu pencerahan nich.

Saya mengalami kesulitan. Saat ambil data dari access dengan sumproduct, sering tidak connect karena

karakter di access kadang berubah (dikarakter akhir).

Pertanyaannya:

Bagaimana caranya mengambil beberapa karakter saja? (terlampir)

terimakasih

Eko Nur

Re: ]] XL-mania [[ Schedule pengiriman barang time based

seperti ini kah ??

* Tgl PENGIRIMAN PERTAMA ditentukan oleh user:

* Sabtu / Minggu Bukan Hari Kerja / Schedule Kirim tidak boleh jatuh hari Sabtu / Minggu.

dengan tujuan spt itu, penggunaan fungsi cWORKDAY pun agak berubah

Private Function SchedList(Sched1 As Date, ByVal Nday As Long)
   '--------------------------------
   Dim i As Integer, ScList As String, Sched2 As Date
   Sched2 = Sched1
   ScList = "\" & Day(Sched1)
   For i = 1 To (31 / Nday)
      Sched2 = cWORKDAY(Sched2, Nday, Saturday + Sunday)
      If Month(Sched2) <> Month(Sched1) Then Exit For
      ScList = ScList & "\" & Day(Sched2)
   Next
   SchedList = ScList & "\"
End Function

lampiran : ctv_schedule pengiriman time based_(modified_2).xls

----- Original Message -----
From: Zaki
To: XL-mania@yahoogroups.com
Sent: Friday, October 30, 2009 01:13PM
Subject: Re: ]] XL-mania [[ Schedule pengiriman barang time based

Dear all,
thanks sebelumnya.
Untuk hari minggu tidak ada pengiriman/libur.
Dan adakah cara lain kalau penetapan pengirimannya bukan tanggal,
tapi qty yang sesuai dengan tgl.
Thanks,
regards
.:: zaki ::.

----- Original Message -----
From: siti Vi
To: XL-mania@yahoogroups.com
Sent: Friday, October 30, 2009 11:52 AM
Subject: Re: ]] XL-mania [[ Schedule pengiriman barang time based

kalau dengan rumus tsb jatuhnya hari mnggu bagimana ??
(mungkin perlu bantuan fungsi workday; tetapi yg menganggap sabtu = hari kerja)

Re: ]] XL-mania [[ sumproduct mengambil beberapa karakter

ya tinggal mengganti dikittt..

"Aditya P" itu 'kan = LEFT("Aditya PEXXXXXXXXXZZZZZZZ", LEN("Aditya P"))

(AMBIL "Aditya PEXXXXXXXXXZZZZZZZ" dari kiri sebanyak karakter yg ada di "Aditya P"

rumus di G7 (array formula)

=SUM(IF((LEFT($B$6:$B$23,LEN(G$6))=G$6)*($C$6:$C$23=$F7),$D$6:$D$23))

atau dapat ditulis dlm versi ZAMPRODAX (G15):

=SUMPRODUCT((LEFT($B$6:$B$23,LEN(G$6))=G$6)*($C$6:$C$23=$F15)*($D$6:$D$23))

yang mungkin sulit diotomatiskan : penulisan KRITERIA di G6

(boleh tahu ndak : itu diambil dengan cara bgmana??)

----- Original Message -----

From: Eko Nur

To: XL-mania@yahoogroups.com

Cc: Bp.Jon Banurea

Sent: Friday, October 30, 2009 06:38PM

Subject: ]] XL-mania [[ sumproduct mengambil beberapa karakter

Dear Pakar XL,

Perlu pencerahan nich.

Saya mengalami kesulitan. Saat ambil data dari access dengan sumproduct,

sering tidak connect karena karakter di access kadang berubah (dikarakter akhir).

Pertanyaannya:

Bagaimana caranya mengambil beberapa karakter saja? (terlampir)

terimakasih

Eko Nur

__._,_.___

Messages in this topic (2) Reply (via web post) | Start a new topic

Messages

MARKETPLACE

Mom Power: Discover the community of moms doing more for their families, for the world and for each other

Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Daily Digest | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe

__,_._,___

Re: ]] XL-mania [[ Schedule pengiriman barang time based

Jika tgl awal (G25): diganti dengan: 1 Oktober 2009, atau

diganti dgn: tgl awal bulan lain yg tgl 1 nya BUKAN MINGGU;
HASIL rumus tidak lagi =sesuai keinginan..

----- Original Message -----
From: Ranu Nugraha
To: XL-mania@yahoogroups.com
Sent: Friday, October 30, 2009 05:12PM
Subject: Re: ]] XL-mania [[ Schedule pengiriman barang time based

Dear pak Zaki,
Semoga solusi pada attachment dapat menjawab,
Thanks,
Ranu

--- On Thu, 29/10/09, Zaki <admin_ppic2@sanwa-ei.com> wrote:
From: Zaki <admin_ppic2@sanwa-ei.com>
Date: Thursday, 29 October, 2009, 3:48 PM

Dear para XL-Mania,
Adakah formula untuk membuat schedule pengiriman based on time?,
contoh data terlampir.

__._,_.___

Messages in this topic (8) Reply (via web post) | Start a new topic

Messages

MARKETPLACE

Mom Power: Discover the community of moms doing more for their families, for the world and for each other

Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Daily Digest | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe

__,_._,___

Re: ]] XL-mania [[ Schedule pengiriman barang time based

menurutku:

alangkah sulit mengartikan KRITERIA INI:

"qty yang sesuai dengan tgl."

Adakah cara lain untuk menggambarkannya dlm

bentuk nyata (misal sebuah contoh HASIL di sheet)

selain dgn kata "qty yang sesuai dengan tgl." tsb?

penetapan pengirimannya bukan tanggal,
tapi qty yang sesuai dengan tgl.
Thanks,
regards
.:: zaki ::.

----- Original Message -----
From: siti Vi
Sent: Friday, October 30, 2009 11:52 AM
kalau dengan rumus tsb jatuhnya hari mnggu bagimana ??
(mungkin perlu bantuan fungsi workday; tetapi yg menganggap sabtu = hari kerja)

__._,_.___

Messages in this topic (7) Reply (via web post) | Start a new topic

Messages

MARKETPLACE

Mom Power: Discover the community of moms doing more for their families, for the world and for each other

Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Daily Digest | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe

__,_._,___

sexta-feira, 30 de outubro de 2009

Re: [Access VBA Central] Searching for shadow in dark room

Jeff are you getting any data back in the Recordset or are you getting the "There are no records....." message?

If you are getting data can you test if you are getting what you expect within the calling program where you want to use it? Or in other words what data IS in that field if anything?

Note both of these test suggestions are tests conducted from within the recipient that you want to use the data within.

Are you using a declared ODBC global PC name for the database (forget the exact term for it)?

--- On Wed, 10/28/09, pdcaregiver <jpjones23@earthlink.net> wrote:

From: pdcaregiver <jpjones23@earthlink.net>
Subject: [Access VBA Central] Searching for shadow in dark room
To: AccessVBACentral@yahoogroups.com
Date: Wednesday, October 28, 2009, 7:57 PM

I've created a tool with an Excel front end and an Access back end. The objective is to store data for analysis for folks working in multiple locations. The tool works correctly on all 5 computers here but at one remote location it doesn't.

The platforms here use Excel 97, Excel 2003 SP3 and Excel 2007. The operating systems are XP Pro SP3, and XP ME SP2.

The remote location uses XP Pro SP3 and Excel 2003 SP3.

I've compared references and all are the same although I've not yet delved down to see if there are different versions of libraries.

The line of code in the remote location that throws up the error is very simply storing a table field in a variable. A snippet of code follows.

Set rs = New ADODB.Recordset
With rs
.Open strSQLQuery, cn, , , adCmdText
If Not rs.EOF Then
strTblKey = rs.Fields("TableKey ") <- error
Else
MsgBox "There are no records..... "
End If
End With

The database table row follows:

ParentKey TableKey
Base_P_Key B9991110

The SQL statement is valid. I tested it directly in Access. i want the TableKey field data (B9991110) to be placed into the variable.

The error message speaks to the item not being in the collection. Err number 3265, "Item cannot be found in collection corresponding to the requested name or ordinal."

I tried an alternative to test the query and connection in the code by executing the query and then copying the record set to a worksheet. It worked correctly on the remote computer. Here's it's copy statement:

Set xlSht = Sheets(ActiveSheet. Name)
xlSht.Range( "A2").CopyFromRe cordset rs

So, after all this wonderfulness, I come to my specific question. Have any of you seen this sort of circumstance and, if so, have you any suggestions for correcting the error. I really would rather figure out a solution than to just use the CopyFromRecordset method. The code ought to just work.

Hoping.....

Jeff

[Non-text portions of this message have been removed]

__._,_.___

Messages in this topic (2) Reply (via web post) | Start a new topic

MARKETPLACE

Mom Power: Discover the community of moms doing more for their families, for the world and for each other

Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Daily Digest | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe

__,_._,___

]] XL-mania [[ sumproduct mengambil beberapa karakter

Dear Pakar XL,

Perlu pencerahan nich.

Saya mengalami kesulitan. Saat ambil data dari access dengan sumproduct, sering tidak connect karena

karakter di access kadang berubah (dikarakter akhir).

Pertanyaannya:

Bagaimana caranya mengambil beberapa karakter saja? (terlampir)

terimakasih

Eko Nur

Re: ]] XL-mania [[ Re: Diurut

Betul 'A..

manakala sebuah kata terucap: "diurut"; ternyata masih perlu penjelasan

yg cukup panjang.. (kalau MAU disusinya jadi efisien)

ini masih ada versi yg ringkas -&- sederhana:

Mencari RowIndexnya (array formula) (dummies di kolom E)

=SMALL(--REPLACE(A4:A23,1,11,""),ROW(1:20))

Memunculkan data berdasarkan index (bukan array formula)

=LEFT($A4,11)&E4

bisa digabung:

=LEFT($A4:$A23,11)&SMALL(--REPLACE(A4:A23,1,11,""),ROW(1:20))

----- Original Message -----
From: Ahmad Adriansyah
To: XL-mania@yahoogroups.com
Sent: Friday, October 30, 2009 09:33AM
Subject: RE: ]] XL-mania [[ Re: Diurut

Salah ternyata,
Terkadang karena berpatokan kepada sesuatu yang tidak pasti menjadikan segalanya menjadi rumit,

padahal permintaannya cukup sederhana.
Ada formula yang lebih pendeka ternyat yakni :

{=INDEX(A7:A26,MATCH(SMALL(--MID(A7:A26,12,3),ROW(1:20)),--MID(A7:A26,12,3),0))}

dengan catatan bahwa datanya ada do A7:A26. CSE

Thanks & Regards
AHMAD ADRIANSYAH

__._,_.___

Messages in this topic (14) Reply (via web post) | Start a new topic

Messages

MARKETPLACE

Mom Power: Discover the community of moms doing more for their families, for the world and for each other

Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Daily Digest | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe

__,_._,___

Re: ]] XL-mania [[ Schedule pengiriman barang time based

Dear pak Zaki,

Semoga solusi pada attachment dapat menjawab,

Thanks,
Ranu

--- On Thu, 29/10/09, Zaki <admin_ppic2@sanwa-ei.com> wrote:

From: Zaki <admin_ppic2@sanwa-ei.com>
Subject: ]] XL-mania [[ Schedule pengiriman barang time based
To: XL-mania@yahoogroups.com
Date: Thursday, 29 October, 2009, 3:48 PM

Dear para XL-Mania,

Adakah formula untuk membuat schedule pengiriman based on time?,

contoh data terlampir.

Thanks,

regards

.:: zaki ::.

Get your new Email address!
Grab the Email name you've always wanted before someone else does!

__._,_.___

Messages in this topic (6) Reply (via web post) | Start a new topic

Messages

MARKETPLACE

Mom Power: Discover the community of moms doing more for their families, for the world and for each other

Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Daily Digest | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe

__,_._,___

sábado, 31 de outubro de 2009

sexta-feira, 30 de outubro de 2009

Arquivo do blog