Hi Pls find the code and the file attached. The html files stored in the folder 'Folder' will be used for the report.
Sub Test()
Dim Fs As New FileSystemObject, Fl As File
Dim Fld As Folder, FolderPath As String
Dim WkBk As Workbook, DtWkBk As Workbook
Set WkBk = Workbooks.Add
NoShts = WkBk.Worksheets.Count
WkBk.SaveAs Filename:="Report"
FolderPath = ThisWorkbook.Worksheets("Sheet1").Range("B1")
Application.DisplayAlerts = False
Set Fld = Fs.GetFolder(FolderPath)
Cn = 1
For Each fls In Fld.Files
If fls.Type = "HTML Document" Then
Debug.Print fls.Type
Workbooks.Open Filename:=fls.Name
ActiveSheet.Cells.Copy
If Cn <= NoShts Then
WkBk.Worksheets(Cn).Activate
ActiveSheet.Paste
ActiveSheet.Name = Left(fls.Name, Len(fls.Name) - 4)
Cn = Cn + 1
Else
WkBk.Worksheets.Add
Cn = Cn + 1
WkBk.Worksheets(Cn).Activate
ActiveSheet.Paste
ActiveSheet.Name = Left(fls.Name, Len(fls.Name) - 4)
End If
End If
Next
Application.DisplayAlerts = True
End Sub
On Wed, Jul 6, 2011 at 4:59 PM, Chidurala, Shrinivas <
[email protected]> wrote:
> Dear Excel Gurus,
>
> I have some HTML files which are saved in same folder and I want to convert
> them into Excel and merger the all files into 1 workbook. Please help me to
> create the macro for the same. Find attached 2 HTML files and required
> report.
>
>
> Regards,
> Shrinivas
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to [email protected]
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
Regards
Vasant
skype Id: vasantjob
http://facebook.com/vasantjob
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
Code.xlsm
Description: Binary data
