Loop with an array? 2

[TampaRocks]

Don't know if that's the right way to put it, but hopefully I can describe it better. I have a directory with 42 spreadsheets in it. I have a script that will open a spreadsheet (in xlsm format), save it out as an xlsx file, then save/close. Here's what I have...

For i = 6 to 9
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open "C:\Temp\0508_00" & i & ".xlsm"
objExcel.ActiveWorkbook.Saved = True
objExcel.ActiveWindow.Close
objExcel.Application.Quit
Set objExcel = Nothing
Next

Here's my problem. I don't need to do all 42. Also, the filename of the spreadsheets have to be padded with 0's.

Here's what I think I need. I'm thinking that I can start my loop with 1 and run through to 42, but verify each number against a series of numbers to skip. So if "i" equals a number that is in a set, then "i" skips to the next number, if not, it runs through the rest of the program. It will keep going until it finishes the 42nd spreadsheet.

Also, as I mentioned, the filenames are padded with zero's. So how do I change the file name to go from 0508_00#.xlsm to 0508_0##,xlsm when I get to "i"=10?

 

[guitarzan]

Regarding the second question, something like this should pad i with an extra zero if its 1 digit:
objExcel.Workbooks.Open "C:\Temp\0508_0" & Right("0" & i, 2) & ".xlsm"

Or use this if it can go 3 digits:
objExcel.Workbooks.Open "C:\Temp\0508_" & Right("00" & i, 3) & ".xlsm

 

[TampaRocks]

Thanks for the reply guitarzan. I added that in there, but nothing happened.

 

[guitarzan]

For the first part, many ways to do that. Case statement is one way.

For i = 1 to 42
   If Not SkipSheet(i) Then
      ...      
      ...
   End If
Next

Function SkipSheet(i)
   Select Case i
      Case 2,3,6,15,21,30
         SkipSheet = True
      Case Else
         SkipSheet = False
   End Select
End Function

 

[guitarzan]

When I run this code:

For i = 1 to 12
   wscript.echo "C:\Temp\0508_0" & Right("0" & i, 2) & ".xlsm"
Next

I get this:

C:\Temp\0508_001.xlsm
C:\Temp\0508_002.xlsm
C:\Temp\0508_003.xlsm
C:\Temp\0508_004.xlsm
C:\Temp\0508_005.xlsm
C:\Temp\0508_006.xlsm
C:\Temp\0508_007.xlsm
C:\Temp\0508_008.xlsm
C:\Temp\0508_009.xlsm
C:\Temp\0508_010.xlsm
C:\Temp\0508_011.xlsm
C:\Temp\0508_012.xlsm

So, are those the right file names?

 

[combo]

Your [tt]objExcel.ActiveWorkbook.Saved = True[/tt] does nothing to your file, it only tells excel to treat it as not dirty. To convert it to xlsx file use excel's SaveAs method with proper format argument. See excel help for complete syntax, and adapt it to VBS (values instead enumerated constants, argument order in function).

You could work with one excel instance to speed up code:
[pre]Set objExcel = CreateObject("Excel.Application")
For i = 6 to 9
' process files
Next
objExcel.Application.Quit
Set objExcel = Nothing[/pre]

combo

 

[TampaRocks]

Thanks again guitarzan. It's possible that the rest of the code doesn't work right which is causing the problem, since yes, those filenames are correct. I'll give you CASE scenario a try and see what happens.

Combo, I have a macro inside the xlsm file that when Excel opens (and other stuff gets done first), it exports one of the tabs out as an xlsx file. I don't need this script to do that... I just need this to basically batch run through the 42 files programmatically.

 

[TampaRocks]

Thanks for the help. Here's the final product...
For i = 1 to 42
If Not SkipSheet(i) Then
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open "C:\Temp\0508_0" & Right("0" & i, 2) & ".xlsm"
WScript.Sleep 3000
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Saved = True
objExcel.ActiveWorkbook.Close False
objExcel.Application.Quit
Set objExcel = Nothing
End If
Next

Function SkipSheet(i)
Select Case i
Case 1,2,3,4,5,21,22,30,31,32,34,42
SkipSheet = True
Case Else
SkipSheet = False
End Select
End Function

 

[combo]

Still faster in one instance. Completing guitarzan code:

Set objExcel = CreateObject("Excel.Application")
For i = 1 to 42
   If Not SkipSheet(i) Then
      Set objWbk = objExcel.Workbooks.Open "C:\Temp\0508_0" & Right("0" & i, 2) & ".xlsm"      
      objWbk.Saved = True
      objWbk.Close
   End If
Next
objExcel.Quit
Set objWbk = Nothing
Set objExcel = Nothing

combo

 

[TampaRocks]

Combo, that eliminates the need for the "Sleep"?

 

[combo]

I'm afraid no.

combo

 

[TampaRocks]

Thanks, it does run a bit quicker.