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]
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top