£12 Travelodge Sale

[TimberWolf]

So this macro. It's written not directly in VB, but coded using captured keystrokes?

[MarillionFan]

So this macro. It's written not directly in VB, but coded using captured keystrokes?

It's in VB. It uses a Web Query to loop around the dates and returns the data into individual sheets before combining the data into a table & running a pivot. As I said cludgy.

[TimberWolf]

It's in VB. It uses a Web Query to loop around the dates and returns the data into individual sheets before combining the data into a table & running a pivot. As I said cludgy.

Cool, haven't used Web Query before, or Excel for ages for that matter.

Pull data into Microsoft Excel with Web queries | TechRepublic

[TimberWolf]

Where is DimPrawn to tell us that one must spend at least £3000/week on hotels.

[pmeswani]

Where is DimPrawn to tell us that one must spend at least £3000/week on hotels.

His hotel room doesn't have wifi.

[Old Greg]

His hotel room doesn't have wifi.

He's still got the breakfast dishes to wash.

[MarillionFan]

I was going to combine in to one report but cannot be bothered. But basically this runs queries against the Travelodge site. Copy the code into a module in Excel and then run it.

I am fairly certain one of the web developers on here could do a pretty cool bookmarklet to do something much more sophisticated.

Sub GetTravel()
'
' GetTravel Macro
'
    Dim Sheetname As String
    Dim Sheets(100) As String
    Dim icnt As Integer
    Dim Location As String
    Dim SearchDate As Date
    Dim StartDate As Date
    Dim EndDate As Date
    Dim GetStr As String
'
    Location = InputBox("Please type in a Travel Lodge Location")
    StartDate = InputBox("Please enter start date")
    EndDate = InputBox("Please enter end date")
    SearchDate = Now
    icnt = 0
    
    For i = DateValue(StartDate) To DateValue(EndDate) Step 7
            Worksheets.Add
            Sheetname = Format(i, "d") & "-" & Format(i, "mmm") & Format(i, "-yyyy")
            For j = 1 To Worksheets.Count
                If Worksheets(j).Name = Sheetname Then
                    Application.DisplayAlerts = False
                    Worksheets(Sheetname).Delete
                    Application.DisplayAlerts = True
                    Exit For
                End If
            Next j
            ActiveSheet.Name = Sheetname
            icnt = icnt + 1
            Sheets(icnt) = Sheetname
'
        checkindate = Format(i, "d") & "%2F0" & Format(i, "mm") & "%2F" & Format(i, "yyyy")
            GetStr = "URL;http://www.travelodge.co.uk/search_and_book/saver_search.php?action=search&tab=list&source=XX&search_for=PROM3&checkInDate=" & checkindate & "&locpostText=" & Location
'    getstr = "URL;http://www.travelodge.co.uk/search_and_book/saver_search.php?action=search&tab=list&source=XX&search_for=PROM3&checkInDate=" & Str(i) & "%2F0" & Format(j, "00") & "%2F2012&locpostText=somerset"

'
        With ActiveSheet.QueryTables.Add(Connection:= _
        GetStr _
        , Destination:=Range("$A$1"))
        .Name = "saver_search.php?action=search&tab=list&source=XX&search_for=PROM3&checkInDate=" & checkindate & "&locpostText=" & Location
        .FieldNames = True
        .RowNumbers = True
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .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
        Range("a1").Value = i
        Range("a1").NumberFormat = "dd/mmm/yyyy"
        ActiveCell.Offset(0, 2).Value = DateValue(Sheets(icnt)) - 3
        ActiveCell.Offset(0, 3).Value = DateValue(Sheets(icnt)) - 2
        ActiveCell.Offset(0, 4).Value = DateValue(Sheets(icnt)) - 1
        ActiveCell.Offset(0, 5).Value = DateValue(Sheets(icnt)) - 0
        ActiveCell.Offset(0, 6).Value = DateValue(Sheets(icnt)) + 1
        ActiveCell.Offset(0, 7).Value = DateValue(Sheets(icnt)) + 2
        ActiveCell.Offset(0, 8).Value = DateValue(Sheets(icnt)) + 3
    Next
 
End Sub

[AtW]

So this macro. It's written not directly in VB, but coded using captured keystrokes?

Basically yes - it's a key logger that will log your CUK password, online banking access etc...

[TimberWolf]

Basically yes - it's a key logger that will log your CUK password, online banking access etc...

Ah, Russian humour must make dark cold winter nights fly by

[TimberWolf]

I was going to combine in to one report but cannot be bothered. But basically this runs queries against the Travelodge site. Copy the code into a module in Excel and then run it.

I am fairly certain one of the web developers on here could do a pretty cool bookmarklet to do something much more sophisticated.

Sub GetTravel()
'
' GetTravel Macro
'
    Dim Sheetname As String
    Dim Sheets(100) As String
    Dim icnt As Integer
    Dim Location As String
    Dim SearchDate As Date
    Dim StartDate As Date
    Dim EndDate As Date
    Dim GetStr As String
'
    Location = InputBox("Please type in a Travel Lodge Location")
    StartDate = InputBox("Please enter start date")
    EndDate = InputBox("Please enter end date")
    SearchDate = Now
    icnt = 0
    
    For i = DateValue(StartDate) To DateValue(EndDate) Step 7
            Worksheets.Add
            Sheetname = Format(i, "d") & "-" & Format(i, "mmm") & Format(i, "-yyyy")
            For j = 1 To Worksheets.Count
                If Worksheets(j).Name = Sheetname Then
                    Application.DisplayAlerts = False
                    Worksheets(Sheetname).Delete
                    Application.DisplayAlerts = True
                    Exit For
                End If
            Next j
            ActiveSheet.Name = Sheetname
            icnt = icnt + 1
            Sheets(icnt) = Sheetname
'
        checkindate = Format(i, "d") & "%2F0" & Format(i, "mm") & "%2F" & Format(i, "yyyy")
            GetStr = "URL;http://www.travelodge.co.uk/search_and_book/saver_search.php?action=search&tab=list&source=XX&search_for=PROM3&checkInDate=" & checkindate & "&locpostText=" & Location
'    getstr = "URL;http://www.travelodge.co.uk/search_and_book/saver_search.php?action=search&tab=list&source=XX&search_for=PROM3&checkInDate=" & Str(i) & "%2F0" & Format(j, "00") & "%2F2012&locpostText=somerset"

'
        With ActiveSheet.QueryTables.Add(Connection:= _
        GetStr _
        , Destination:=Range("$A$1"))
        .Name = "saver_search.php?action=search&tab=list&source=XX&search_for=PROM3&checkInDate=" & checkindate & "&locpostText=" & Location
        .FieldNames = True
        .RowNumbers = True
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .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
        Range("a1").Value = i
        Range("a1").NumberFormat = "dd/mmm/yyyy"
        ActiveCell.Offset(0, 2).Value = DateValue(Sheets(icnt)) - 3
        ActiveCell.Offset(0, 3).Value = DateValue(Sheets(icnt)) - 2
        ActiveCell.Offset(0, 4).Value = DateValue(Sheets(icnt)) - 1
        ActiveCell.Offset(0, 5).Value = DateValue(Sheets(icnt)) - 0
        ActiveCell.Offset(0, 6).Value = DateValue(Sheets(icnt)) + 1
        ActiveCell.Offset(0, 7).Value = DateValue(Sheets(icnt)) + 2
        ActiveCell.Offset(0, 8).Value = DateValue(Sheets(icnt)) + 3
    Next
 
End Sub

Worked for me. I've now only got 12 trial runs of MS Excel student edition left...