Tecnologia, programação e muito Visual FoxPro.

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




__._,_.___
+-:: 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              |
+-------------------------------------------------------------------+
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Hollywood kids

in the spotlight

Their moms

share secrets

Yahoo! Groups

Small Business Group

A community for

small business owners

.

__,_._,___

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
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

 

__._,_.___
+-:: 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              |
+-------------------------------------------------------------------+
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! Groups

Mental Health Zone

Learn about issues

Find support

Yahoo! Groups

Dogs Owners Group

Join Do More For Dogs

pet community

.

__,_._,___

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.

__._,_.___
+-:: 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              |
+-------------------------------------------------------------------+
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! Groups

Mom Power

Just for moms

Join the discussion

Yahoo! Groups

Auto Enthusiast Zone

Auto Enthusiast Zone

Car groups and more!

.

__,_._,___

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?


----- 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
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)

__._,_.___
+-:: 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              |
+-------------------------------------------------------------------+
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! Groups

Small Business Group

Improve your business

by community exchange

Celebrity Parents

Spotlight on Kids

Hollywood families

share stories

.

__,_._,___

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]

__._,_.___
Recent Activity
Visit Your Group
Yahoo! News

Get it all here

Breaking news to

entertainment news

New web site?

Drive traffic now.

Get your business

on Yahoo! search.

Yahoo! Groups

Mental Health Zone

Find support for

Mental illnesses

.

__,_._,___

]] 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

__._,_.___
+-:: 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              |
+-------------------------------------------------------------------+
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Dog Groups

on Yahoo! Groups

discuss everything

related to dogs.

Group Charity

Loans that

change lives

Kiva.org

.

__,_._,___

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!

__._,_.___
+-:: 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              |
+-------------------------------------------------------------------+
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! Groups

Auto Enthusiast Zone

Auto Enthusiast Zone

Car groups and more!

Yahoo! Groups

Do More For Dogs Group

Join a group of dog owners

who do more.

.

__,_._,___

Arquivo do blog