Creating a loop to save multiple excel documents...

[cori25]

Hello...

I am attempting to open up 4 paths and then save each path using a loop.

[PHP]Option Compare Database
Function SaveSat()

Dim strMonth As String

Dim strSavePath(4) As String

Dim strPath(4) As String


strPath(0) = "C:\Database\Daily_Ourvoice\Data\180\Sat.xls"
strPath(1) = "C:\Database\Daily_Ourvoice\Data\181\Sat.xls"
strPath(2) = "C:\Database\Daily_Ourvoice\Data\182\Sat.xls"
strPath(3) = "C:\Database\Daily_Ourvoice\Data\183\Sat.xls"
strPath(4) = "C:\Database\Daily_Ourvoice\Data\184\Sat.xls"

Dim xlApp As New Excel.Application
xlApp.Workbooks.Open filename:="" & strPath & ""
xlApp.Visible = True

DoCmd.SetWarnings False

strMonth = Format(Date, "mm-dd-yyyy")

For i = 0 To 4

strSavePath(0) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(180)(" & strMonth & ").xls"
strSavePath(1) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(181)(" & strMonth & ").xls"
strSavePath(2) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(182)(" & strMonth & ").xls"
strSavePath(3) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(183)(" & strMonth & ").xls"
strSavePath(4) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(184)(" & strMonth & ").xls"

xlApp.ActiveWorkbook.SaveAs strSavePath

Next
xlApp.Quit
Set xlApp = Nothing
End Function[/PHP]

I can not get this to work and need it asap. Any help appreciated..

Thanks

[Denburt]

For i = 0 To 4

strSavePath(0) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(180)(" & strMonth & ").xls"
strSavePath(1) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(181)(" & strMonth & ").xls"
strSavePath(2) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(182)(" & strMonth & ").xls"
strSavePath(3) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(183)(" & strMonth & ").xls"
strSavePath(4) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(184)(" & strMonth & ").xls"

xlApp.ActiveWorkbook.SaveAs strSavePath

Next
Thanks

I see a number of issues with this procedure, one that hit me right off was what I quoted above. try something like the following or just remove the for each statement in the quote above. You should also turn your warnings back on at the end of the procedure and it should work.

For i = 0 To 4
strSavePath(i) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(180)(" & strMonth & ").xls"
xlApp.ActiveWorkbook.SaveAs strSavePath
Next
DoCmd.SetWarnings True
 

[cori25]

This would work if the strSavePath were the same for all documents. If you look closely each savepath is different and I need the strpath to open and then save then go to the next strpath, open and save....

[Denburt]

Yeah what was I thinking. Try the following:

strSavePath(0) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(180)(" & strMonth & ").xls"
strSavePath(1) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(181)(" & strMonth & ").xls"
strSavePath(2) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(182)(" & strMonth & ").xls"
strSavePath(3) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(183)(" & strMonth & ").xls"
strSavePath(4) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(184)(" & strMonth & ").xls"
For i = 0 To 4
xlApp.ActiveWorkbook.SaveAs strSavePath(i)
Next
xlApp.Quit
DoCmd.SetWarnings False
Set xlApp = Nothing
End Function