tag:blogger.com,1999:blog-28492262.post7327636431065145830..comments2023-12-27T22:22:25.151-08:00Comments on VBA Tips & Tricks: Delete Comments from Excel Workbook using VBAUnknownnoreply@blogger.comBlogger1125tag:blogger.com,1999:blog-28492262.post-46744978962915591542010-12-16T04:06:07.705-08:002010-12-16T04:06:07.705-08:00Sub ShowFolderList()
Dim fs, f, f1, fc, s, fr, r, ...Sub ShowFolderList()<br />Dim fs, f, f1, fc, s, fr, r, D, WSN<br />Dim folderspec<br />Dim I As Integer<br /><br />'Delete all the worksheets<br /> Workbooks.Add<br /> Application.ScreenUpdating = False<br /> Application.DisplayAlerts = False<br /> For I = ActiveWorkbook.Worksheets.Count To 1 Step -1<br /> If Worksheets(I).Name <> "Sheet1" Then Worksheets(I).Delete<br /> Next I<br /><br />'Insert worksheet with folder name<br /> folderspec = "D:\Dinakaran" 'CurDir()<br /> Set fs = CreateObject("Scripting.FileSystemObject")<br /> Set f = fs.GetFolder(folderspec)<br /> Set fc = f.SubFolders<br /> For Each f1 In fc<br /> With ActiveWorkbook.Sheets.Add<br /> ActiveSheet.Name = f1.Name<br /> End With<br /> Next<br /> <br />'Delete Sheet1<br /> ActiveWorkbook.Sheets("Sheet1").Delete<br /> Application.ScreenUpdating = True<br /> Application.DisplayAlerts = True<br /> <br />'List all the files<br />For Each xlSheet In ActiveWorkbook.Worksheets<br />WSN = xlSheet.Name<br />D = "D:\Dinakaran\" & WSN & "\"<br />xlSheet.Cells(1, 1) = "Filenames"<br />r = 2<br />fr = Dir(D, 7)<br />Do While fr <> ""<br /> xlSheet.Cells(r, 1) = fr<br /> r = r + 1<br /> fr = Dir<br />Loop<br />'r = 0<br />Next xlSheet<br />End SubAnonymousnoreply@blogger.com