Export the data from Excel to HTML page

State the difference between HTML and VBA

HTML is a TAG based language
VBA is a Object Oriented lanugage

Data Extraction -- from Text file

We can extract the data through:

File System Object: FSO -- by considering Text file as object
Workbooks.Opentext Method

In this process once we selected the text file., data will import, by creating new workbook

In the newly created workbook, we can perform our required operations by using VBA

Click the below image to watch the video

Sub ImportDataFromTextFile()
Dim filename As String
filename = Application.GetOpenFilename()
Workbooks.OpenText filename:=filename, startrow:=1, DataType:=xlDelimited, Comma:=True
Dim wkb As Workbook
Set wkb = ActiveWorkbook
Dim sh As Worksheet
Set sh = wkb.ActiveSheet
UserForm1.TextBox1.Value = filename
UserForm1.ComboBox1.Clear
UserForm1.ComboBox2.Clear
For i = 1 To sh.UsedRange.Rows.Count
If i = 1 Then
For c = 1 To sh.Range("A1").End(xlToRight).Column
UserForm1.ListBox1.AddItem c & "-" & sh.Cells(1, c).Value
Next
End If
UserForm1.ComboBox1.AddItem i
UserForm1.ComboBox2.AddItem i
Next
wkbname = Left(wkb.Name, Len(wkb.Name) - 4)
ThisWorkbook.Sheets("sheet2").Range("G1").Value = wkbname
'MsgBox wkbname
UserForm1.Show
End Sub

Click on command button on userform

Private Sub CommandButton1_Click()
Dim wkb As Workbook
Set wkb = Workbooks(ThisWorkbook.Sheets("sheet2").Range("G1").Value)
Dim sh As Worksheet
Set sh = wkb.ActiveSheet
Dim minvalue As Integer, maxvalue As Integer
minvalue = Me.ComboBox1.Value + 1
maxvalue = Me.ComboBox2.Value + 1
If minvalue > maxvalue Then
MsgBox ("Min row should be less than Max row")
Unload Me
wkb.Close
ThisWorkbook.Sheets("sheet2").Range("G1").Clear
Exit Sub
End If
Dim NSH As Worksheet
Set NSH = ThisWorkbook.Worksheets.Add
rownumb = 2
For r = minvalue To maxvalue
colnumb = 1: Headerrow = 1
For c = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(c) = True Then
colname = Me.ListBox1.List(c)
selectedcolumn = c + 1
If Headerrow = 1 Then
NSH.Cells(Headerrow, colnumb).Value = sh.Cells(Headerrow, selectedcolumn).Value
End If
NSH.Cells(rownumb, colnumb).Value = sh.Cells(r, selectedcolumn).Value
colnumb = colnumb + 1
End If
Next ' column Loop
Headerrow = ""
rownumb = rownumb + 1
Next ' row loop
Unload Me
wkb.Close
ThisWorkbook.Sheets("sheet2").Range("G1").Clear
NSH.Move after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
End Sub

Write the data into text file from excel workbook

Copy data from Note Pad to Workbook

Copy the data from Note pad to Workbook

Private Sub CommandButton1_Click()
Dim OpenNotepad As String
OpenNotepad = "D:\Datafile.txt"
Dim mydata As String
'open file for input
Dim i As Integer
i = 1
Open OpenNotepad For Input As #1
' do until end of file
Do Until EOF(1)
'Through line statement we can move the data into textline variable
Line Input #1, mydata
Range("A" & i).Value = mydata
'paste the data in next line
i = i + 1
'do loop close
Loop
Close #1
Columns(1).AutoFit
End Sub

Write data into Notepad

Private Sub CommandButton1_Click()
Dim np As String
np = "D:\DataCopy"
Dim j As Integer
j = Range(Range("A1"), Range("A1").End(xlDown)).Rows.Count
Dim mydata As Variant
Open np For Output As #1
For i = 1 To j
mydata = Cells(i, 1).Value
Write #1, mydata
Next
Close #1
End Sub

Write data into Notepad - Multicolumn

Private Sub CommandButton1_Click()
Dim np As String
np = "D:\DataCopymulti"
'np = Application.GetOpenFilename()
Dim R As Variant, C As Variant
R = Range(Range("A1"), Range("A1").End(xlDown)).Rows.Count
C = Range(Range("A1"), Range("A1").End(xlToRight)).Columns.Count
Dim mydata As Variant
Open np For Output As #1
For i = 1 To R
For o = 1 To C
mydata = Cells(i, o).Value
If o = C Then
Write #1, mydata
Else
Write #1, mydata,
End If
Next o
Next i
Close #1
End Sub