7 Messages
Digest #3013
4
Re: Ada Terbilang di MS Word 2007 (siapa takut?) [2 Attachments] by "Hendrik Karnadi" hendrikkarnadi
Messages
Sun Jul 20, 2014 6:18 pm (PDT) . Posted by:
"him mah"
Assalamu'alaikum Wr. Wb.
saya barusan nyari fungsi untuk konvert dari xls ke CSV, dan ketemu
hasil seperti dibawah
yang mau saya tanyakan adalah, setelah saya jalankan fungsi tersebut
dan berhasil namun yang jadi masalah pembatas antar kolom nya pake
koma (,) kalau ingin merubah untuk pembatasnya menjadi titik koma /
semicolon(;) kira kira edit fungsinya seperti apa ya
terima kasih
fungsinya seperti ini
'----------------------------------------------------------
' Procedure : ConvertXls2CSV
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Converts a standard Excel file to csv format
' Requirements: Requires MS Excel be installed
' Uses late binding, so no libraries need be declared
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on
other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sXlsFile : Fully qualified path and filename with extension of the
Excel workbook
'
' Usage:
' ~~~~~~
' ConvertXls2CSV "C:\Users\Daniel\Desktop\Contact_E-mail listing.xls"
' Will output a file C:\Users\Daniel\Desktop\Contact_E-mail listing.csv
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2012-May-11 Initial Release - Answer to forum question
'----------------------------------------------------------
Function ConvertXls2CSV(sXlsFile As String)
On Error Resume Next
Dim oExcel As Object
Dim oExcelWrkBk As Object
Dim bExcelOpened As Boolean 'Was Excel already open or not
'Review 'XlFileFormat Enumeration' for more formats
Const xlCSVWindows = 23 'Windows CSV Format
Const xlCSV = 6 'CSV
Const xlCSVMac = 22 'Macintosh CSV
Const xlCSVMSDOS = 24 'MSDOS CSV
Set oExcel = GetObject(, "Excel.Application";) 'Bind to existing
instance of Excel
If Err.Number <> 0 Then 'Could not get instance of Excel, so
create a new one
Err.Clear
On Error GoTo Error_Handler
Set oExcel = CreateObject("excel.application";)
bExcelOpened = False
Else 'Excel was already running
bExcelOpened = True
End If
On Error GoTo Error_Handler
oExcel.ScreenUpdating = False
oExcel.Visible = False 'Keep Excel hidden from the user
oExcel.Application.DisplayAlerts = False
Set oExcelWrkBk = oExcel.Workbooks.Open(sXlsFile)
'Note: you may wish to change the file format constant for another
type declared
' above based on your usage/needs in the following line.
oExcelWrkBk.SaveAs Left(sXlsFile, InStrRev(sXlsFile, ".")) &
"csv", xlCSVWindows
oExcelWrkBk.Close False
If bExcelOpened = False Then
oExcel.Quit
End If
Error_Handler_Exit:
On Error Resume Next
Set oExcelWrkBk = Nothing
Set oExcel = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: ConvertXls2CSV"; & vbCrLf & _
"Error Description: " & Err.Description, _
vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
saya barusan nyari fungsi untuk konvert dari xls ke CSV, dan ketemu
hasil seperti dibawah
yang mau saya tanyakan adalah, setelah saya jalankan fungsi tersebut
dan berhasil namun yang jadi masalah pembatas antar kolom nya pake
koma (,) kalau ingin merubah untuk pembatasnya menjadi titik koma /
semicolon(;) kira kira edit fungsinya seperti apa ya
terima kasih
fungsinya seperti ini
'----------------------------------------------------------
' Procedure : ConvertXls2CSV
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Converts a standard Excel file to csv format
' Requirements: Requires MS Excel be installed
' Uses late binding, so no libraries need be declared
' Copyright : The following may be altered and reused as you wish so long as the
' copyright notice is left unchanged (including Author, Website and
' Copyright). It may not be sold/resold or reposted on
other sites (links
' back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~
' sXlsFile : Fully qualified path and filename with extension of the
Excel workbook
'
' Usage:
' ~~~~~~
' ConvertXls2CSV "C:\
' Will output a file C:\Users&#
'
' Revision History:
' Rev Date(yyyy/mm/
' ************
' 1 2012-May-11 Initial Release - Answer to forum question
'-------
Function ConvertXls2CSV(
On Error Resume Next
Dim oExcel As Object
Dim oExcelWrkBk As Object
Dim bExcelOpened As Boolean 'Was Excel already open or not
'Review 'XlFileFormat Enumeration&#
Const xlCSVWindows = 23 'Windows CSV Format
Const xlCSV = 6 'CSV
Const xlCSVMac = 22 'Macintosh CSV
Const xlCSVMSDOS = 24 'MSDOS CSV
Set oExcel = GetObject(, "Excel.
instance of Excel
If Err.Number <> 0 Then 'Could not get instance of Excel, so
create a new one
Err.Clear
On Error GoTo Error_Handler
Set oExcel = CreateObject(
bExcelOpened = False
Else 'Excel was already running
bExcelOpened = True
End If
On Error GoTo Error_Handler
oExcel.ScreenUpdati
oExcel.Visible = False 'Keep Excel hidden from the user
oExcel.Application.
Set oExcelWrkBk = oExcel.Workbooks.
'Note: you may wish to change the file format constant for another
type declared
' above based on your usage/needs in the following line.
oExcelWrkBk.
"csv"
oExcelWrkBk.
If bExcelOpened = False Then
oExcel.Quit
End If
Error_Handler_
On Error Resume Next
Set oExcelWrkBk = Nothing
Set oExcel = Nothing
Exit Function
Error_Handler:
MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: ConvertXls2CSV"
"Error Description: " & Err.Description, _
vbCritical, "An Error has Occured!"
Resume Error_Handler_
End Function
Sun Jul 20, 2014 7:33 pm (PDT) . Posted by:
"him mah"
Alhamdulillah sudah ketemu disni
http://www.vbaexpress.com/forum/showthread.php?42769-save-as-csv-file-format/page2
oExcelWrkBk.SaveAs Left(sXlsFile, InStrRev(sXlsFile, ".")) &
"csv", xlCSVWindows
diganti seperti ini
oExcelWrkBk.SaveAs Left(sXlsFile, InStrRev(sXlsFile, ".")) & "csv",
xlCSVWindows, , , , , , , , , , True
2014-07-21 8:18 GMT+07.00, him mah <himmah.mlg@gmail.com>:
> Assalamu'alaikum Wr. Wb.
>
> saya barusan nyari fungsi untuk konvert dari xls ke CSV, dan ketemu
> hasil seperti dibawah
>
> yang mau saya tanyakan adalah, setelah saya jalankan fungsi tersebut
> dan berhasil namun yang jadi masalah pembatas antar kolom nya pake
> koma (,) kalau ingin merubah untuk pembatasnya menjadi titik koma /
> semicolon(;) kira kira edit fungsinya seperti apa ya
>
> terima kasih
>
> fungsinya seperti ini
>
> '----------------------------------------------------------
> ' Procedure : ConvertXls2CSV
> ' Author : Daniel Pineault, CARDA Consultants Inc.
> ' Website : http://www.cardaconsultants.com
> ' Purpose : Converts a standard Excel file to csv format
> ' Requirements: Requires MS Excel be installed
> ' Uses late binding, so no libraries need be declared
> ' Copyright : The following may be altered and reused as you wish so long as
> the
> ' copyright notice is left unchanged (including Author, Website
> and
> ' Copyright). It may not be sold/resold or reposted on
> other sites (links
> ' back to this site are allowed).
> '
> ' Input Variables:
> ' ~~~~~~~~~~~~~~~~
> ' sXlsFile : Fully qualified path and filename with extension of the
> Excel workbook
> '
> ' Usage:
> ' ~~~~~~
> ' ConvertXls2CSV "C:\Users\Daniel\Desktop\Contact_E-mail listing.xls"
> ' Will output a file C:\Users\Daniel\Desktop\Contact_E-mail
> listing.csv
> '
> ' Revision History:
> ' Rev Date(yyyy/mm/dd) Description
> '
> **************************************************************************************
> ' 1 2012-May-11 Initial Release - Answer to forum
> question
> '----------------------------------------------------------
> Function ConvertXls2CSV(sXlsFile As String)
> On Error Resume Next
> Dim oExcel As Object
> Dim oExcelWrkBk As Object
> Dim bExcelOpened As Boolean 'Was Excel already open or not
> 'Review 'XlFileFormat Enumeration' for more formats
> Const xlCSVWindows = 23 'Windows CSV Format
> Const xlCSV = 6 'CSV
> Const xlCSVMac = 22 'Macintosh CSV
> Const xlCSVMSDOS = 24 'MSDOS CSV
>
> Set oExcel = GetObject(, "Excel.Application";) 'Bind to existing
> instance of Excel
>
> If Err.Number <> 0 Then 'Could not get instance of Excel, so
> create a new one
> Err.Clear
> On Error GoTo Error_Handler
> Set oExcel = CreateObject("excel.application";)
> bExcelOpened = False
> Else 'Excel was already running
> bExcelOpened = True
> End If
>
> On Error GoTo Error_Handler
> oExcel.ScreenUpdating = False
> oExcel.Visible = False 'Keep Excel hidden from the user
> oExcel.Application.DisplayAlerts = False
>
> Set oExcelWrkBk = oExcel.Workbooks.Open(sXlsFile)
> 'Note: you may wish to change the file format constant for another
> type declared
> ' above based on your usage/needs in the following line.
> oExcelWrkBk.SaveAs Left(sXlsFile, InStrRev(sXlsFile, ".")) &
> "csv", xlCSVWindows
> oExcelWrkBk.Close False
>
> If bExcelOpened = False Then
> oExcel.Quit
> End If
>
> Error_Handler_Exit:
> On Error Resume Next
> Set oExcelWrkBk = Nothing
> Set oExcel = Nothing
> Exit Function
>
> Error_Handler:
> MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
> "Error Number: " & Err.Number & vbCrLf & _
> "Error Source: ConvertXls2CSV"; & vbCrLf & _
> "Error Description: " & Err.Description, _
> vbCritical, "An Error has Occured!"
> Resume Error_Handler_Exit
> End Function
>
http://www.vbaexpress.com/forum/showthread.php?42769-save-as-csv-file-format/page2
oExcelWrkBk.SaveAs Left(sXlsFile, InStrRev(sXlsFile, ".")) &
"csv", xlCSVWindows
diganti seperti ini
oExcelWrkBk.SaveAs Left(sXlsFile, InStrRev(sXlsFile, ".")) & "csv",
xlCSVWindows, , , , , , , , , , True
2014-07-21 8:18 GMT+07.00, him mah <himmah.mlg@gmail.com>:
> Assalamu'alaikum Wr. Wb.
>
> saya barusan nyari fungsi untuk konvert dari xls ke CSV, dan ketemu
> hasil seperti dibawah
>
> yang mau saya tanyakan adalah, setelah saya jalankan fungsi tersebut
> dan berhasil namun yang jadi masalah pembatas antar kolom nya pake
> koma (,) kalau ingin merubah untuk pembatasnya menjadi titik koma /
> semicolon(;) kira kira edit fungsinya seperti apa ya
>
> terima kasih
>
> fungsinya seperti ini
>
> '----------------------------------------------------------
> ' Procedure : ConvertXls2CSV
> ' Author : Daniel Pineault, CARDA Consultants Inc.
> ' Website : http://www.cardaconsultants.com
> ' Purpose : Converts a standard Excel file to csv format
> ' Requirements: Requires MS Excel be installed
> ' Uses late binding, so no libraries need be declared
> ' Copyright : The following may be altered and reused as you wish so long as
> the
> ' copyright notice is left unchanged (including Author, Website
> and
> ' Copyright). It may not be sold/resold or reposted on
> other sites (links
> ' back to this site are allowed).
> '
> ' Input Variables:
> ' ~~~~~~~~~~~~
> ' sXlsFile : Fully qualified path and filename with extension of the
> Excel workbook
> '
> ' Usage:
> ' ~~~~~~
> ' ConvertXls2CSV "C:\
> ' Will output a file C:\Users&#
> listing.csv
> '
> ' Revision History:
> ' Rev Date(yyyy/mm/
> '
> ************
> ' 1 2012-May-11 Initial Release - Answer to forum
> question
> '-------
> Function ConvertXls2CSV(
> On Error Resume Next
> Dim oExcel As Object
> Dim oExcelWrkBk As Object
> Dim bExcelOpened As Boolean 'Was Excel already open or not
> 'Review 'XlFileFormat Enumeration&#
> Const xlCSVWindows = 23 'Windows CSV Format
> Const xlCSV = 6 'CSV
> Const xlCSVMac = 22 'Macintosh CSV
> Const xlCSVMSDOS = 24 'MSDOS CSV
>
> Set oExcel = GetObject(, "Excel.
> instance of Excel
>
> If Err.Number <> 0 Then 'Could not get instance of Excel, so
> create a new one
> Err.Clear
> On Error GoTo Error_Handler
> Set oExcel = CreateObject(
> bExcelOpened = False
> Else 'Excel was already running
> bExcelOpened = True
> End If
>
> On Error GoTo Error_Handler
> oExcel.ScreenUpdati
> oExcel.Visible = False 'Keep Excel hidden from the user
> oExcel.Application.
>
> Set oExcelWrkBk = oExcel.Workbooks.
> 'Note: you may wish to change the file format constant for another
> type declared
> ' above based on your usage/needs in the following line.
> oExcelWrkBk.
> "csv"
> oExcelWrkBk.
>
> If bExcelOpened = False Then
> oExcel.Quit
> End If
>
> Error_Handler_
> On Error Resume Next
> Set oExcelWrkBk = Nothing
> Set oExcel = Nothing
> Exit Function
>
> Error_Handler:
> MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
> "Error Number: " & Err.Number & vbCrLf & _
> "Error Source: ConvertXls2CSV"
> "Error Description: " & Err.Description, _
> vbCritical, "An Error has Occured!"
> Resume Error_Handler_
> End Function
>
Sun Jul 20, 2014 9:03 pm (PDT) . Posted by:
"KESI VIZZIANTI"
Adakah yang salah dengan perintah di bawah ini?
waktu di lock kok tidak bisa insert koment di cell ya?
Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
On Error GoTo akhir
If wSheet.ProtectContents = True Then
wSheet.Unprotect Password:=TextBox1.Text
teruskan:
Else
wSheet.Protect Password:=TextBox1.Text, AllowFiltering:=True,
Scenarios:=True
End If
Next wSheet
Exit Sub
akhir:
MsgBox wSheet.Name & " : passwordnya salah !!!bukan : " & TextBox1,
48, "oops!"
GoTo teruskan
End Sub
--
*Kesi Vizzianti*
Email kesi.vizzianti@gmail.com
Phone 0812-77176028
Blog http://kesivizzianti.blogspot.com/
Twitter KesiVizzianti <https://twitter.com/KesiVizzianti>
waktu di lock kok tidak bisa insert koment di cell ya?
Private Sub CommandButton1_Click()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
On Error GoTo akhir
If wSheet.ProtectContents = True Then
wSheet.Unprotect Password:=TextBox1.Text
teruskan:
Else
wSheet.Protect Password:=TextBox1.Text, AllowFiltering:=True,
Scenarios:=True
End If
Next wSheet
Exit Sub
akhir:
MsgBox wSheet.Name & " : passwordnya salah !!!bukan : " & TextBox1,
48, "oops!"
GoTo teruskan
End Sub
--
*Kesi Vizzianti*
Email kesi.vizzianti@gmail.com
Phone 0812-77176028
Blog http://kesivizzianti.blogspot.com/
Twitter KesiVizzianti <https://twitter.com/KesiVizzianti>
Sun Jul 20, 2014 9:03 pm (PDT) . Posted by:
syafriaji
Dear Siti vi,
Assalamu Alaikum Wr. Wb.
Terinspirasi dari Terbilang yg anda buat, maka saya mencoba
untuk mengkombain "Terbilang 18 digit + 2 Desimal" dan Terbilang 27 Digit,
yang menghasilkan "Terbilang 27 digit + 2 Desimal serta bilangan Negatif"
File-nya dapat di unduh: https://app.box.com/s/dzt9atq23bwm454vvkpw https://app.box.com/s/dzt9atq23bwm454vvkpw
Tolong dikoreksi, terimakasih.
Wassalam
SCRAJ_471
Assalamu Alaikum Wr. Wb.
Terinspirasi dari Terbilang yg anda buat, maka saya mencoba
untuk mengkombain "Terbilang 18 digit + 2 Desimal" dan Terbilang 27 Digit,
yang menghasilkan "Terbilang 27 digit + 2 Desimal serta bilangan Negatif"
File-nya dapat di unduh: https://app.box.com/s/dzt9atq23bwm454vvkpw https://app.box.com/s/dzt9atq23bwm454vvkpw
Tolong dikoreksi, terimakasih.
Wassalam
SCRAJ_471
Sun Jul 20, 2014 9:26 pm (PDT) . Posted by:
"Hendrik Karnadi" hendrikkarnadi
Maaf oot...
Hai mustika,
Mungkin lebih pas kalau emailnya dikirim via kurir khusus ke heaven karena beliau mungkin sdh di sana sejak 2 th yang lalu.
Salam,
HK
Sent from Samsung Mobile"mustika.471@hotmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> wrote:Dear Siti vi,
Assalamu Alaikum Wr. Wb.
Terinspirasi dari Terbilang yg anda buat, maka saya mencoba
untuk mengkombain "Terbilang 18 digit + 2 Desimal" dan Terbilang 27 Digit,
yang menghasilkan "Terbilang 27 digit + 2 Desimal serta bilangan Negatif"
File-nya dapat di unduh: https://app.box.com/s/dzt9atq23bwm454vvkpw
Tolong dikoreksi, terimakasih.
Wassalam
SCRAJ_471
Hai mustika,
Mungkin lebih pas kalau emailnya dikirim via kurir khusus ke heaven karena beliau mungkin sdh di sana sejak 2 th yang lalu.
Salam,
HK
Sent from Samsung Mobile"mustika.471@hotmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> wrote:Dear Siti vi,
Assalamu Alaikum Wr. Wb.
Terinspirasi dari Terbilang yg anda buat, maka saya mencoba
untuk mengkombain "Terbilang 18 digit + 2 Desimal" dan Terbilang 27 Digit,
yang menghasilkan "Terbilang 27 digit + 2 Desimal serta bilangan Negatif"
File-nya dapat di unduh: https://app.box.com/s/dzt9atq23bwm454vvkpw
Tolong dikoreksi, terimakasih.
Wassalam
SCRAJ_471
Sun Jul 20, 2014 9:41 pm (PDT) . Posted by:
a.hafidz
Dear all master...
Kiranya bisa di terjemahkan ke bhs manusia code macro sebagai berikut :
Sub macro2()
ActiveSheet.Range(�$V$7:$W$564�) .AutoFilter Field:=1, Criteria1:=�<>�
ActiveWindow.SmallScroll Down:=-6
End sub
Mohon pencerhannya yah..
Trims.
Hafidz
Sent from my BlackBerry�
powered by Sinyal Kuat INDOSAT
Kiranya bisa di terjemahkan ke bhs manusia code macro sebagai berikut :
Sub macro2()
ActiveSheet.
ActiveWindow.
End sub
Mohon pencerhannya yah..
Trims.
Hafidz
Sent from my BlackBerry�
powered by Sinyal Kuat INDOSAT
Mon Jul 21, 2014 1:41 am (PDT) . Posted by:
"Emi" emilestariklcc
TemanBeExcel,
Bagaimana rumusnya agar Code di
Kolom C hanya keluar hanya sekali sekiranya mempunyai value yang sama supaya formula SUMPRODUCT tidak menghasilkan
out put yang ganda.
File seperti dilampirkan.
Terima kasih
-Emi Lestari
=====================================================================
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
Tidak ada komentar:
Posting Komentar