Question

Locked

how do I import HTML page into EXCEL

By lysq ·
basically I am currently responsible for extracting and creating reports from the system.
I receive a report in a table but as a html web page(I select save as and them html).
Next I can rename the file to report.xls which is not the best but quite efficient way to open the same table in Excel.
I've prepared some nice macro's to import couple files at a time but and merge into one big report file however the report is not perfect and can't be sorted due to some merged cells.
Mainly when I do the report the tables in HTML are shown properly however in couple of description fields there was the ENTER sign used and thus when importing into Excel it turns out to be a merged cell.
and so I receive a row of data in the format:
http://imgur.com/s3Ucx.jpg
LINE1 and LINE2 are two separate cells because ENTER was used in the description.
I want this to be one cell in EXCEL.
Anyone got a solution or a hint what a macro should look like when importing such a file?
There are at least couple of rows like that in the report file and the rest is normal. I have around 2000 rows in a report each day and it would help me a lot to automatically remove those additional cells so 1 row would only include information about 1 entry.
Currently I am using:

Dim NoOfFiles As Integer
Dim name As String


NoOfFiles = Range("D6").Value

For i = 1 To NoOfFiles

name = i & ".xls"
Workbooks.Open Filename:=("C:\daily\" & name)
Workbooks.Open Filename:=("C:\daily\out.xls")
On Error GoTo ErrHandler

Workbooks("temp1.xls").Activate
Worksheets(2).Activate

If i = 1 Then
Workbooks(name).Activate
Dim LastCell As Range
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)


End With

CellCount1 = LastCell.Row
Workbooks(name).Worksheets(1).Range("a4:t" & CellCount1).Copy Workbooks("temp1.xls").Worksheets(2).Range("a2")
Workbooks(name).Activate
ThisWorkbook.Saved = True
ActiveWorkbook.Close

Else

Workbooks(name).Activate
Dim LastCell3 As Range
With ActiveSheet
Set LastCell3 = .Cells(.Rows.Count, "A").End(xlUp)

End With
CellCount1 = LastCell3.Row

Workbooks("temp1.xls").Activate
Dim LastCell2 As Range
Dim OstCell As Integer
With ActiveSheet
Set LastCell2 = .Cells(.Rows.Count, "A").End(xlUp)
End With

OstCell = LastCell2.Row + 1

Workbooks(name).Worksheets(1).Range("a4:t" & CellCount1).Copy Workbooks("temp1.xls").Worksheets(2).Range("a" & OstCell)
Workbooks(name).Activate
ThisWorkbook.Saved = True
ActiveWorkbook.Close

End If

Next i

Workbooks("temp1.xls").Activate
Worksheets(2).Activate
Dim FinalC As Range
With ActiveSheet
Set FinalC = .Cells(.Rows.Count, "A").End(xlUp)
End With


Fin = FinalC.Row

Workbooks("temp1.xls").Worksheets(2).Range("a2:t" & Fin).Copy Workbooks("out.xls").Worksheets(1).Range("a5")

Workbooks("out.xls").Activate
File_To_SaveAs_Name = Application.GetSaveAsFilename( _
fileFilter:="Excel files (*.xls), *.xls")
ActiveWorkbook.SaveAs Filename:=File_To_SaveAs_Name, CreateBackup:=False
ActiveWorkbook.Close

Workbooks("temp1.xls").Activate
ThisWorkbook.Saved = True
ActiveWorkbook.Close

this macro is not the best one but it does its job.
depending on cell D6 and the number in it, it imports as many files named 1.xls-n.xls, copies the proper range from those files and pastes them into my temp1.xls, if there is some data already in the first cell of temp1.xls, then it searches for the last cell with data and copies data from next file after the previous. then all data is transferred to already prepared output file and automatically saved asking for the name.
I would really appreciate help.

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

how do I import HTML page into EXCEL

by savindrasingh In reply to how do I import HTML page ...

You can modify this code to your need:

Sub websurf()
Const url As String = "file:///C:/Documents%20and%20Settings/nbkzpji/Desktop/Test/DSS-Automail.html"
Const OLECMDID_COPY = 12
Const OLECMDID_SELECTALL = 17
Const OLECMDEXECOPT_DODEFAULT = 0
Const OLECMDEXECOPT_PROMPTUSER = 1
Const OLECMDEXECOPT_DONTPROMPTUSER = 2
Const OLECMDEXECOPT_SHOWHELP = 3
Set ie = CreateObject("internetexplorer.application")
With ie
.Top = 1
.Left = 1
.Height = 400
.Width = 500
.AddressBar = False
.MenuBar = False
.Toolbar = False
.Visible = True
.Navigate url
Do While .ReadyState <> 4
DoEvents
Loop
.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
End With
ActiveSheet.Paste
ie.Quit
End Sub

Above code can copy all data from the given HTML file and paste it on excel sheet.

Back to Software Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums