PDA

View Full Version : Solved: Build file string to import an HTML file



Switchman
03-18-2008, 06:05 PM
I have program that builds a HTML report file that I need to bring into Excel where I can format it, add formulas to sum up the values. I have no control of the program that builds the HTML file. What I have got my script to do is input the HTML file if I hard code the path to the file. Unfortunately the filename will change every time I execute the program.

I can get the file name by selecting and pasting the link in the HTML report. Here is a sample of the link when I copy it:

file:///C:/Documents%20and%20Settings/sd/Local%20Settings/Temp/Bill%20of%20Materials%20Report41004.html (file:///C:/Documents%20and%20Settings/sdulaney/Local%20Settings/Temp/Bill%20of%20Materials%20Report41004.html)

I can use the following msgbox to paste the link into to get the path.
Sub MsgBox_Get_BOM_Path()

BOMFilePAth = InputBox("Using the Network Information Report, paste the path to the BOM file using the Copy Shortcut command:", "Import BOM")

End Sub


:banghead:
What I need help on is building the following command up. The whole path could, and will change so I need to use the full file path. I am not a programmer; I just try and use code to make my job easier when I can on what will be a repetitive task.

With ActiveSheet.QueryTables.Add(Connection:="FINDER;file:///C:/Documents%20and%20Settings/sd/My%20Documents/320%20Configuration%20Tool/Bill%20of%20Materials%20Report26425.html" _
, Destination:=Range("A1"))


Here ist the entire section of code:
Sub Import_BOM_RTF()
'
' Import_BOM_RTF Macro
'
'
'
Sheets("BOM").Select

With ActiveSheet.QueryTables.Add(Connection:="FINDER;file:///C:\Documents and Settings\sd\Local Settings\Temp\Bill of Materials Report40985.html", Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=72
Range("A1").Activate
End Sub

Thanks for any help you can provide.
Switchman

Switchman
03-19-2008, 10:59 AM
After talking this through with a friend of mine, this is the change we came up with that solves the problem. I just wanted to post the fix and close out the request.

The message box is added here for to provide the complete solution. In the original post, it was in a separate subroutine. I don't know why I was having so much trouble originally. If you use a "Copy Shortcut" on a web link and paste it into the message box, it will then import the linked web page into excel.




Sub Import_BOM_RTF()
'
' Import_BOM_RTF Macro
' Macro recorded 3/18/2008 by
'
Dim Variable As String
'
Sheets("BOM").Select
Range("A1").Select
Range("A1").Activate

Variable = InputBox("Input stuff")
Variable = "FINDER;" & Variable
MsgBox Variable
With ActiveSheet.QueryTables.Add(Connection:=Variable, Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingRTF
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=72
Range("A1").Activate


End Sub