[belajar-excel] Mencari Alamat File (Path), NEXT ke kolom B6

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

[belajar-excel] Mencari Alamat File (Path), NEXT ke kolom B6

yusman
Dear Master Vba,
 

 Terlampir File untuk mencari Alamat File (Path).
 Yang Saya butuhkan, yaitu saat NEXT ke kolom berikutnya (B6) tanpa menimpa kolom sebelumnya. Mohon dibantu koreksi scriptnya berikut:
 

 

 Sub Subfoldersandfolders()
 Dim FSO As Object
 Dim FSOFolder As Object
 Dim Objfile As Object
 Dim subfd As Object
 Dim x As String
 

 

 

 

 Application.FileDialog(msoFileDialogFolderPicker).Show
 x = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
 Set FSO = CreateObject("Scripting.FileSystemObject")
 i = 3
 Set FSOFolder = FSO.GetFolder(x)
 

 'Lis File in Folders
 For Each Objfile In FSOFolder.Files
 'Cells(i, 1) = Objfile.Name
 Cells(i, 2) = Objfile.Path
 i = i + 1
 Next
 

 'Lis File in SubFolders
 For Each subfd In FSOFolder.SubFolders
 For Each Objfile In subfd.Files.Count
 'Cells(i, 1) = Objfile.Count
 Cells(i, 2) = Objfile.Path
 i = i + 1
 Next
 Next
 End Sub
 

 

 

 Terimakasih
 

 

 

 

 Regards,
 Roni

PATH.xlsb (24K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Mencari Alamat File (Path), NEXT ke kolom B6

hendrik karnadi
 Dear Roni,Coba code ini,Sub Subfoldersandfolders()    Dim FSO As Object    Dim FSOFolder As Object    Dim Objfile As Object    Dim subfd As Object    Dim x As String    Dim i As Integer 'baris data    Dim n As Integer 'no urut        Application.FileDialog(msoFileDialogFolderPicker).Show    x = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)    Set FSO = CreateObject("Scripting.FileSystemObject")    Set FSOFolder = FSO.GetFolder(x)    i = 3: n = 1        'Lis File in Folders    For Each Objfile In FSOFolder.Files        'Cells(i, 1) = Objfile.Name        Cells(i, 1) = n        Cells(i, 2) = Objfile.Path        i = i + 1        n = n + 1    Next        'Lis File in SubFolders    For Each subfd In FSOFolder.SubFolders        'For Each Objfile In subfd.Files.Count        For Each Objfile In subfd.Files            'Cells(i, 1) = Objfile.Count            Cells(i, 1) = n            Cells(i, 2) = Objfile.Path            i = i + 1            n = n + 1        Next    NextEnd Sub
Salam,HK

    Pada Kamis, 11 Juli 2019 17.10.42 GMT+7, [hidden email] [belajar-excel] <[hidden email]> menulis:  
 
    


Dear Master Vba,




Terlampir File untuk mencari Alamat File (Path).

Yang Saya butuhkan, yaitu saat NEXT ke kolom berikutnya (B6) tanpa menimpa kolom sebelumnya. Mohon dibantu koreksi scriptnya berikut:







Sub Subfoldersandfolders()

Dim FSO As Object

Dim FSOFolder As Object

Dim Objfile As Object

Dim subfd As Object

Dim x As String













Application.FileDialog(msoFileDialogFolderPicker).Show

x = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)

Set FSO = CreateObject("Scripting.FileSystemObject")

i = 3

Set FSOFolder = FSO.GetFolder(x)




'Lis File in Folders

For Each Objfile In FSOFolder.Files

'Cells(i, 1) = Objfile.Name

Cells(i, 2) = Objfile.Path

i = i + 1

Next




'Lis File in SubFolders

For Each subfd In FSOFolder.SubFolders

For Each Objfile In subfd.Files.Count

'Cells(i, 1) = Objfile.Count

Cells(i, 2) = Objfile.Path

i = i + 1

Next

Next

End Sub










Terimakasih













Regards,

Roni
   
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Mencari Alamat File (Path), NEXT ke kolom B6

Kid
Administrator
In reply to this post by yusman
Kalau pakai VBA dan PowerQuery (lebih cepat) di
file xlfilefolderlist_vba_n_pq.xlsm
Kalau pakai VBA dengan fitur FSO di file re-PATH.xlsm

Regards,
Kid

On Thu, Jul 11, 2019 at 5:10 PM [hidden email] [belajar-excel] <
[hidden email]> wrote:

