[belajar-excel] Digest Number 1375

Milis Belajar Microsoft Excel

Messages In This Digest (8 Messages)

1a.

Re: Mengubah csv menjadi txt From: jkssxls Sudarsono

1b.

Re: Mengubah csv menjadi txt From: jkssxls Sudarsono

2.

Bls: [belajar-excel] Menyisipkan data di kolom sebelah From: ghozi alkatiri

3a.

Re: Menyisipkan data di kolom sebelah From: STDEV(i)

3b.

Re: Menyisipkan data di kolom sebelah From: jkssxls Sudarsono

3c.

Re: Menyisipkan data di kolom sebelah From: jkssxls Sudarsono

3d.

Re: Menyisipkan data di kolom sebelah From: jkssxls Sudarsono

4.

(Belajar-Excel) Membuat menu Pada Userform From: Asror Al-Aliif's

View All Topics | Create New Topic

Messages

1a.

Re: Mengubah csv menjadi txt

Posted by: "jkssxls Sudarsono" jkssxls@hotmail.com   jkssbma

Tue Oct 25, 2011 2:29 am (PDT)

Agar menghapus data sebelumnya , maka perlu diubah :
Sub MacroCVS()
On Error Resume Next Application.Goto Reference:="data_1"
Selection.ClearContents
ActiveSheet.QueryTables.Item("data_1").Delete With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\data.csv", _
Destination:=Range("$A$1"))
.Name = "data_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","

'TEXT MYD GENERAL
.TextFileColumnDataTypes = Array(2, 3, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

To: belajar-excel@yahoogroups.com
From: jkssxls@hotmail.com
Date: Tue, 25 Oct 2011 09:02:41 +0000
Subject: RE: [belajar-excel] Mengubah csv menjadi txt

untuk pengaturan tanda pemisah desimal dan ribuan gunakan :

.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","

To: belajar-excel@yahoogroups.com
From: jkssxls@hotmail.com
Date: Tue, 25 Oct 2011 08:49:47 +0000
Subject: RE: [belajar-excel] Mengubah csv menjadi txt

maksudnya mengubah nama ? atau import file ?
kalau import :
text yg ada ',' ( koma ) perlu diubah seperti ini "emapt,lima" pada format CVS

isi data.txt (tab delimiter):
Text Tanggal Numeric
satu 5/16/2011 12333
dua 12/5/2011 4567.4
tiga 12/12/2011 3444.56
"emapt,lima" 11/11/2011 1111

isi data.csv ( comma delimiter) :
Text,Tanggal,Numeric
satu,5/16/2011,12333
dua,12/5/2011,4567.4
tiga,12/12/2011,3444.56
"emapt,lima",11/11/2011,1111

hasil :

Text
Tanggal
Numeric


satu
5/16/2011
12333


dua
12/5/2011
4567.4


tiga
12/12/2011
3444.56


emapt,lima
11/11/2011
1111


"emapt,lima" diubah menjadi emapt,lima pada data.cvs :

Text
Tanggal
Numeric



satu
5/16/2011
12333



dua
12/5/2011
4567.4



tiga
12/12/2011
3444.56



emapt
lima
11/11/2011
1111


Sub MacroCVS()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\data.csv", _
Destination:=Range("$A$1"))
.Name = "data"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
'TEXT MYD GENERAL
.TextFileColumnDataTypes = Array(2, 3, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet2").Select
End Sub
Sub MacroTXT()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\data.txt", _
Destination:=Range("$A$1"))
.Name = "data"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
'TEXT MYD GENERAL
.TextFileColumnDataTypes = Array(2, 3, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet2").Select
End Sub

2. kalau rename file

kill "e:\data.txt" ' hapus file lama
Name "e:\data.cvs" As
"e:\data.txt"

To: belajar-excel@yahoogroups.com
From: yulius0707@gmail.com
Date: Tue, 25 Oct 2011 11:52:50 +0700
Subject: [belajar-excel] Mengubah csv menjadi txt

Hi teman2,

saya mau tanya bagaimana meng-Ubah nama suatu file dari sample.csv menjadi sample.txt tanpa perlu membukanya di excel terlebih dahulu

Saya coba mengolah yang csv tetapi parsing datanya error, tetapi kalau txt parsingnya lancar.

Terima kasih atas bantuannya

Salam,

Yulius

===========================================

mod's Note:

Barang kali sambil dicoba juga di-rename

di windows-explorer *.csv menjadi *.txt

lalu hasil parsingnya spt apa

Komentar lain sulit, berhubung tiadanya

contoh file yg BOLEH dilihat

===========================================

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)

