Salam Be-Exceller.
Ini melanjutkan topik tentang cek file di server.
Kalau ada kasus VBA seperti ini :
Private sub ContohKodeSaya()
Dim Alamatfile as string
Dim Mywb as workbook,Wbbaru as workbook
Dim wbbaruSh as worksheet,MywbSh as worksheet
On error goto Keluar
Alamatfile="D:\\myfolder\mysubfolder\namafilesaya.xlsx"
'''Buka file dari server
BukaFile Alamatfile,"PasswordSaya"
Set Mywb=ThisWorkbook
Set Wbbaru=workbooks("namafilesaya.xlsx")
Wbbaru.activate
Set WbbaruSh=Wbbaru.worksheets("mySheet")
With WbbaruSh
.Cells(1,1).value="XXX"
'''Code lain...........
End with
Mywb.activate
Set MywbSh=Mywb.worksheets("mySheetJuga")
With MywbSh
.Cells(1,1).value="YYY"
'''Code lain...
End with
Wbbaru.close true ---------------> Saya selalu error disini.
Set mywb=nothing
Set wbbaru=nothing
Set wbbaruSh=nothing
Set MywbSh=nothing
Keluar:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Err.Clear
On Error GoTo 0
End sub
Pertanyaannya :
Karena baris Wbbaru.close true selalu error, maka saya mau menangkap nomor
Error nya sehingga bisa dihandle.
Bagaimana cara penulisannya kalau dengan kondisi diatas (multiple error
handling). Apakah yang benar seperti ini ?(Yaitu dengan 2 error handling,
Mylabel dan Keluar)
Private sub ContohKodeSaya()
Dim Alamatfile as string
Dim Mywb as workbook,Wbbaru as workbook
Dim wbbaruSh as worksheet,MywbSh as worksheet
On error goto Keluar
Alamatfile="D:\\myfolder\mysubfolder\namafilesaya.xlsx"
'''Buka file dari server
BukaFile Alamatfile,"PasswordSaya"
Set Mywb=ThisWorkbook
Set Wbbaru=workbooks("namafilesaya.xlsx")
Wbbaru.activate
Set WbbaruSh=Wbbaru.worksheets("mySheet")
With WbbaruSh
.Cells(1,1).value="XXX"
'''Code lain.........
End with
Mywb.activate
Set MywbSh=Mywb.worksheets("mySheetJuga")
With MywbSh
.Cells(1,1).value="YYY"
'''Code lain...
End with
On error goto Mylabel
Wbbaru.close true
Set mywb=nothing
Set wbbaru=nothing
Set wbbaruSh=nothing
Set MywbSh=nothing
Exit sub
Mylabel:
Msgbox "Nomor Errornya :" & Err.number &" dengan deskripsi error sbb :
" & Err.description
Keluar:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Err.Clear
On Error GoTo 0
End sub
Terima kasih atas bimbingannya semoga amal baiknya diterima disisiNya. Amiin.
Tio