>
>
> Dear Master Vba,
>
>
> Terlampir File untuk mencari Alamat File (Path).
>
> Yang Saya butuhkan, yaitu saat NEXT ke kolom berikutnya (B6) tanpa menimpa
> kolom sebelumnya. Mohon dibantu koreksi scriptnya berikut:
>
>
>
> Sub Subfoldersandfolders()
>
> Dim FSO As Object
>
> Dim FSOFolder As Object
>
> Dim Objfile As Object
>
> Dim subfd As Object
>
> Dim x As String
>
>
>
>
>
> Application.FileDialog(msoFileDialogFolderPicker).Show
>
> x = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
>
> Set FSO = CreateObject("Scripting.FileSystemObject")
>
> i = 3
>
> Set FSOFolder = FSO.GetFolder(x)
>
>
> 'Lis File in Folders
>
> For Each Objfile In FSOFolder.Files
>
> 'Cells(i, 1) = Objfile.Name
>
> Cells(i, 2) = Objfile.Path
>
> i = i + 1
>
> Next
>
>
> 'Lis File in SubFolders
>
> For Each subfd In FSOFolder.SubFolders
>
> For Each Objfile In subfd.Files.Count
>
> 'Cells(i, 1) = Objfile.Count
>
> Cells(i, 2) = Objfile.Path
>
> i = i + 1
>
> Next
>
> Next
>
> End Sub
>
>
>
>
> Terimakasih
>
>
>
>
>
> Regards,
>
> Roni
>
>
>

xlfilefolderlist_vba_n_pq.xlsm (41K) Download Attachment
re-PATH.xlsm (31K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] Re: Mencari Alamat File (Path), NEXT ke kolom B6

yusman
Dear Mr Kid,

Saya masih gagal paham??



Regards,
Roni
Reply | Threaded
Open this post in threaded view
|

[belajar-excel] Re: Mencari Alamat File (Path), NEXT ke kolom B6

yusman
In reply to this post by hendrik karnadi
Dear Mr Hendrik,

Saya sudah coba scriptnya, namun file tidak next ke kolom berikutnya justru menimpa kembali kolom sebelumnya. Mohon dibantu lagi. Terimakasih



Regards,
Roni
Kid
Reply | Threaded
Open this post in threaded view
|

Re: [belajar-excel] Re: Mencari Alamat File (Path), NEXT ke kolom B6

Kid
Administrator
In reply to this post by yusman
Lah kan tinggal milih, mau pakai file attachment yang pertama
(xlfilefolderlist_vba_n_pq.xlsm) atau yang kedua (re-PATH.xlsm)

Malah saya yang jadinya gagal paham,
kok ya bisa dua bijik file itu bikin gagal paham

1. kalau pilih xlfilefolderlist_vba_n_pq.xlsm
    > syarat : PowerQuery harus sudah ter-install dan aktif -> ini fitur
gratisan yang bisa di-unduh di MS
    > contoh pilih folder, lalu ok (seperti gambar 1 dan 2 berikut )

[image: Capture31.JPG]

[image: Capture32.JPG]

         trus pilh foder lagi, lalu di OK (seperti gambar 3 dan 4 berikut) :

[image: Capture33.JPG]

[image: Capture34.JPG]

      *** lha jadinya cakep gitu loh... lengkap dengan folder sub folder,
fullfilename (path), ada extension nya, bisa difilter si extensionnya. Kok
ya bikin gagal paham sih...
           saya malah jadi gagal paham deh...

2. atau mau pakai file re-Path.xlsm
    > klik tombol [Path] pilih folder, lalu OK (seperti gambar 1 dan 2
berikut) :

[image: Capture41.JPG]

[image: Capture42.JPG]


       lalu klik tombol [Path] lagi, dan pilih folder lagi trus OK (seperti
gambar 3 dan 4 berikut) :

[image: Capture43.JPG]

[image: Capture44.JPG]


       *** Lah kan juga cakep, walaupun lebih cakep yang file 1 di atas
tadi. Lengkap semua file dalam setiap subfolder nya juga loh. Kok ya bisa
bikin gagal paham loh
            Saya jadi lebih gagal paham lagi deh... lha yang file ini
semuanya seperti file dari Anda...

Mungkin yang bikin gagal paham itu bunyi VBA-nya ya ?
> lah script VBA milik Anda kan cuma bisa baca difolder itu dan satu level
subfolder, makanya gak bikin gagal paham, lah wong kurang seru kemampuannya
baca sub\sub\folder nya

Kalau di-file 1, yang bikin gagal paham script VBA-nya yang cuma ngatur
prosesnya ?
> lah semua proses utama mendaftar file sudah diserahkan ke PowerQuery
kok.. jadinya kan gak perlu ribet mikirin caranya baca daftar file lagi.
Biar PowerQuery ajah yang kerja

Regards,
Kid




On Tue, Jul 16, 2019 at 3:29 PM [hidden email] [belajar-excel] <
[hidden email]> wrote:

>
>
> Dear Mr Kid,
>
> Saya masih gagal paham??
>
>
>
> Regards,
> Roni
>
>

Capture31.JPG (115K) Download Attachment
Capture32.JPG (74K) Download Attachment
Capture33.JPG (124K) Download Attachment
Capture34.JPG (315K) Download Attachment
Capture41.JPG (80K) Download Attachment
Capture42.JPG (25K) Download Attachment
Capture43.JPG (97K) Download Attachment
Capture44.JPG (173K) Download Attachment