VBA to get cells from workbook on network drive very slow

R

rhodesmc

New Member
Joined
Apr 29, 2015
Messages
1
I'm fairly new to VBA in excel and have been going through several beginning books to try and accomplish getting values from closed workbooks on a network drive and populating them into my main Dashboard workbook. The only problem is they are for a running 13 months, so the files will change name from 01-14 to 02-14 and so on. I wrote variables to get the correct month format to find the workbook for that month on the network drive and was able to set the string "z" to the file path. Then using the variables and string inserted the formulas in the corresponding ranges. My question is the speed in which this occurs seems to be slow as my timer is telling my between 8-10 seconds to process the formulas. Is there a way to increase the speed of the macro? I'm sure there are probably some better programming ways to accomplish this so i'm all ears. Currently running Windows 7, Excel 2010

Sub Import_SHP_Data()
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False


'Loops through Home Health SHP Data workbooks on network drive for correct file names
'based on month/year for columns and imports cell values into Dashboard
Dim c1 As String
Dim c2 As String
Dim c3 As String
Dim c4 As String
Dim c5 As String
Dim c6 As String
Dim c7 As String
Dim c8 As String
Dim c9 As String
Dim c10 As String
Dim c11 As String
Dim c12 As String
Dim c13 As String
Dim x As String
Dim d As Integer
Dim e As Integer
Dim found As Boolean
Dim LDate As String
Dim z As String
Dim strFormulasA(1 To 13) As Variant
Dim strFormulasB(1 To 13) As Variant
Dim strFormulasC(1 To 13) As Variant
Dim strFormulasD(1 To 13) As Variant
Dim strFormulasE(1 To 13) As Variant
Dim strFormulasF(1 To 13) As Variant
Dim strFormulasG(1 To 13) As Variant
Dim StartTime, ElapsedTime, TotalTime


StartTime = Timer


c1 = Range("C1")
c2 = Format(DateAdd("m", 1, c1), "MM-YY")
c3 = Format(DateAdd("m", 2, c1), "MM-YY")
c4 = Format(DateAdd("m", 3, c1), "MM-YY")
c5 = Format(DateAdd("m", 4, c1), "MM-YY")
c6 = Format(DateAdd("m", 5, c1), "MM-YY")
c7 = Format(DateAdd("m", 6, c1), "MM-YY")
c8 = Format(DateAdd("m", 7, c1), "MM-YY")
c9 = Format(DateAdd("m", 8, c1), "MM-YY")
c10 = Format(DateAdd("m", 9, c1), "MM-YY")
c11 = Format(DateAdd("m", 10, c1), "MM-YY")
c12 = Format(DateAdd("m", 11, c1), "MM-YY")
c13 = Format(DateAdd("m", 12, c1), "MM-YY")
c1 = Format(c1, "MM-YY")




z = "\\server\filepath\"


