[belajar-excel] Download

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] Download

Ahmad Adriansyah
Dear Exceler,
Bagaimana caranya ketika kita akan download data dan save as file lain, sebelum tersave maka akan muncul directory dulu dan kita member nama file dan setelah itu baru save.

Berikut ini koding yang saya buat :

--------------------------------------------------------------------------------
Sub download()
   Dim dKey As Range
   Dim dTbl As Range

   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
    ActiveWorkbook.SaveAs ("Data hasil download")

End Sub
--------------------------------------------------------------------------------

Terima kasih.
Rgds,

AA

Download.xlsm (29K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Download

hendrik karnadi
Coba ganti Code
ActiveWorkbook.SaveAs ("Data hasil download")

dengan
Direktori = InputBox("Masukkan Nama Dir dan Sub Dir mis D:\DirBaru")

NamaFile = InputBox("Masukkan Nama File")
MkDir Direktori
ChDir Direktori
ActiveWorkbook.SaveAs Filename:=Direktori & "\" & NamaFile & ".xlsm", FileFormat:= _
      xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Salam,
HK



________________________________
 From: Ahmad Adriansyah <[hidden email]>
To: "[hidden email]" <[hidden email]>
Sent: Monday, 1 July 2013, 12:51
Subject: [belajar-excel] Download
 


 
Dear Exceler,
Bagaimana caranya ketika kita akan download data dan save as file lain, sebelum tersave maka akan muncul directory dulu dan kita member nama file dan setelah itu baru save.
 
Berikut ini koding yang saya buat :
 
--------------------------------------------------------------------------------
Sub download()
   Dim dKey As Range
   Dim dTbl As Range
  
   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
    ActiveWorkbook.SaveAs ("Data hasil download")
 
End Sub
--------------------------------------------------------------------------------
 
Terima kasih.
Rgds,
 
AA
 
Reply | Threaded
Open this post in threaded view
|

RE: [belajar-excel] Download

Ahmad Adriansyah
Dear Pak Hendrik,
Sudah saya perbaiki menjadi seperti ini :

[cid:image005.jpg@01CE7664.C8E52270]

Hanya pasa pada saat running, muncul error message :

[cid:image006.jpg@01CE7664.C8E52270]

Kalau menampilkan jendela ini kodingnya apa ya Pak ?

[cid:image007.jpg@01CE7664.C8E52270]

Untuk format filnya menjadi .xlsx ya Pak, bukan xlsm. Terima kaih.

Rgds,
AA

From: [hidden email] [mailto:[hidden email]] On Behalf Of hendrik karnadi
Sent: 01 Juli 2013 13:38
To: [hidden email]
Subject: Re: [belajar-excel] Download


Coba ganti Code
ActiveWorkbook.SaveAs ("Data hasil download")

dengan
Direktori = InputBox("Masukkan Nama Dir dan Sub Dir mis D:\DirBaru")
NamaFile = InputBox("Masukkan Nama File")
MkDir Direktori
ChDir Direktori
ActiveWorkbook.SaveAs Filename:=Direktori & "\" & NamaFile & ".xlsm", FileFormat:= _
      xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Salam,
HK


________________________________
From: Ahmad Adriansyah <[hidden email]>
To: "[hidden email]" <[hidden email]>
Sent: Monday, 1 July 2013, 12:51
Subject: [belajar-excel] Download


Dear Exceler,
Bagaimana caranya ketika kita akan download data dan save as file lain, sebelum tersave maka akan muncul directory dulu dan kita member nama file dan setelah itu baru save.

Berikut ini koding yang saya buat :

--------------------------------------------------------------------------------
Sub download()
   Dim dKey As Range
   Dim dTbl As Range

   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
    ActiveWorkbook.SaveAs ("Data hasil download")

End Sub
--------------------------------------------------------------------------------

Terima kasih.
Rgds,

AA



image005.jpg (49K) Download Attachment
image006.jpg (43K) Download Attachment
image007.jpg (141K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Download

hendrik karnadi
Dear AA,

Maaf, dalam pertanyaan awal Option Expicitnya ga kelihatan.
Option Explicit mengharuskan semua Variable dideklarasikan.

Kalau mau pake Option Explicit Variable Direktori dan Nama File harus dideklarasikan dulu dengan Dim Direktori as ..., Dim NamaFile as .... (coba isi .....).

Filenya mengandung macro.
Untuk file yang mengandung Macro (Excel 2007 ke atas) hanya ada 3 pilihan format:
.xls (jika di save as Excel 97-2003)
.xlsb (jika di save as binary)
.xlsm (jika di save as Macro_Enabled Workbook)

Semoga penjelasannya bisa dipahami....

Salam,
HK



________________________________
 From: Ahmad Adriansyah <[hidden email]>
To: "[hidden email]" <[hidden email]>
Sent: Monday, 1 July 2013, 14:10
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
 
From:[hidden email] [mailto:[hidden email]] On Behalf Of hendrik karnadi
Sent: 01 Juli 2013 13:38
To: [hidden email]
Subject: Re: [belajar-excel] Download
 
 
Coba ganti Code
ActiveWorkbook.SaveAs ("Data hasil download")
 
dengan
Direktori = InputBox("Masukkan Nama Dir dan Sub Dir mis D:\DirBaru")
NamaFile = InputBox("Masukkan Nama File")
MkDir Direktori
ChDir Direktori
ActiveWorkbook.SaveAs Filename:=Direktori & "\" & NamaFile & ".xlsm", FileFormat:= _
      xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
 
Salam,
HK
 
 

________________________________
 
From:Ahmad Adriansyah <[hidden email]>
To: "[hidden email]" <[hidden email]>
Sent: Monday, 1 July 2013, 12:51
Subject: [belajar-excel] Download
 
 
Dear Exceler,
Bagaimanacaranya ketika kita akan download data dan save as file lain, sebelum tersave maka akan muncul directory dulu dan kita member nama file dan setelah itu baru save.
 
Berikutini koding yang saya buat :
 
--------------------------------------------------------------------------------
Sub download()
   Dim dKey As Range
   Dim dTbl As Range
  
   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
    ActiveWorkbook.SaveAs ("Data hasil download")
 
End Sub
--------------------------------------------------------------------------------
 
Terimakasih.
Rgds,
 
AA
 
 

image005.jpg (49K) Download Attachment
image006.jpg (43K) Download Attachment
image007.jpg (141K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

RE: [belajar-excel] Download

Zainul Ulum
In reply to this post by Ahmad Adriansyah
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: [hidden email] [mailto:[hidden email]] On Behalf Of Ahmad Adriansyah
Sent: Monday, July 01, 2013 2:11 PM
To: [hidden email]
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
 

Reply | Threaded
Open this post in threaded view
|

RE: [belajar-excel] Download

Ahmad Adriansyah
Mantab Pak Zainul,

Berikut ini 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: [hidden email] [mailto:[hidden email]] On Behalf Of zainul_ulum[at]yahoo[dot]com
Sent: 01 Juli 2013 15:54
To: [hidden email]
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: [hidden email]<mailto:belajar-excel%40yahoogroups.com> [mailto:[hidden email]<mailto:belajar-excel%40yahoogroups.com>] On Behalf Of Ahmad Adriansyah
Sent: Monday, July 01, 2013 2:11 PM
To: [hidden email]<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


Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Download

De Premor
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:*[hidden email]
> [mailto:[hidden email]] *On Behalf Of
> *zainul_ulum[at]yahoo[dot]com
> *Sent:* 01 Juli 2013 15:54
> *To:* [hidden email]
> *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: [hidden email]
> <mailto:belajar-excel%40yahoogroups.com>
> [mailto:[hidden email]
> <mailto:belajar-excel%40yahoogroups.com>] On Behalf Of Ahmad Adriansyah
> Sent: Monday, July 01, 2013 2:11 PM
> To: [hidden email] <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
>
>