Posts by Richie(UK)

Richie(UK) · Jul 13th 2012

Re: Excel VBA SUMPRODUCT QUOTATION MARKS

Hi,

I think the problem is probably caused by the mixed references that you are using - part R1C1 and past A1. If you can re-write the initial part of the code to use A1 also then I suspect that the problem will go away.

Richie(UK) · Jul 13th 2012

Re: Execute code before workbook deactivate

Hi,

I've only looked at this quickly but could you change the event code to add in a workbook test, like this?

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Me.Parent.Name = ActiveWorkbook.Name Then
        MsgBox "changed"
    End If
End Sub

Richie(UK) · Jul 12th 2012

Re: Using sendkeys and loop to download files

Hi,

I've deleted the data that looked like it was personal/private (you may wish to consider changing your password, just in case).

As for the code, how about changing the Set line to :

Code

Set inputCollection = ieDoc.forms(0).getElementByID("download")

I think I picked up the wrong value before, so try:

Code

Set inputCollection = ieDoc.forms(0).getElementByID("download")
     
    For Each inputElement In inputCollection
        If inputElement.getAttribute("value") = "Download" Then
            inputElement.Click
            Exit For
        End If
    Next inputElement

Richie(UK) · Jul 11th 2012

Re: loop my range with criteria

Hi,

Is this what you had in mind?

Code

Sub Test()
    Dim MyRange As Range, MyCell As Range


    With Worksheets("Sheet1")
        .Range("F2").AutoFilter Field:=1, Criteria1:="AAA"
        Set MyRange = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)
        For Each MyCell In MyRange
            MsgBox MyCell.Address
        Next MyCell
    End With
    
End Sub

Display More

Richie(UK) · Jul 11th 2012

Re: Using sendkeys and loop to download files

Hi again,

It's weird coming up with code and not being able to test it! Do me a favour? When we are finished post the final routine (less any confidential stuff of course) just for reference in case anybody else has similar issues and uses the Search)

Anyway, how about adding:

Code

Set inputCollection = ieDoc.getElementsByTagName("download")
 
    For Each inputElement In inputCollection
        If inputElement.getAttribute("value") = "no--DrgyX79ihQVaG-0SqtOquypjt4NxHUl8zoS6wrfoZzO8z73vF5DIKlxhD-rO" Then
            inputElement.Click
            Exit For
        End If
    Next inputElement

Richie(UK) · Jul 11th 2012

Re: Using sendkeys and loop to download files

Hi,

Given the success of the last suggested solution, have you tried:

ieDoc.All("Download").Click

Richie(UK) · Jul 10th 2012

Re: VB Code applied to entire column

Is this the sort of thing that you are looking for?

Code

Private Sub Worksheet_Change(ByVal Target As Range)
     
    If Target.Cells.Count > 1 Then Exit Sub
     
    If Target.Column = 4 Then
        With Target
            Select Case .Value
                Case 100, 150, 1500: Range("P" & Target.Row).ClearContents
                Case Else: 'do nothing
            End Select
        End With
    End If
     
End Sub

Display More

Richie(UK) · Jul 10th 2012

Re: VB Code applied to entire column

Whichever username you decide to go with please remember to use code tags when posting code to the forum. I've added them for you this time.

Richie(UK) · Jul 10th 2012

Re: Using Range() Property With Do While Loop

Hi,

You could try buying a book like "Excel 2007 VBA Programmer's Reference". If that isn't an option then just keep writing code, use F1 to use the in-built help, and frequent forums such as this one (you don't have to post solutions if you don't want to but think about how you would tackle a problem and then compare that with what others suggest). Oh, and don't forget to make use of the macro recorder - you will often need to tidy up its efforts but it will point you in the right direction. Keep at it

Richie(UK) · Jul 10th 2012

Re: Using Range() Property With Do While Loop

Hi and welcome.

Is this the sort of thing that you mean?

Code

Sub test()
    Dim lRow As Long
    
    lRow = 123
    MsgBox Range("A1:E" & lRow).Address
    'or
    MsgBox Range(Cells(1, "A"), Cells(lRow, "E")).Address
    
End Sub

Richie(UK) · Jul 10th 2012

Re: Using sendkeys and loop to download files