1b.

Re: Mengubah csv menjadi txt

Posted by: "jkssxls Sudarsono" jkssxls@hotmail.com   jkssbma

Tue Oct 25, 2011 2:51 am (PDT)

Jika saya menggunakan tabel ms access dan mengexport ( gunakan menu external data ) ke format text ( comma delimiter ) dan hasil export sbb ( nama field disertakan ) :"fText","fDate","fNumber"
"satu",5/16/2011 0:00:00,12333.00
"dua",12/5/2011 0:00:00,4567.40
"tiga",12/12/2011 0:00:00,3444.56
"emapt,lima",11/11/2011 0:00:00,1111.00 dan saya gunakan/jalankan MacroCVS , meghasilkan sesuai apa yg saya harapkan....
To: belajar-excel@yahoogroups.com
From: jkssxls@hotmail.com
Date: Tue, 25 Oct 2011 09:29:46 +0000
Subject: RE: [belajar-excel] Mengubah csv menjadi txt

Agar menghapus data sebelumnya , maka perlu diubah :
Sub MacroCVS()
On Error Resume Next
Application.Goto Reference:="data_1"
Selection.ClearContents
ActiveSheet.QueryTables.Item("data_1").Delete With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\data.csv", _
Destination:=Range("$A$1"))
.Name = "data_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","

'TEXT MYD GENERAL
.TextFileColumnDataTypes = Array(2, 3, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

To: belajar-excel@yahoogroups.com
From: jkssxls@hotmail.com
Date: Tue, 25 Oct 2011 09:02:41 +0000
Subject: RE: [belajar-excel] Mengubah csv menjadi txt

untuk pengaturan tanda pemisah desimal dan ribuan gunakan :

.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","

To: belajar-excel@yahoogroups.com
From: jkssxls@hotmail.com
Date: Tue, 25 Oct 2011 08:49:47 +0000
Subject: RE: [belajar-excel] Mengubah csv menjadi txt

maksudnya mengubah nama ? atau import file ?
kalau import :
text yg ada ',' ( koma ) perlu diubah seperti ini "emapt,lima" pada format CVS

isi data.txt (tab delimiter):
Text Tanggal Numeric
satu 5/16/2011 12333
dua 12/5/2011 4567.4
tiga 12/12/2011 3444.56
"emapt,lima" 11/11/2011 1111

isi data.csv ( comma delimiter) :
Text,Tanggal,Numeric
satu,5/16/2011,12333
dua,12/5/2011,4567.4
tiga,12/12/2011,3444.56
"emapt,lima",11/11/2011,1111

hasil :

Text
Tanggal
Numeric


satu
5/16/2011
12333


dua
12/5/2011
4567.4


tiga
12/12/2011
3444.56


emapt,lima
11/11/2011
1111


"emapt,lima" diubah menjadi emapt,lima pada data.cvs :

Text
Tanggal
Numeric



satu
5/16/2011
12333



dua
12/5/2011
4567.4



tiga
12/12/2011
3444.56



emapt
lima
11/11/2011
1111


Sub MacroCVS()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\data.csv", _
Destination:=Range("$A$1"))
.Name = "data"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
'TEXT MYD GENERAL
.TextFileColumnDataTypes = Array(2, 3, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet2").Select
End Sub
Sub MacroTXT()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\data.txt", _
Destination:=Range("$A$1"))
.Name = "data"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
'TEXT MYD GENERAL
.TextFileColumnDataTypes = Array(2, 3, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet2").Select
End Sub

2. kalau rename file

kill "e:\data.txt" ' hapus file lama
Name "e:\data.cvs" As
"e:\data.txt"

To: belajar-excel@yahoogroups.com
From: yulius0707@gmail.com
Date: Tue, 25 Oct 2011 11:52:50 +0700
Subject: [belajar-excel] Mengubah csv menjadi txt

Hi teman2,

saya mau tanya bagaimana meng-Ubah nama suatu file dari sample.csv menjadi sample.txt tanpa perlu membukanya di excel terlebih dahulu

Saya coba mengolah yang csv tetapi parsing datanya error, tetapi kalau txt parsingnya lancar.

Terima kasih atas bantuannya

Salam,

Yulius

===========================================

mod's Note:

Barang kali sambil dicoba juga di-rename

di windows-explorer *.csv menjadi *.txt

lalu hasil parsingnya spt apa

Komentar lain sulit, berhubung tiadanya

contoh file yg BOLEH dilihat

===========================================

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)

