Thanx atas bantuan temen2 bilie christiansen, s.kom -----Original Message----- From: Wadi Achmed <[email protected]> Sender: [email protected] Date: Mon, 11 Oct 2010 14:18:39 To: <[email protected]> Reply-To: [email protected] Subject: Re: [indopb] Mau Tanya dong
Sorry kelewatan. konstanta xlUp = -4162 cheers, Wadi Achmed 2010/10/11 Wadi Achmed <[email protected]> > LastRow=Sheet.Range("A65536").End(xlUp).Row. > > > Kalo mau import yang lebih cepet: Dari excel saveas ke tab separated, lalu > import ke datawindow dan update + commit. > Selesai deh. > > Syntax nya gimana? monggo digoogling. > > Cheers, > Wadi ACh > > 2010/10/11 Afrianto Dulbahri ST. <[email protected]> > > 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 >>> >>> >>> >> >
