• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
Collapse

You are not logged in or you do not have permission to access this page. This could be due to one of several reasons:

  • You are not logged in. If you are already registered, fill in the form below to log in, or follow the "Sign Up" link to register a new account.
  • You may not have sufficient privileges to access this page. Are you trying to edit someone else's post, access administrative features or some other privileged system?
  • If you are trying to post, the administrator may have disabled your account, or it may be awaiting activation.

Previously on "£12 Travelodge Sale"

Collapse

  • escapeUK
    replied
    Originally posted by d000hg View Post
    MF in Not Bulltulipting Shocker
    I know! I've never been more impressed by something he posted, not saying much I know, but still a compliment.

    He actually claimed he could do something that most programmers here thought, "I wouldnt waste me time on that, and you probably cant do it anyway." Then he proved not only he could do it, but that he would waste time on it too!

    Leave a comment:


  • escapeUK
    replied
    Double post.

    Leave a comment:


  • Old Greg
    replied
    Originally posted by AtW View Post
    Talking of cheap hotels - what happened to scooterscot?
    I didn't even remember to put it on expenses - Travelodge Bridgwater, Bridgwater Traveller Reviews - TripAdvisor

    Leave a comment:


  • d000hg
    replied
    MF in Not Bulltulipting Shocker

    Leave a comment:


  • VirtualMonkey
    replied
    Looks good. Thanks MF

    Leave a comment:


  • AtW
    replied
    Originally posted by TimberWolf View Post
    Ah, Russian humour must make dark cold winter nights fly by
    MF got hit by Freamon's source code release, it's his turn now

    Leave a comment:


  • TimberWolf
    replied
    Originally posted by MarillionFan View Post
    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.

    Code:
    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...

    Leave a comment:


  • TimberWolf
    replied
    Originally posted by AtW View Post
    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

    Leave a comment:


  • AtW
    replied
    Originally posted by TimberWolf View Post
    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...

    Leave a comment:


  • MarillionFan
    replied
    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.

    Code:
    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
    Last edited by MarillionFan; 16 April 2012, 19:54.

    Leave a comment:


  • Old Greg
    replied
    Originally posted by pmeswani View Post
    His hotel room doesn't have wifi.
    He's still got the breakfast dishes to wash.

    Leave a comment:


  • pmeswani
    replied
    Originally posted by TimberWolf View Post
    Where is DimPrawn to tell us that one must spend at least £3000/week on hotels.
    His hotel room doesn't have wifi.

    Leave a comment:


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

    Leave a comment:


  • TimberWolf
    replied
    Originally posted by MarillionFan View Post
    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

    Leave a comment:


  • MarillionFan
    replied
    Originally posted by TimberWolf View Post
    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.

    Leave a comment:

Working...
X