Import html notepad file to excel cells

[dollraje]

Hi dear friends, i am struggled with copying data from html notepad file. please help for this task.

here my sample files www.transfernow.net/21jpp4k2x1fg

Thanks in advance!!

 

[dollraje]

sample files https://www.justbeamit.com/k9gdk

 

[dollraje]

sample files https://we.tl/FiHwmJjn6i

 

[dollraje]

Lolz!!! still no minimum response.

 

[Worf]

Hi

I opened the HTML file directly with Excel 2016, now it would be just a matter of adjusting the data layout to produce the desired table.
Would you like VBA code to do that?

 

[dollraje]

Helo worf....Yeah!!! Need VBA code to produce table of data from html data file...as specified pattern
Thank you for response...
Best wishes!!!

 

[dollraje]

Here i have attached detailed example format...

https://we.tl/8arWrBjsC0

Thank you..

 

[Worf]

Please test it:

Sub FillTable()
Dim c As Range, fa$, y(), i%, j%, ra(), r As Range, samp As Worksheet, fin As Worksheet, lr%, v
Set samp = Worksheets("sample")     ' HTML imported data
samp.[c:e].UnMerge
Set fin = Sheets("final")           ' table sheet
ReDim Preserve y(1 To 1)
With samp.Range("a1:a" & samp.Range("a" & Rows.Count).End(xlUp).Row)
    Set c = .Find("Design Code", LookIn:=xlValues)
    If Not c Is Nothing Then
        fa = c.Address
        i = 1
        Do
            ReDim Preserve y(1 To i)
            y(i) = c.Row
            i = i + 1
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> fa
    End If
End With
ReDim Preserve y(1 To i)
y(i) = samp.Range("a" & Rows.Count).End(xlUp).Row
BubbleSort y
ReDim ra(1 To UBound(y))
For j = 1 To UBound(ra) - 1
    ra(j) = "a" & y(j) & ":a" & (y(j + 1) - 1)
Next
For i = LBound(ra) To UBound(ra) - 1
    lr = fin.Range("b" & Rows.Count).End(xlUp).Row
    Set c = fin.[b3]
    For j = 1 To 6
        Set r = samp.Range(ra(i)).Find(c, , xlValues, xlPart)
        fin.Cells(lr + 1, 1 + j) = Replace(r.Offset(, 2), ".", ",") ' if your separator is "," and not "."
        Set c = c.Offset(, 1)
    Next
    Set r = samp.Range(ra(i)).Find("Footing Size", , xlValues, xlPart)
    v = Split(r.Offset(, 2), "X")
    fin.Cells(lr + 1, 9) = Val(v(2))
    v = Split(r.Offset(, 2), "=")
    If UBound(v) > 0 Then fin.Cells(lr + 1, 10) = Val(v(1))
    Set r = samp.Range(ra(i)).Find("Reinforcement Along L", , xlValues, xlPart)
    Set r = samp.Range(r, samp.Cells(samp.Range("a" & Rows.Count).End(xlUp).Row, 1))
    Set c = r.Find("Ast Prv", r.Cells(1, 1), xlValues, xlPart, xlByRows, xlNext)
    fin.Cells(lr + 1, 11) = c.Offset(, 2)
    fin.Cells(lr + 1, 12) = c.Offset(1, 2)
    Set r = samp.Range(ra(i)).Find("Reinforcement Along B", , xlValues, xlPart)
    Set r = samp.Range(r, samp.Cells(samp.Range("a" & Rows.Count).End(xlUp).Row, 1))
    Set c = r.Find("Ast Prv", r.Cells(1, 1), xlValues, xlPart, xlByRows, xlNext)
    fin.Cells(lr + 1, 13) = c.Offset(, 2)
    fin.Cells(lr + 1, 14) = c.Offset(1, 2)
Next
End Sub


Sub BubbleSort(List())
Rem Sorts the List array in ascending order
Dim First As Long, Last As Long, i As Long, j&, Temp$
First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
    For j = i + 1 To Last
        If List(i) > List(j) Then
            Temp = List(j)
            List(j) = List(i)
            List(i) = Temp
        End If
    Next
Next
End Sub

 

[dollraje]

Please test it:

Thank you for helping. i have tested but data not developed. can you guide me for instructions, for how to use your code..
if any possibility please attach excel file with code.


Best wishes...

 

[Worf]

o Link to workbook below.
o Note that the HTML has “Analysis No” and the final table had “Analysis No.”, this can make a difference, I removed the dot.
o Run the fill table routine to transfer data from one sheet to another.

https://www.dropbox.com/s/cvcwkuym8ghw3bt/sample raw file.xlsm?dl=0