PDA

View Full Version : Sleeper:Running Macros from an FTP Server



Anne Troy
06-29-2005, 05:12 PM
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
06-29-2005, 05:13 PM
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
06-29-2005, 05:27 PM
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
06-29-2005, 05:29 PM
He dun bin PM'd. :)

johnske
06-29-2005, 05:53 PM
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
06-29-2005, 06:17 PM
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
06-29-2005, 07:03 PM
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
06-29-2005, 07:09 PM
Check your PMs. :)

johnske
06-29-2005, 10:18 PM
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
06-29-2005, 10:44 PM
Well, let's try it. I'm uploading right now.

johnske
07-01-2005, 06:50 PM
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 :)