update data from website txt file

[Guest]

I have it set to update properly if I manually enter the website. What I
want is for the website file to be read from within my workbook.

'
Sub CheckUpdate()
On Error GoTo ErrorMessage
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.mywebsite.com/client name.txt",
Destination:=Range("A1"))
.Name = "update"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlPasteDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Exit Sub
ErrorMessage:
MsgBox "The update failed"
End Sub
'
I want the website to be put together like this:
"URL;http://www.mywebsite.com/
Sheets("Sheet1").range("H6") 'gets name of client
..txt",
when the 3 components are brought together it would result in:
"URL;http://www.mywebsite.com/client name.txt"
The website will always be the same and it will always be a txt file, I need
each client to access their own txt file based on their name in H6

Thank you for your help

 

[Guest]

I got it.

Heres what I changed:
'
Sub CheckUpdate()
Dim strCompleteURL As String
Const strDefaultURL As String = "URL;http://www.website.com/"

cboPartTwo = Sheets("Sheet1").Range("H6")
cboPartThree = ".txt"
strCompleteURL = strDefaultURL & cboPartTwo & cboPartThree

On Error GoTo ErrorMessage
With ActiveSheet.QueryTables.Add(Connection:= _
strCompleteURL, Destination:=Range("A1"))
.Name = "update"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlPasteDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Exit Sub
ErrorMessage:
MsgBox "The update failed"
End Sub
'
Thanks to other posts I was able to put this together.

Mike

 

[NickHK]

Mike,
Presumably you do not need to keep adding QueryTables. You can just update
the existing QT.

Private Sub CommandButton2_Click()
Dim QT As QueryTable

Set QT = ActiveSheet.QueryTables("update")

With QT
.Connection = "URL;http://www.mywebsite.com/" &
Sheets("Sheet1").Range("H6").Value & ".txt"
.Refresh False
End With

End Sub

NickHK