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

2019-07-12 Terurut Topik 'Mr. Kid' mr.nm...@gmail.com [belajar-excel] 
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 y.ron...@yahoo.com [belajar-excel] <
belajar-excel@yahoogroups.com> 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
Description: application/vnd.ms-excel.sheet.macroenabled.12


re-PATH.xlsm
Description: application/vnd.ms-excel.sheet.macroenabled.12

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

2019-07-11 Terurut Topik hendrik karnadi hendrikkarn...@yahoo.com [belajar-excel] 
 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, y.ron...@yahoo.com [belajar-excel] 
 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

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

2019-07-11 Terurut Topik y.ron...@yahoo.com [belajar-excel] 
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
Description: application/vnd.ms-excel.sheet.binary.macroenabled.12