Build file string to import an HTML file

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

I can use the following msgbox to paste the link into to get the path.

Code:

---

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.

Code:

---

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:

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

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.

Code:

---

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

---