Kalau mau dipendekin, pakai yang perlu2 saja, bahasa gahoolnya tunjek
poin, jadinya sbb:
Sub download()
Dim SaveName As Variant
Sheet1.UsedRange.Copy
Workbooks.Add
ActiveSheet.Paste
ActiveSheet.ListObjects.Add.Name = "Table2"
ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleLight9"
SaveName = Application.GetSaveAsFilename(fileFilter:="Excel
Workbook (*.xlsx), *.xlsx")
If SaveName <> False Then
ActiveWorkbook.SaveAs (SaveName)
Application.Dialogs(xlDialogSendMail).Show
End If
ActiveWorkbook.Close False
Application.CutCopyMode = False
End Sub
Rgds,
[dp]
Pada 01/07/2013 16:51, Ahmad Adriansyah menulis:
MantabPak Zainul,
Berikutini script jadinya :
‘--------------------------------------------------------------------------------------------------------------------------
Option Explicit
Sub download()
Dim dKey As Range
Dim dTbl As Range
Dim Direktori As String
Dim NamaFile As String
'variabel fr Zainul Ulum
Dim vInput As Variant
Set dKey = Sheet1.Range("a1")
Set dKey = Range(dKey, dKey.End(xlDown))
dKey.Name = "dKey"
Set dTbl = Range(dKey, dKey.End(xlToRight))
dTbl.Name = "dTabel"
dTbl.Copy
Workbooks.Add
ActiveSheet.Paste
ActiveSheet.ListObjects.Add.Name = "Table2"
Range("Table2[#All]").Select
ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleLight9"
ActiveWindow.DisplayGridlines = False
'fr Zainul Ulum
vInput = Application.GetSaveAsFilename(fileFilter:="Excel Workbook
(*.xlsx), *.xlsx")
If vInput <> False Then
ActiveWorkbook.SaveAs CStr(vInput)
End If
Application.CutCopyMode = False
‘Option jika workbook mau dikirim by e-mail langsung
Application.Dialogs(xlDialogSendMail).Show
End Sub
‘--------------------------------------------------------------------------------------------------------------------------
Regards,
AA
*From:*[email protected]
[mailto:[email protected]] *On Behalf Of
*zainul_ulum[at]yahoo[dot]com
*Sent:* 01 Juli 2013 15:54
*To:* [email protected]
*Subject:* RE: [belajar-excel] Download
Dear AA,
coba pakai method GetSaveAsFilename
Dim vInput As Variant
vInput = Application.GetSaveAsFilename(fileFilter:="Excel Workbook
(*.xlsx), *.xlsx")
If vInput <> False Then
ActiveWorkbook.SaveAs CStr(vInput)
End If
'Note:
kelemahannya adalah jika macro disimpan workbook yang aktif, maka file
macro akan hilang.
Agar macro nggak hilang sebaiknya maro disimpan di workbook yang lain
dan tidak menjadi satu dengan workbook yang akan disimpan.
thanks
-zainul-
From: [email protected]
<mailto:belajar-excel%40yahoogroups.com>
[mailto:[email protected]
<mailto:belajar-excel%40yahoogroups.com>] On Behalf Of Ahmad Adriansyah
Sent: Monday, July 01, 2013 2:11 PM
To: [email protected] <mailto:belajar-excel%40yahoogroups.com>
Subject: RE: [belajar-excel] Download
Dear Pak Hendrik,
Sudah saya perbaiki menjadi seperti ini :
Hanya pasa pada saat running, muncul error message :
Kalau menampilkan jendela ini kodingnya apa ya Pak ?
Untuk format filnya menjadi .xlsx ya Pak, bukan xlsm. Terima kaih.
Rgds,
AA