Also, did you try the approach I posted above (post #9) using HTMLInputElements?

And (desperately clutching at straws!) possibly try replacing
.submit

with
ieDoc.All("Login").Click

Richie(UK) · Jul 10th 2012

Re: Using sendkeys and loop to download files

Hi,

Try replacing
.submit

with
ieDoc.getelementsbyname("subAction")(0).Click

Richie(UK) · Jul 10th 2012

Re: Printing Setup Macro!

Hi Blake,

As suggested, the macro recorder is often a good starting place for generating code in situations like this. You can then look to modify it and might go with something like this:

Code

Sub Toggle_Papersize()


    With ActiveSheet.PageSetup
        If .PaperSize = xlPaperA4 Then
            .PaperSize = xlPaperLetter
        Else
            .PaperSize = xlPaperA4
        End If
    End With


End Sub

Display More

You could add the code to each of the financial models or add it to your Personal.xls to have it available for use on all workbooks (you can assign a keyboard shortcut to it to make its use easier).

Richie(UK) · Jul 9th 2012

Re: vba code / stock historical price macro ?

Hi,

Take a look at using QueryTables. You can probably get pretty much all you need by recording a macro of you doing this manually - that's how I started with the following:

Code

Sub QueryTableGrabber()
'*****************************************************************************
'*** Create a new QueryTable (in a new workbook to avoid naming issues)    ***
'***                                                                       ***
'*** Copy the information from the QT in the new workbook to the           ***
'*** worksheet before closing the new workbook.                            ***
'*****************************************************************************


    Const strMain As String = "URL;http://money.cnn.com/data/dow30"
    Const strQTName = "dow30"
    Dim wbkNew As Workbook
    
    Application.ScreenUpdating = False
    
    Set wbkNew = Workbooks.Add
    ThisWorkbook.Worksheets("Sheet1").Range("A1:F40").ClearContents
       
    Call NewQT(wbkNew.Worksheets(1), strMain, strQTName)
    wbkNew.Worksheets(1).Range(strQTName).Copy
    ThisWorkbook.Worksheets("Sheet1").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    
    With Application
        .DisplayAlerts = False
        wbkNew.Close savechanges:=False
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
End Sub


Sub NewQT(ws As Worksheet, strURL As String, strName As String)
'**********************************************************
'*** Create a new QueryTable in the specified worksheet ***
'*** using the URL and name given                       ***
'**********************************************************
    With ws.QueryTables.Add(Connection:=strURL, Destination:=ws.Range("A1"))
        .Name = strName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "4"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    
End Sub

Display More

Richie(UK) · Jul 9th 2012

Re: vba code / stock historical price macro ?

Hi and welcome to the forum.

As this is a school project I take it that you have been given some guidance on how to proceed. What were you told? What initial ideas do you have (other than ask on a forum to get somebody else to do the project for you! )

Might also be useful if you indicate which exchange these stocks are quoted on.

Richie(UK) · Jul 9th 2012

Re: Using sendkeys and loop to download files

Hi,

Would you:
1. Post the routine that you are currently using (replace your company details with alternatives for illustration but keep the code the same).
2. Confirm that you have set the required references.

Richie(UK) · Jul 8th 2012

Re: Using sendkeys and loop to download files

Hi,

That is good news - should make things much easier for you.

The following should help to get you started. I've used http://www.fool.co.uk/index.aspx as an example site. If you register there (it's free and just for the purposes of illustration). The following code will login, using the details provided, and navigate to a given page.

Code

Sub GoSurf()
'Need references to Microsoft Internet Controls and Microsoft Forms Library
    Dim ieApp As InternetExplorer
    Dim ieDoc As HTMLDocument
    Dim UserNameInputBox As HTMLInputElement
    Dim PasswordInputBox As HTMLInputElement
    Dim SignInButton As HTMLInputElement


    'create a new instance of ie
   Set ieApp = New InternetExplorer
   
    'you don’t need this, but it’s good for debugging
   ieApp.Visible = True
   
    'assume we’re not logged in and just go directly to the login page
   ieApp.navigate "https://www.fool.co.uk/secure/members/login.aspx"
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop
           
    Set ieDoc = ieApp.document
   
    'fill in the login form – View Source from your browser to get the control names
   With ieDoc.forms(0)
        Set UserNameInputBox = .elements("ctl00$MainContent$ctlLogin$txtUsername")
        UserNameInputBox.Value = "YourNameHere"
        Set PasswordInputBox = .elements("ctl00$MainContent$ctlLogin$txtPassword")
        PasswordInputBox.Value = "YourPasswordHeretestweb"
        Set SignInButton = .elements("ctl00_MainContent_btnSubmit")
        SignInButton.Click
    End With
    
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop
   
    'now that we’re in, go to the page we want
   ieApp.navigate "http://www.fool.co.uk/latest-stories/"
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop
   
End Sub

Display More

It's a bit of a mish-mash of exisiting routines that I have just to serve as an illustration. The initial routine came from here : http://www.dailydoseofexcel.co…te-that-requires-a-login/ . Note that you will need to set a couple of References in the VBE.

Richie(UK) · Jul 8th 2012

Re: Using sendkeys and loop to download files

Hi,

It looks as if automation using FireFox is not really an option. There was an attempt to make it usable in the same way as IE by use of a plugin, see here : http://www.adamlock.com/mozilla/ . However, it looks as if that isn't being maintained currently so it may not support the current version of FF.

Are you sure your webpage won't work with IE? It really would be much easier and safer than using SendKeys (which is basically an accident waiting to happen!).

Richie(UK) · Jul 7th 2012

Re: Using sendkeys and loop to download files

Hi and welcome to the forum.

I don't really want to discourage you but I would strongly urge you not to use SendKeys - it is not a reliable method and will likely get you into trouble at some stage. You would be better advised, IMO, to use an Object variable to refer to InternetExplorer and proceed on that basis. You should then find it easier to navigate the webpage in question. Do you have a particular webpage (so that we can see what you are trying to achieve) or is it a confidential/works page?

Richie(UK) · Jul 7th 2012

Re: Extracting data from a directory of XLSM's

Hi Ent,

You could look into Digital Signatures. I can't advise any further than that though because I've never actually used them