| No | rumus | keterangan |
| 1 | FileCopy "3 data.xls", "3 data " & Format(Date - 2, "dddd,") & Format(Date - 2, "dd mmmm yyyy") & ".xls" | kegunaanya mengubah nama file menjadi nama file plu s tgl 2 hari mundur dari sekarang |
| 2 | Selection.AutoFilter | pasang filter atau lepaskan filter |
| 3 | Range("a2:y6000").SelectSelection.ClearContents | menghapus data pada range/ blok |
| 4 | Application.Run _ "'(3) PEMBANTU LKU.xls'!HAPUS_JURNAL" | menjalankan macro yg sdh disimpan |
| 5 | ActiveWorkbook.Save ActiveWindow.Close | menyimpan file dan menutup file |
| 6 | ChDir _ (Range("f1")) | ref tempat folder (diisi dengan rumus alamat folder file) |
| 7 | (Range("L4")) | referensi folder yg akan dibuka ditaruh di cell tertentu dengan rumus file |
| 8 | =LEFT(CELL("filename";J4);SEARCH("[";CELL("filename";J4))-2) | contoh formula dengan file info address |
| 9 | ............ | ........... http://mymacrosformula.blogspot.com |
| 10 | .....http://mymacrosformula.blogspot.com | ........... |
| 11 | ............ | .......... |
| 12 | ........... | .............http://mymacrosformula.blogspot.com |
| 13 | ................. | .......... |
| 14 | .........http://mymacrosformula.blogspot.com | ........... |
| 15 | Sheets("progress ").Select Sheets("progress ").Name = "pospaid" Sheets("progress (2)").Select Sheets("progress (2)").Name = "pospaid rinci" | merubah nama sheet dari progress ke pospaid |
| 16 | Sheets("CEK SELISIH data asli").Select Sheets("CEK SELISIH data asli").Copy ChDir "D:\1 interface pusat" ActiveWorkbook.SaveAs Filename:= _ (Range("m40")) | copy sheet dan menyimpan dgn nama baru sesuai referensi di cel m40 |
| 17 | Selection.NumberFormat = "ddmmyyyy" | format cell dengan format tanggal |
| 18 | Selection.AutoFilter Field:=2, Criteria1:="ZR" | filter kolom 2 dengan isi "ZR" |
| 19 | Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select | memblok sampai cell terakhir |
| 20 | Sheets("Sheet3").Name = "zrnonsapbatch" | mengganti nama sheet http://mymacrosformula.blogspot.com |
| 21 | ActiveWorkbook.BreakLink Name:= _ "D:\1 interface pusat\1 file induk cek interface.xls", Type:=xlExcelLinks | untuk memutuskan link dari file lain, sehingga value menjadi value tersendiri seperti tampilan http://mymacrosformula.blogspot.com |
| 22 | Rows("301:375").Select Selection.EntireRow.HIDDEN = True Columns("I:I").Select Selection.EntireColumn.HIDDEN = True | menyembunyikan baris (contoh baris 301 s/d 375 dan menyembunyikan kolom (contoh kolom i) http://mymacrosformula.blogspot.com |
| ini adalah formula excel yang sering kugunakan |
Tampilkan postingan dengan label copy file. Tampilkan semua postingan
Tampilkan postingan dengan label copy file. Tampilkan semua postingan
Rabu, 06 Juli 2011
Formula macro excel 1
Selasa, 05 Juli 2011
COPY FILE MENGGUNAKAN MACRO EXCEL VBA
| No | rumus | keterangan |
| 1 | How to get the active station and folder name: MsgBox "Active station and folder name: " & CurDir How to change the active station: ChDrive "F" ' changes to the F-station How to change the active folder: ChDir "F:\My Documents\Private" How to determine if a file exists in a folder: If Dir("F:\My Documents\My Workbook.xls") <> "" Then ' the file exists, returns "" (empty string) if the file doesn't exist. How to delete a file : Kill "F:\My Documents\My Workbook.xls" If you don't specify the station, Excel uses the active station. If you don't specify the folder, Excel uses the active folder. How to create a new folder: MkDir "NewPrivateFolder" ' creates a new folder in the active folder MkDir "F:\My Documents\NewPrivateFolder" ' creates a new folder in the existing folder F:\My Documents How to delete a folder (folder must be empty): RmDir "NewPrivateFolder" ' deletes the subfolder NewPrivateFolder in the active folder RmDir "F:\My Documents\NewPrivateFolder" ' deletes the subfolder NewPrivateFolder in the folder F:\My Documents How to copy a file (the file must be closed): FileCopy "OrgWorkBook.xls", "CopyWorkBook.xls" ' copies OrgWorkBook.xls to CopyWorkBook.xls in the active folder FileCopy "OrgWorkBook.xls", "F:\My Documents\CopyWorkBook.xls" ' copies OrgWorkBook.xls from the active folder to F:\My Documents\CopyWorkBook.xls How to move a file (the file must be closed): OldFilePath = "C:\OldFolder\Filename.xls" ' original file location NewFilePath = "C:\NewFolder\Filename.xls" ' new file location Name OldFilePath As NewFilePath ' move the file | |
| 2 | Sum ber |
Semua petunjuk diatas diambil dari situs di samping : http://www.exceltip.com/st/Basic_file_and_folder_examples_using_VBA_in_Microsoft_Excel/443.html |
| c | c | c |
Kegunaannya adalah kita dapat mengcopy file apapun atau backup, hapus hanya dengan membuka atau menjalankan macro dari excel atau membuat tombol di file excel untuk menjalankan proses copy file
Langganan:
Postingan (Atom)
