Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts

    Macro to update internet links

    Hi again,

    I have 8 workbooks which connect to 8 internet stock addresses and import stock data.

    If I use Data-Connection Properties-Refresh every 2 Minutes on all of them sometimes I get a Script error and nothing refreshes anymore.

    Is it possible to have a macro which will have a refresh routine so that I do not need Excel's Refresh function?

    Best regards,

    Wolfgang

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Wolfgang,

    Just threw this together...untested but should work.

    Place this code int the Sub Workbook_Open() event routine
    Code:
    Sub Workbook_Open()
    repeat = Now + TimeValue("00:02:00")
    Application.OnTime repeat, "ConnectionRefresh"
    End sub

    Place this routine in a module
    Code:
    Public Sub ConnectionRefresh()
    ActiveWorkbook.Connections("Connection").Refresh
    repeat = Now + TimeValue("00:02:00")
    Application.OnTime repeat, "ConnectionRefresh"
    End Sub

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    wolfgang (2013-03-04)

  4. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi Maude,

    runs like hell...

    As always, many "Thank Yous" and have a great time.

    Best regards,

    Wolfgang

  5. #4
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi Maude, just one more thing...

    The internet link below takes you to one of my stock exchange sources Lang & Schwarz.

    Lang & Schwarz provide Real-Time data which I don't use but all others down to Nasdaq.

    What I like to have is on my worksheet to get rid of the row that contains Lang & Schwarz - it is NOT always on top but depends on the time some other place delivers data.

    That is to say, it may appear anywhere between row 1 to row 10.

    Can you help me just one more time, please?

    Best regards,
    Wolfgang

    http://www.boersennews.de/markt/akti...ation/17851618
    Last edited by wolfgang; 2013-03-05 at 07:12. Reason: Forgot the link...

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Wokfgang,

    This worked when I tested it. There are slight modifications that must be made.

    1. Change the previous code for the standard module to the following:
    Code:
    Dim keyword As Range
    
    Public Sub ConnectionRefresh()
    ActiveWorkbook.Connections("Connection").Refresh
    Repeat = Now + TimeValue("00:02:00")
    Application.OnTime Repeat, "ConnectionRefresh"
    Set keyword = Cells.Find(What:="Lang & Schwarz")
    keyword.Rows.Delete
    End Sub
    2. Change the code in the Workbook module to the following:
    Code:
    Sub Workbook_Open()
    Dim keyword As Range
    Repeat = Now + TimeValue("00:02:00")
    Application.OnTime Repeat, "ConnectionRefresh"
    Set keyword = Cells.Find(What:="Lang & Schwarz")
    keyword.Rows.Delete
    End Sub
    3. In the Data menu, click Connections> Properties> Make sure that "Enable background refresh" is unchecked
    Connection.jpg

    HTH,
    Maud

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    wolfgang (2013-03-07)

  8. #6
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Go Morning Maud,

    thanks a lot for your time and work.

    I implemented all of it and after I loaded the workbook it comes up with the following error:

    RUN-time error '91'
    Object variable or With block variable not set

    In the workbook open code the last line is highlighted yellow: keyword.ROWS.DELETE

    Any ideas?
    Best regards,
    Wolfgang

  9. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Wolfgang

    The error will occur whenever "Lang & Schwarz" can't be found.
    So add an error trap to deal with this:

    On Error Resume Next
    Set keyword = Cells.Find(What:="Lang & Schwarz")
    keyword.Rows.Delete
    On Error GoTo 0

    zeddy

  10. #8
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi zeddy,

    thank you for answering me.

    The error already occurs in the workbook module!

    Best regards,
    Wolfgang

  11. #9
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    zeddy,

    I separated the Web-Import part from the rest of my workbook which contains quite a lot of evaluation routines and attach it. Then a different error message is generated.

    Best regards,
    Wolfgang
    Attached Files Attached Files

  12. #10
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    DEAR ALL,

    there is nothing wrong with your codes...

    It is the way how Excel imports the Lang & Schwarz data into the spreadsheet - some cells are Merged or contain Wrapped text...

    This confuses the way how Rows are selected and processed.

    I will look into that matter and let you know ho it goes.

    Best regards,
    Wolfgang

  13. #11
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi Maud,

    after many "Trial-and-Errors" I give up on that import tune-up it will work with many work arounds only. But the site is very informative so I came up with the idea to implement AutoFilter.

    The only problem left is how to copy the visible cells to the other sheet in such a way that they appear as a "real" copy of the MASTER-sheet.

    By that I mean the result should read A1 to A9 and not A4 to A11 like on the MASTER.

    If you load the workbook just click "Continue", please.

    Best regards,

    Wolfgang
    Attached Files Attached Files

  14. #12
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi Maud,

    I figured it out and use Excel 2007 build-in "Select-Visible-Cells" function and that works just fine.

    This concludes my question and I am very grateful for your time and patience!

    All the best,

    Wolfgang

  15. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Wolfgang

    I modified the file you submitted.
    See attached.
    It seems to work for me.
    Please let me know what you think.

    zeddy
    Attached Files Attached Files

  16. #14
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi zeddy,

    looks great to me!

    Now I need to integrate your work into my rather big workbook - I will let you know how it went.

    Best regards,

    Wolfgang

  17. #15
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi again,

    would you please pre-format the page prior to the first refresh cycle in such way:
    CALIBRI 8 - no WRAP TEXT and no MERGE CELLS

    Because my evaluation routines get totally confused by wrapped and merged cells.

    So far I just did this manually...

    Thank you

    Best regards,

    Wolfgang

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •