Sleeper:Running Macros from an FTP Server

[Anne Troy]

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]

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.

[Ken Puls]

Anne,

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

[Anne Troy]

He dun bin PM'd.

[johnske]

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

[Anne Troy]

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.

[johnske]

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

[Anne Troy]

Check your PMs.

[johnske]

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

[Anne Troy]

Well, let's try it. I'm uploading right now.

[johnske]

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