update data from website txt file
 

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

update data from website txt file
 

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





"mikeolson" wrote:

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

update data from website txt file
 

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

"mikeolson" wrote in message
...
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