Consulting

Results 1 to 11 of 11

Thread: Sleeper:Running Macros from an FTP Server

  1. #1
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location

    Sleeper:Running Macros from an FTP Server

    I never thought it was possible. Is it?

    Specifically, we want the code from this thread (the code posted by KPuls is the "final" we used.

    http://www.vbaexpress.com/forum/showthread.php?t=3819
    ~Anne Troy

  2. #2
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    By the way, these are worksheet events. When we click on a cell, the macro's supposed to run. I can only get a couple of them to run, and only by double-clicking the cell, another of the same type just won't run at all.
    ~Anne Troy

  3. #3
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Anne,

    PM Johnske. I think he's played with Excel on an FTP server. Could be wrong, but I thought he said it worked.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  4. #4
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    He dun bin PM'd.
    ~Anne Troy

  5. #5
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Quote Originally Posted by kpuls
    Anne,

    PM Johnske. I think he's played with Excel on an FTP server. Could be wrong, but I thought he said it worked.
    Hi Anne,

    No, not specifically an STP server. Wot exactly's the problem? You want to download and auto-install the code module in a workbook maybe?

    Regards,
    John
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  6. #6
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Nope. We wanna open the workbook right from the FTP server and run macros from it. Let me know if you wanna see the behavior. I'll upload to my FTP and give you the logins.
    ~Anne Troy

  7. #7
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Quote Originally Posted by Dreamboat
    Nope. We wanna open the workbook right from the FTP server and run macros from it. Let me know if you wanna see the behavior. I'll upload to my FTP and give you the logins.
    Hi Anne,

    Shouldn't be hard, but I'm not too sure about runnung a worksheet event from it.

    Yeh, I'll need logins etc to test.

    John
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  8. #8
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Check your PMs.
    ~Anne Troy

  9. #9
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Hi Anne,

    Very strange, it runs perfectly when downloaded to my desktop, but sometime one or none will work when run from the link. However I got it to work every time by turning ScreenUpdating = False off.

    I can't save the changes to your book on the link cos it's saying read-only, but if you put this mod into it, it should work every time (as I said, it only turns off ScreenUpdating)

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim i               As Long
    Dim LastRow         As Long
        If Intersect(Target, Range("B8,E8:F8")) Is Nothing Then
            Exit Sub
        End If
    'Application.ScreenUpdating = False
        Application.EnableEvents = False
    Range("E:F").NumberFormat = "@"
    LastRow = Sheet7.Range("A65536").End(xlUp).Row
        For i = 2 To LastRow
            Range("E:E").Replace What:=Sheet7.Range("A" & i).Text, _
                Replacement:=Application.WorksheetFunction.Rept("a", i), LookAt:=xlWhole
        Next i
    LastRow = Sheet7.Range("B65536").End(xlUp).Row
        For i = 2 To LastRow
            Range("F:F").Replace What:=Sheet7.Range("B" & i).Text, _
                Replacement:=Application.WorksheetFunction.Rept("a", i), LookAt:=xlWhole
        Next i
    Select Case Target.Column
            Case Is = 2
                Range("sfw").Sort Key1:=Range("B9"), Order1:=xlAscending, _
                    Key2:=Range("E9"), Order2:=xlAscending, _
                    Key3:=Range("F9"), Order3:=xlAscending, _
                    Header:=xlNo, MatchCase:=False, _
                    Orientation:=xlTopToBottom
            Case Is = 5
                Range("sfw").Sort Key1:=Range("E9"), Order1:=xlAscending, _
                    Key2:=Range("B9"), Order2:=xlAscending, _
                    Key3:=Range("F9"), Order3:=xlAscending, _
                    Header:=xlNo, MatchCase:=False, _
                    Orientation:=xlTopToBottom
            Case Is = 6
                Range("sfw").Sort Key1:=Range("F9"), Order1:=xlAscending, _
                    Key2:=Range("B9"), Order2:=xlAscending, _
                    Key3:=Range("E9"), Order3:=xlAscending, _
                    Header:=xlNo, MatchCase:=False, _
                    Orientation:=xlTopToBottom
        End Select
        LastRow = Sheet7.Range("A65536").End(xlUp).Row
        For i = 2 To LastRow
            Range("E:E").Replace What:=Application.WorksheetFunction.Rept("a", i), _
                Replacement:=Sheet7.Range("A" & i).Text, LookAt:=xlWhole
        Next i
    LastRow = Sheet7.Range("B65536").End(xlUp).Row
        For i = 2 To LastRow
            Range("F:F").Replace What:=Application.WorksheetFunction.Rept("a", i), _
                Replacement:=Sheet7.Range("B" & i).Text, LookAt:=xlWhole
        Next i
        'Application.ScreenUpdating = True
        Application.EnableEvents = True
    End Sub
    Let me know,
    John
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  10. #10
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Well, let's try it. I'm uploading right now.
    ~Anne Troy

  11. #11
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Hi Anne,

    The only thing I can think of is that the command Application.ScreenUpdating = True is getting lost somewhere in the aether... Maybe due to the difference in the speed at which the app (XL) is working and the signal download speed.

    I played with this for quite a while and found that sometimes 1, 2, or even all 3 cells weren't triggering the sort, and other times they all were...

    Anyhoo, can we mark this solved for now?

    Regards,
    John
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

Posting Permissions

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