With ThisWorkbook.Sheets("HH - Key Indicator Report")
'''LUPA METRICS''
''Case Weight
strFormulasA(1) = "='" & z & "[" & c1 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C19"
strFormulasA(2) = "='" & z & "[" & c2 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C19"
strFormulasA(3) = "='" & z & "[" & c3 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C19"
strFormulasA(4) = "='" & z & "[" & c4 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C19"
strFormulasA(5) = "='" & z & "[" & c5 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C19"
strFormulasA(6) = "='" & z & "[" & c6 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C19"
strFormulasA(7) = "='" & z & "[" & c7 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C19"
strFormulasA(8) = "='" & z & "[" & c8 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C19"
strFormulasA(9) = "='" & z & "[" & c9 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C19"
strFormulasA(10) = "='" & z & "[" & c10 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C19"
strFormulasA(11) = "='" & z & "[" & c11 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C19"
strFormulasA(12) = "='" & z & "[" & c12 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C19"
strFormulasA(13) = "='" & z & "[" & c13 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C19"


''Avg Reimb per completed Episode/LUPA - combined
strFormulasB(1) = "='" & z & "[" & c1 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17"
strFormulasB(2) = "='" & z & "[" & c2 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17"
strFormulasB(3) = "='" & z & "[" & c3 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17"
strFormulasB(4) = "='" & z & "[" & c4 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17"
strFormulasB(5) = "='" & z & "[" & c5 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17"
strFormulasB(6) = "='" & z & "[" & c6 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17"
strFormulasB(7) = "='" & z & "[" & c7 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17"
strFormulasB(8) = "='" & z & "[" & c8 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17"
strFormulasB(9) = "='" & z & "[" & c9 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17"
strFormulasB(10) = "='" & z & "[" & c10 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17"
strFormulasB(11) = "='" & z & "[" & c11 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17"
strFormulasB(12) = "='" & z & "[" & c12 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17"
strFormulasB(13) = "='" & z & "[" & c13 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17"


''Medicare LUPA #
strFormulasC(1) = "='" & z & "[" & c1 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C9"
strFormulasC(2) = "='" & z & "[" & c2 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C9"
strFormulasC(3) = "='" & z & "[" & c3 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C9"
strFormulasC(4) = "='" & z & "[" & c4 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C9"
strFormulasC(5) = "='" & z & "[" & c5 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C9"
strFormulasC(6) = "='" & z & "[" & c6 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C9"
strFormulasC(7) = "='" & z & "[" & c7 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C9"
strFormulasC(8) = "='" & z & "[" & c8 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C9"
strFormulasC(9) = "='" & z & "[" & c9 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C9"
strFormulasC(10) = "='" & z & "[" & c10 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C9"
strFormulasC(11) = "='" & z & "[" & c11 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C9"
strFormulasC(12) = "='" & z & "[" & c12 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C9"
strFormulasC(13) = "='" & z & "[" & c13 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C9"


''Medicare LUPA %
strFormulasD(1) = "='" & z & "[" & c1 & " Financial Executive Advantage.xls]RAC Metrics'!R10C5"
strFormulasD(2) = "='" & z & "[" & c2 & " Financial Executive Advantage.xls]RAC Metrics'!R10C5"
strFormulasD(3) = "='" & z & "[" & c3 & " Financial Executive Advantage.xls]RAC Metrics'!R10C5"
strFormulasD(4) = "='" & z & "[" & c4 & " Financial Executive Advantage.xls]RAC Metrics'!R10C5"
strFormulasD(5) = "='" & z & "[" & c5 & " Financial Executive Advantage.xls]RAC Metrics'!R10C5"
strFormulasD(6) = "='" & z & "[" & c6 & " Financial Executive Advantage.xls]RAC Metrics'!R10C5"
strFormulasD(7) = "='" & z & "[" & c7 & " Financial Executive Advantage.xls]RAC Metrics'!R10C5"
strFormulasD(8) = "='" & z & "[" & c8 & " Financial Executive Advantage.xls]RAC Metrics'!R10C5"
strFormulasD(9) = "='" & z & "[" & c9 & " Financial Executive Advantage.xls]RAC Metrics'!R10C5"
strFormulasD(10) = "='" & z & "[" & c10 & " Financial Executive Advantage.xls]RAC Metrics'!R10C5"
strFormulasD(11) = "='" & z & "[" & c11 & " Financial Executive Advantage.xls]RAC Metrics'!R10C5"
strFormulasD(12) = "='" & z & "[" & c12 & " Financial Executive Advantage.xls]RAC Metrics'!R10C5"
strFormulasD(13) = "='" & z & "[" & c13 & " Financial Executive Advantage.xls]RAC Metrics'!R10C5"


''Avg effective Reimb per visit - Medicare & Other
strFormulasE(1) = "='" & z & "[" & c1 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17" _
& "/" & "'[" & c1 & " Financial Executive Advantage.xls]Visits by Discipline'!R9C25"
strFormulasE(2) = "='" & z & "[" & c2 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17" _
& "/" & "'[" & c2 & " Financial Executive Advantage.xls]Visits by Discipline'!R9C25"
strFormulasE(3) = "='" & z & "[" & c3 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17" _
& "/" & "'[" & c3 & " Financial Executive Advantage.xls]Visits by Discipline'!R9C25"
strFormulasE(4) = "='" & z & "[" & c4 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17" _
& "/" & "'[" & c4 & " Financial Executive Advantage.xls]Visits by Discipline'!R9C25"
strFormulasE(5) = "='" & z & "[" & c5 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17" _
& "/" & "'[" & c5 & " Financial Executive Advantage.xls]Visits by Discipline'!R9C25"
strFormulasE(6) = "='" & z & "[" & c6 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17" _
& "/" & "'[" & c6 & " Financial Executive Advantage.xls]Visits by Discipline'!R9C25"
strFormulasE(7) = "='" & z & "[" & c7 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17" _
& "/" & "'[" & c7 & " Financial Executive Advantage.xls]Visits by Discipline'!R9C25"
strFormulasE(8) = "='" & z & "[" & c8 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17" _
& "/" & "'[" & c8 & " Financial Executive Advantage.xls]Visits by Discipline'!R9C25"
strFormulasE(9) = "='" & z & "[" & c9 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17" _
& "/" & "'[" & c9 & " Financial Executive Advantage.xls]Visits by Discipline'!R9C25"
strFormulasE(10) = "='" & z & "[" & c10 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17" _
& "/" & "'[" & c10 & " Financial Executive Advantage.xls]Visits by Discipline'!R9C25"
strFormulasE(11) = "='" & z & "[" & c11 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17" _
& "/" & "'[" & c11 & " Financial Executive Advantage.xls]Visits by Discipline'!R9C25"
strFormulasE(12) = "='" & z & "[" & c12 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17" _
& "/" & "'[" & c12 & " Financial Executive Advantage.xls]Visits by Discipline'!R9C25"
strFormulasE(13) = "='" & z & "[" & c13 & " Financial Executive Advantage.xls]LUPA Metrics'!R9C17" _
& "/" & "'[" & c13 & " Financial Executive Advantage.xls]Visits by Discipline'!R9C25"




''EPISODE METRICS''
''Medicare Recerts #
strFormulasF(1) = "='" & z & "[" & c1 & " Financial Executive Advantage.xls]Episodes Started'!R9C6" _
& "+" & "'[" & c1 & " Financial Executive Advantage.xls]Episodes Started'!R9C10"
strFormulasF(2) = "='" & z & "[" & c2 & " Financial Executive Advantage.xls]Episodes Started'!R9C6" _
& "+" & "'[" & c2 & " Financial Executive Advantage.xls]Episodes Started'!R9C10"
strFormulasF(3) = "='" & z & "[" & c3 & " Financial Executive Advantage.xls]Episodes Started'!R9C6" _
& "+" & "'[" & c3 & " Financial Executive Advantage.xls]Episodes Started'!R9C10"
strFormulasF(4) = "='" & z & "[" & c4 & " Financial Executive Advantage.xls]Episodes Started'!R9C6" _
& "+" & "'[" & c4 & " Financial Executive Advantage.xls]Episodes Started'!R9C10"
strFormulasF(5) = "='" & z & "[" & c5 & " Financial Executive Advantage.xls]Episodes Started'!R9C6" _
& "+" & "'[" & c5 & " Financial Executive Advantage.xls]Episodes Started'!R9C10"
strFormulasF(6) = "='" & z & "[" & c6 & " Financial Executive Advantage.xls]Episodes Started'!R9C6" _
& "+" & "'[" & c6 & " Financial Executive Advantage.xls]Episodes Started'!R9C10"
strFormulasF(7) = "='" & z & "[" & c7 & " Financial Executive Advantage.xls]Episodes Started'!R9C6" _
& "+" & "'[" & c7 & " Financial Executive Advantage.xls]Episodes Started'!R9C10"
strFormulasF(8) = "='" & z & "[" & c8 & " Financial Executive Advantage.xls]Episodes Started'!R9C6" _
& "+" & "'[" & c8 & " Financial Executive Advantage.xls]Episodes Started'!R9C10"
strFormulasF(9) = "='" & z & "[" & c9 & " Financial Executive Advantage.xls]Episodes Started'!R9C6" _
& "+" & "'[" & c9 & " Financial Executive Advantage.xls]Episodes Started'!R9C10"
strFormulasF(10) = "='" & z & "[" & c10 & " Financial Executive Advantage.xls]Episodes Started'!R9C6" _
& "+" & "'[" & c10 & " Financial Executive Advantage.xls]Episodes Started'!R9C10"
strFormulasF(11) = "='" & z & "[" & c11 & " Financial Executive Advantage.xls]Episodes Started'!R9C6" _
& "+" & "'[" & c11 & " Financial Executive Advantage.xls]Episodes Started'!R9C10"
strFormulasF(12) = "='" & z & "[" & c12 & " Financial Executive Advantage.xls]Episodes Started'!R9C6" _
& "+" & "'[" & c12 & " Financial Executive Advantage.xls]Episodes Started'!R9C10"
strFormulasF(13) = "='" & z & "[" & c13 & " Financial Executive Advantage.xls]Episodes Started'!R9C6" _
& "+" & "'[" & c13 & " Financial Executive Advantage.xls]Episodes Started'!R9C10"


''Medicare Recerts %
strFormulasG(1) = "=R[-1]C/('" & z & "[" & c1 & " Financial Executive Advantage.xls]Episodes Started'!R9C7" _
& "+" & "'[" & c1 & " Financial Executive Advantage.xls]Episodes Started'!R9C11)"
strFormulasG(2) = "=R[-1]C/('" & z & "[" & c2 & " Financial Executive Advantage.xls]Episodes Started'!R9C7" _
& "+" & "'[" & c2 & " Financial Executive Advantage.xls]Episodes Started'!R9C11)"
strFormulasG(3) = "=R[-1]C/('" & z & "[" & c3 & " Financial Executive Advantage.xls]Episodes Started'!R9C7" _
& "+" & "'[" & c3 & " Financial Executive Advantage.xls]Episodes Started'!R9C11)"
strFormulasG(4) = "=R[-1]C/('" & z & "[" & c4 & " Financial Executive Advantage.xls]Episodes Started'!R9C7" _
& "+" & "'[" & c4 & " Financial Executive Advantage.xls]Episodes Started'!R9C11)"
strFormulasG(5) = "=R[-1]C/('" & z & "[" & c5 & " Financial Executive Advantage.xls]Episodes Started'!R9C7" _
& "+" & "'[" & c5 & " Financial Executive Advantage.xls]Episodes Started'!R9C11)"
strFormulasG(6) = "=R[-1]C/('" & z & "[" & c6 & " Financial Executive Advantage.xls]Episodes Started'!R9C7" _
& "+" & "'[" & c6 & " Financial Executive Advantage.xls]Episodes Started'!R9C11)"
strFormulasG(7) = "=R[-1]C/('" & z & "[" & c7 & " Financial Executive Advantage.xls]Episodes Started'!R9C7" _
& "+" & "'[" & c7 & " Financial Executive Advantage.xls]Episodes Started'!R9C11)"
strFormulasG(8) = "=R[-1]C/('" & z & "[" & c8 & " Financial Executive Advantage.xls]Episodes Started'!R9C7" _
& "+" & "'[" & c8 & " Financial Executive Advantage.xls]Episodes Started'!R9C11)"
strFormulasG(9) = "=R[-1]C/('" & z & "[" & c9 & " Financial Executive Advantage.xls]Episodes Started'!R9C7" _
& "+" & "'[" & c9 & " Financial Executive Advantage.xls]Episodes Started'!R9C11)"
strFormulasG(10) = "=R[-1]C/('" & z & "[" & c10 & " Financial Executive Advantage.xls]Episodes Started'!R9C7" _
& "+" & "'[" & c10 & " Financial Executive Advantage.xls]Episodes Started'!R9C11)"
strFormulasG(11) = "=R[-1]C/('" & z & "[" & c11 & " Financial Executive Advantage.xls]Episodes Started'!R9C7" _
& "+" & "'[" & c11 & " Financial Executive Advantage.xls]Episodes Started'!R9C11)"
strFormulasG(12) = "=R[-1]C/('" & z & "[" & c12 & " Financial Executive Advantage.xls]Episodes Started'!R9C7" _
& "+" & "'[" & c12 & " Financial Executive Advantage.xls]Episodes Started'!R9C11)"
strFormulasG(13) = "=R[-1]C/('" & z & "[" & c13 & " Financial Executive Advantage.xls]Episodes Started'!R9C7" _
& "+" & "'[" & c13 & " Financial Executive Advantage.xls]Episodes Started'!R9C11)"


.Range("C3:O3").Formula = strFormulasA
.Range("C6:O6").Formula = strFormulasB
.Range("C7:O7").Formula = strFormulasC
.Range("C8:O8").Formula = strFormulasD
.Range("C9:O9").Formula = strFormulasE
.Range("C11:O11").Formula = strFormulasF
.Range("C12:O12").Formula = strFormulasG

End With


ElapsedTime = Timer


TotalTime = ElapsedTime - StartTime


MsgBox "calc time is " & TotalTime & " seconds"


Exit_ErrHandler:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Exit Sub


ErrHandler:
MsgBox Error$
Resume Exit_ErrHandler


End Sub
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
You must log in or register to reply here.

Forum statistics

Threads
1,214,584
Messages
6,120,385
Members
448,956
Latest member
JPav

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top