VBA speed

[EliOklesh]

Hi all.

At work I have created a VBA process in Excel that opens an MS Access database program, which in turn interfaces with our workforce management software via an ODBC connection. After harvesting the necessary data it is exported back to Excel and formatted into a report.

My issue is that when working from home over a remote (wireless) connection the workforce management software runs very slowly and my macros start sending me the lovely "Excel is waiting for another program to run an OLE action" error. Conveniently, it continues to do this about every ten seconds or so.

My questions are:
Is there a way to turn these errors off entirely?
Given the description of my set up above, does anyone have any thoughts on how to speed up this VBA process?

Thanks in advance!
eo

 

[RSXchin]

Application.DisplayAlerts = False

maybe? not too sure. Would you mind posting the rest of the code so we might be able to speed it up?

 

[EliOklesh]

Yes, sorry. I meant to earlier and forgot. Note, the I: drive is a shared drive that runs slowly when connecting remotely through VPN. Any other suggestions you may have would be great too, I'm relatively new at this.

Here's the code:

Sub m_auto()
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Admin").Select
Range("A1").Select
Sheets("data").Visible = True
Sheets("pleasewait").Visible = True
Dim switch As String
Dim oApp As Object
Dim LPath As String

LPath = "I:\pathomittedforbusinesssecurity\Service Level Calculator.mdb"
Set oApp = CreateObject("Access.Application")

oApp.Visible = False

oApp.OpenCurrentDatabase LPath

oApp.DoCmd.RunMacro "m_export1"
oApp.DoCmd.RunMacro "m_export2"

oApp.Quit

switch = "Real Time Stats v4.xls"

'********************************************
'***** Paste the Data ***********************
'********************************************
Workbooks.Open Filename:="I:\pathomittedforbusinesssecurity\export1.xls"
Columns("A:P").Select
Selection.Copy
Windows(switch).Activate
Sheets("data").Activate
Range("A1").PasteSpecial
Windows("export1.xls").Activate
Range("A1").Copy
ActiveWorkbook.Close

Workbooks.Open Filename:="I:\pathomittedforbusinesssecurity\export2.xls"
Columns("A:Z").Select
Selection.Copy
Windows(switch).Activate
Sheets("data").Activate
Range("T1").PasteSpecial
Sheets("data").Visible = False
Windows("export2.xls").Activate
Range("A1").Copy
ActiveWorkbook.Close
Dim Tday As String

Tday = Format(Date, "dddd")

Select Case WeekDay(Now())
Case 1, 7 'Sunday
Sheets("Weekend Stats").Activate
Range("b2:m19").Copy
MsgBox "The Real-Time Report is done. Have a great " & Tday & "."
Case Else
Sheets("Daily Stats").Activate
Range("b2:m22").Copy
MsgBox "The Real-Time Report is done. Have a great " & Tday & "."
End Select
Sheets("pleasewait").Visible = False

End Sub

 

[RSXchin]

I've noticed that in the end of your code, you copy range("a1") and do nothing with it, and then copy range("b2:m19") and do nothing with that either. is it supposed to do anything?

 

[EliOklesh]

Yes. The copy A1 looks useless but for some reason the code would not work without it. The copying of the bigger range later (in the if/then portion) will eventually copy and paste into an Outlook email, but that part of this project will be developed later when the rest of it works.

This code works GREAT in the office, 60 seconds and it's done. It's working from home where things just won't happen.

 

[Ralajer]

To get an idea what taking so long you can place some debug.print statement that output the Start time of the various operations to the immediate window. That way you know that DB open for example is taking the longest.

Debug.Print "Open DB: " & Format(Now, "HH:MM:SS")
oApp.OpenCurrentDatabase LPath

Debug.Print "Run DB Macro 1: " & Format(Now, "HH:MM:SS")
oApp.DoCmd.RunMacro "m_export1"

There may be nothing that can be done in VBA done in to improve speed as it may just be matter of bandwidth and VPN performance. Of course I haven't worked with files over a VPN so I am kinda guessing there.

If time is big enough issue you might look into a remote desktop solution so all the processing is done on your work computer but controllable by you when you're at home.

 

[energman58]

It sounds like one or more of the connections between you on the VPN and the computers with data is too slow to do the OLE before timing out - nothing in your VBA code running on your local machine will do anything toimprove this. Instead you can use Remote Desktop to run the files on your desktop from home. Be careful though as some operating systems dont support this.

This article explains how to do it:

http://windows.microsoft.com/en-US/...ther-computer-using-Remote-Desktop-Connection

there are variations for W7 but the processis basically similar and will work provided that both machines are connected to the internet (and you set up the permissions correctly).

Depending on your server software you can also run remote terminal sessions through your VPN too which are similar to the above