Kalo pake macro di Xls
kira2 gini mas
Application.Goto Reference:="R65536C1"
Selection.End(xlUp).Select
baris = Excel.Application.Selection.Row - 1
MsgBox "Jumlah Baris dikurangi Header adalah " & baris
Monggo diimplemantasikan di PB nya :)
Afrianto Dulbahri ST. wrote:
Om Wadi,
Setuju sih... cuma, saya tahunya cara ini aja. Mungkin Om Wadi
bisa memberikan pencerahan yang lebih sip... :)
Pengalaman saya, import data dengan cara yang saya gunakan di
Intel Dual Core 1.7 GHz / 2 GB RAM dari file Excel yang ada di harddisk
(10 kolom, ± 4000 baris), perlu waktu sekitar 50 detik.
Salam,
Afri
>>> Wadi Achmed <[email protected]> 10/11/2010 2:45
PM >>>
Haaaah gak ada cara yang lebih
efisien kah?
Kasihan
dong processor dan memory nya.
Kenapa
gak memanfaatkan Range & matematika sederhana?
Cheers,
Wadi Achmed
On Mon, Oct 11, 2010 at 1:40 PM, Afrianto
Dulbahri ST. <[email protected]>
wrote:
Dear Bilie,
Mengacu ke jawaban saya sebelumnya, dilakukan iterasi terlebih
dahulu.... Contoh script-nya adalah:
/* SEARH FOR NUMBER OF ROWS IN EXCEL */
i = 2
anemployee_id =
lole_excel.application.Sheets("Sheet1").Cells(1,1).value
DO WHILE ISNULL(anemployee_id) = FALSE
i = i + 1
anemployee_id =
lole_excel.application.Sheets("Sheet1").Cells(i,1).value
// MessageBox("Baris ke : " + string(i), "Nilai Tanggal " +
STRING(antanggal))
LOOP
inbaris = i - 1
-----
Asumsinya, Row pertama adalah header, data yang diimport ada
di "Sheet1", dan data pada kolom pertama jika data akan menandakan
bahwa baris tersebut akan diimport.
Variable inbaris pada script di atas akan menghasilkan jumlah
baris yang akan diimport.
Salam,
Afri
>>> "bilie" < [email protected]>
10/11/2010 2:31 PM >>>
Thanx pak,.. Nanya lagi nh,.. Lanjut dri kasus yg sama,.. Kalo
udh connect ke excel dan kita mau mengetahui brp jumlah bris yg ada di
file excel tersbt, gmn yah fungsiny? Thanx pak
bilie christiansen, s.kom
-----Original Message-----
From: "Afrianto Dulbahri ST." <[email protected]>
Sender: [email protected]
Date: Fri, 8 Oct 2010 13:34:27
To: <[email protected]>
Reply-To: [email protected]
Subject: Re: [indopb] Mau Tanya dong
Dear Bilie,
Berikut ini adalah contohnya.
Salam,
Afri
>>>>
ANY anemployee_id, ansurname, anchperiod, anchpromoted_flag,
andcpromotion_percentage, andcgeneral_increase
ANY andcinternal_factor, andccompa_ratio, andccompetitiveness_factor
STRING employee_id, surname, chperiod, chpromoted_flag
DECIMAL dcpromotion_percentage, dcgeneral_increase
DECIMAL dcinternal_factor, dccompa_ratio, dccompetitiveness_factor
INTEGER ll_result
LONG i, j, inbaris
STRING chfile_location, chremark, chdummy
DATE dtshiftdate
oleobject lole_excel
lole_excel = create oleobject
string docname, named
integer value
value = GetFileOpenName("Select File", docname, named, "XLS", "Excel
97-2003 Files (*.XLS), *.XLS, Excel 2007 Files (*.XLSX), *.XLSX")
//MessageBox("File Yang dipilih", docname)
IF value = 1 THEN
dw_gi_increment.Reset()
ll_result = lole_excel.ConnectToNewObject("Excel.Application")
lole_excel.application.workbooks.open(docname)
lole_excel.application.Sheets("Sheet1").Select
// Baca File Excel-nya
/* SEARH FOR NUMBER OF ROWS IN EXCEL */
i = 2
anemployee_id =
lole_excel.application.Sheets("Sheet1").Cells(1,1).value
DO WHILE ISNULL(anemployee_id) = FALSE
i = i + 1
anemployee_id =
lole_excel.application.Sheets("Sheet1").Cells(i,1).value
// MessageBox("Baris ke : " + string(i), "Nilai Tanggal " +
STRING(antanggal))
LOOP
inbaris = i - 1
// MessageBox("Jumlah Baris", STRING(inbaris, "#,##0"))
FOR i = 2 to inbaris
st_2.Text = STRING(i - 1, "#,##0")
anemployee_id =
lole_excel.application.Sheets("Sheet1").Cells(i,1).value
ansurname = lole_excel.application.Sheets("Sheet1").Cells(i,2).value
anchperiod = lole_excel.application.Sheets("Sheet1").Cells(i,3).value
anchpromoted_flag =
lole_excel.application.Sheets("Sheet1").Cells(i,4).value
andcpromotion_percentage =
lole_excel.application.Sheets("Sheet1").Cells(i,5).value
andcgeneral_increase =
lole_excel.application.Sheets("Sheet1").Cells(i,6).value
andcinternal_factor =
lole_excel.application.Sheets("Sheet1").Cells(i,7).value
andccompa_ratio =
lole_excel.application.Sheets("Sheet1").Cells(i,8).value
andccompetitiveness_factor =
lole_excel.application.Sheets("Sheet1").Cells(i,9).value
employee_id = STRING(anemployee_id)
surname = STRING(ansurname)
chperiod = STRING(anchperiod)
chpromoted_flag = STRING(anchpromoted_flag)
dcpromotion_percentage = DEC(andcpromotion_percentage)
dcgeneral_increase = DEC(andcgeneral_increase)
dcinternal_factor = DEC(andcinternal_factor)
dccompa_ratio = DEC(andccompa_ratio)
dccompetitiveness_factor = DEC(andccompetitiveness_factor)
dw_gi_increment.InsertRow(0)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(), "employee_id",
employee_id)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(), "surname", surname)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(), "chperiod",
chperiod)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(), "chpromoted_flag",
chpromoted_flag)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(),
"dcpromotion_percentage", dcpromotion_percentage)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(),
"dcgeneral_increase", dcgeneral_increase)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(),
"dcinternal_factor", dcinternal_factor)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(), "dccompa_ratio",
dccompa_ratio)
dw_gi_increment.SetItem(dw_gi_increment.RowCount(),
"dccompetitiveness_factor", dccompetitiveness_factor)
st_4.Text = STRING(dw_gi_increment.RowCount(), "#,##0")
NEXT
delete ellipse.tblsim_grade_promotion
using sqlca;
commit using sqlca;
dw_gi_increment.Update()
COMMIT using sqlca;
// END OF READ Excel
lole_excel.application.workbooks.close()
lole_excel.application.visible = true
if isvalid(lole_excel) then destroy lole_excel
ELSE
MessageBox("File Open Error", "Can not Open the file")
END IF
>>> "bilie" <[email protected]>
10/8/2010 12:22 PM >>>
Para master, aku mau tanya nih,.. Gmana carany import record dri file
excel to datawindow? Mohon penjelasanny agak detail, maklum bru bljr
nih,hehehe thank you
bilie christiansen, s.kom
__._,_.___
-----------------------------------------------
IndoPB - Indonesia PB User Group
http://groups.yahoo.com/group/indopb
![]()
__,_._,___
|
