By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Creating a loop to save multiple excel documents...

cori25
P: 83
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
Mar 5 '08 #1
Share this Question
Share on Google+
3 Replies


Denburt
Expert 100+
P: 1,356
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.
Expand|Select|Wrap|Line Numbers
  1. For i = 0 To 4
  2. strSavePath(i) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(180)(" & strMonth & ").xls"
  3. xlApp.ActiveWorkbook.SaveAs strSavePath
  4. Next
  5. DoCmd.SetWarnings True
  6.  
Mar 5 '08 #2

cori25
P: 83
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....
Mar 6 '08 #3

Denburt
Expert 100+
P: 1,356
Yeah what was I thinking. Try the following:

Expand|Select|Wrap|Line Numbers
  1. strSavePath(0) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(180)(" & strMonth & ").xls"
  2. strSavePath(1) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(181)(" & strMonth & ").xls"
  3. strSavePath(2) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(182)(" & strMonth & ").xls"
  4. strSavePath(3) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(183)(" & strMonth & ").xls"
  5. strSavePath(4) = "\\Norwalk2\COMMON1\Field Ops\DailyOurVoice_BackUps\Sat(184)(" & strMonth & ").xls"
  6. For i = 0 To 4
  7. xlApp.ActiveWorkbook.SaveAs strSavePath(i)
  8. Next
  9. xlApp.Quit
  10. DoCmd.SetWarnings False
  11. Set xlApp = Nothing
  12. End Function
Mar 6 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.