Open HTML file in new Sheet from File Browser

netwire

New Member
Joined
Sep 30, 2018
Messages
1
Hello,

I am trying to hammer out the VBA needed to open a .html file and copy it's contents into Sheet2 of the currently open workbook. My plan is to have a button on Sheet1 that reads "Click to locate file", this will open a dialog and the path will be set as a server path and filtered by .html files. The user would select the appropriate html file and click open. Once the user clicks open I want the contents of the .html file loaded into Sheet2. After that I'd setup a macro that takes the data on Sheet2 and formats it into a table so that I can setup a PivotTable on Sheet3 to display the data.

The .html file is generated on an IBM Mainframe in a text format and saved as html... unfortunately it's going to be a mess to edit the file so that I can actually scrape data... I don't think it's got proper html tags and it's not coma delimited.

Here is where I'm at in the code... still not sure how to get it to pull the file into Sheet2.

Code:
[FONT=&quot]Sub openFileDialog()[/FONT][FONT=&quot]
[/FONT]
[FONT=&quot]Dim myFileName as Variable[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]myFileName = Application.GetOpenFileName(FileFilter:=“HTML Files,*.htm*”)[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]If myFileName <> False Then[/FONT]
[FONT=&quot]	Workbooks.Open _[/FONT]
[FONT=&quot]FileName:=“\\server\subdir\anothersub\”myFileName[/FONT]
[FONT=&quot]End If[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]End Sub
[/FONT]/CODE]
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

solotrek

New Member
Joined
Mar 15, 2019
Messages
2
Found this to work.

Sub openFileDialog()
Dim myFileName As Variant
Dim OpenWorkbook As Variant

myFileName = Application.GetOpenFilename(FileFilter:="HTML Files,*.htm*")
If myFileName <> False Then
Set OpenWorkbook = Application.Workbooks.Open(myFileName)
End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,073
Messages
5,526,655
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top