2.

Bls: [belajar-excel] Menyisipkan data di kolom sebelah

Posted by: "ghozi alkatiri" ghozialkatiri@yahoo.co.id   ghozialkatiri

Tue Oct 25, 2011 2:49 am (PDT)

coba gunakan formula

=IF(ISODD(ROW(1:1));INDEX($B$3:$B$12;TRUNC((ROW(1:1)-1)/2)+1);TRUNC((ROW(1:1)-1)/2)+1)
copy ke bawah sampai tuntas tas.

wassalam

Ghozi Alkatiri

________________________________
Dari: Scorpio Alone <sc0rpi0_b0y82@yahoo.com>
Kepada: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
Dikirim: Selasa, 25 Oktober 2011 15:58
Judul: [belajar-excel] Menyisipkan data di kolom sebelah

 
Kepada para suhu X-cel

Mohon pencerahan untuk kasus newbie yang pertama, kalo bisa jangan pake macro

Thanks

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (1)

3a.

Re: Menyisipkan data di kolom sebelah

Posted by: "STDEV(i)" setiyowati.devi@gmail.com   siti_vi

Tue Oct 25, 2011 3:14 am (PDT)

formula lain (ditulis di salah satu cell di baris 3)

=IF(ISEVEN(ROW()),
INDEX($C$3:$C$12,FLOOR(ROW()-1,2)/2),
INDEX($B$3:$B$12,FLOOR(ROW()-1,2)/2))

ASUMSI
DATA 2 BISA DIUBAH-UBAH; tidak harus berupa angka urut 1,2,3... dst

2011/10/25 STDEV(i) <setiyowati.devi@gmail.com>

> mungkin begini
>
> =IF(MOD(ROW(),2)>0,

* OFFSET($B$2,**CEILING(ROW()-2,2)/2**,0)*,

CEILING(ROW()-2,2)/2)
>
> tinggal NGOPI ke cells dibawahnya sampai sebanyak yg anda perlukan
>
>
> '-------
> lupa kiboard-short-cut di excel???
> daftarnya ada di sini:. http://www.box.net/shared/h0k1ds7gg3frypr67t1h
>
>
> 2011/10/25 Scorpio Alone <sc0rpi0_b0y82@yahoo.com>
>
>> **
>> Kepada para suhu X-cel
>> Mohon pencerahan untuk kasus newbie yang pertama, kalo bisa jangan pake
>> macro [image: ;) winking]
>> Thanks
>>
>

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)

3b.

Re: Menyisipkan data di kolom sebelah

Posted by: "jkssxls Sudarsono" jkssxls@hotmail.com   jkssbma

Tue Oct 25, 2011 6:24 am (PDT)

gunakan array formula :{=INDEX(B3:C12,CEILING(ROW(1:20)/2,1),IF(ISEVEN(ROW(1:20)),2,1))} rumus ini berpola {{1,1},{1,2},{2,2},{3,1},{3,2} ... {10,1},{10,2}} CEILING(ROW(1:20)/2,1) dapat diganti oleh ROUNDUP(ROW(1:20)/2,1)IF(ISEVEN(ROW(1:20)),2,1) dapat diganti IF(ISODD(ROW(1:20)),1,2) ' ini masa saja
To: belajar-excel@yahoogroups.com
From: sc0rpi0_b0y82@yahoo.com
Date: Tue, 25 Oct 2011 16:58:45 +0800
Subject: [belajar-excel] Menyisipkan data di kolom sebelah

Kepada para suhu X-cel

Mohon pencerahan untuk kasus newbie yang pertama, kalo bisa jangan pake macro
Thanks

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)

3c.

Re: Menyisipkan data di kolom sebelah

Posted by: "jkssxls Sudarsono" jkssxls@hotmail.com   jkssbma

Tue Oct 25, 2011 6:25 am (PDT)

gunakan array formula :
{=INDEX(B3:C12,CEILING(ROW(1:20)/2,1),IF(ISEVEN(ROW(1:20)),2,1))}

rumus ini berpola {{1,1},{1,2},{2,1},{2,2},{3,1},{3,2} ... {10,1},{10,2}}

CEILING(ROW(1:20)/2,1) dapat diganti oleh ROUNDUP(ROW(1:20)/2,1)
IF(ISEVEN(ROW(1:20)),2,1) dapat diganti IF(ISODD(ROW(1:20)),1,2) ' ini masa saja

To: belajar-excel@yahoogroups.com
From: sc0rpi0_b0y82@yahoo.com
Date: Tue, 25 Oct 2011 16:58:45 +0800
Subject: [belajar-excel] Menyisipkan data di kolom sebelah

Kepada para suhu X-cel

Mohon pencerahan untuk kasus newbie yang pertama, kalo bisa jangan pake macro
Thanks

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)

3d.

Re: Menyisipkan data di kolom sebelah

Posted by: "jkssxls Sudarsono" jkssxls@hotmail.com   jkssbma

Tue Oct 25, 2011 6:35 am (PDT)

pola 1,2,1,2 atau 1,2,3,1,2,3 atau 1,2,3,4,1,2,3,4dapat diakali dengan cara ?{=MOD(ROW(1:20)-1,2)+1} --> 1,2,1,2 ...{=MOD(ROW(1:20)-1,3)+1} --> 1,2,3,1,2,3 ...{=MOD(ROW(1:20)-1,4)+1} --> 1,2,3,4,1,2,3,4 ... pola 1,1,2,2,.. atau 1,1,1,2,2,2, .. {=CEILING(ROW(1:20)/2,1)} ---> 1,1,2,2 ...{=CEILING(ROW(1:20)/3,1)} ---> 1,1,1,2,2,2 ... silakan berkreasi dgn pola-pola bilangan lainnya.....
To: belajar-excel@yahoogroups.com
From: jkssxls@hotmail.com
Date: Tue, 25 Oct 2011 13:25:25 +0000
Subject: RE: [belajar-excel] Menyisipkan data di kolom sebelah

gunakan array formula :
{=INDEX(B3:C12,CEILING(ROW(1:20)/2,1),IF(ISEVEN(ROW(1:20)),2,1))}

rumus ini berpola {{1,1},{1,2},{2,1},{2,2},{3,1},{3,2} ... {10,1},{10,2}}

CEILING(ROW(1:20)/2,1) dapat diganti oleh ROUNDUP(ROW(1:20)/2,1)
IF(ISEVEN(ROW(1:20)),2,1) dapat diganti IF(ISODD(ROW(1:20)),1,2) ' ini masa saja

To: belajar-excel@yahoogroups.com
From: sc0rpi0_b0y82@yahoo.com
Date: Tue, 25 Oct 2011 16:58:45 +0800
Subject: [belajar-excel] Menyisipkan data di kolom sebelah

Kepada para suhu X-cel

Mohon pencerahan untuk kasus newbie yang pertama, kalo bisa jangan pake macro
Thanks

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (6)

4.

(Belajar-Excel) Membuat menu Pada Userform

Posted by: "Asror Al-Aliif's" asrorcakep@yahoo.com   asrorcakep

Tue Oct 25, 2011 6:35 am (PDT)

Selamat malam,

Dear rekan2 milis, mohon bantuannya, saya mempunyai sebuah form, pada form tersebut mau saya kasih menu bar, jadi ketika ada button di klik akan muncul beberapa menu kebawah (seperti tampilan pada file excel, file di klik kebawan ada open, save, print dlsb). Bagaimana memasang seperti ini pada userform? Apakah bisa?

Terimakasih.

Salam,

Asror

"Belajar dimulai dengan bertanya, jika dimulai dengan penjelasan dari guru, maka guru yang akan bisa, bukan muridnya"



Safety is my responsibility
Sent from Super Asror's BlackBerry� smartphone from Sinyal Bagus XL, Nyambung Teruuusss...!

Back to top

Reply to sender | Reply to group | Reply via web post
Messages in this topic (1)

Recent Activity

•  16
  New Members

Visit Your Group

Yahoo! Groups

Cat Zone

Connect w/ others

who love cats.

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! Groups

Mental Health Zone

Find support for

Mental illnesses

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web

Messages

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

MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

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