×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Loop with an array?
2

Loop with an array?

Loop with an array?

(OP)
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?

RE: Loop with an array?

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"

RE: Loop with an array?

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

RE: Loop with an array?

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

CODE

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 

RE: Loop with an array?

When I run this code:

CODE

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

I get this:

CODE

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?

RE: Loop with an array?

Your objExcel.ActiveWorkbook.Saved = True 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:
Set objExcel = CreateObject("Excel.Application")
For i = 6 to 9
    ' process files
Next
objExcel.Application.Quit
Set objExcel = Nothing 

combo

RE: Loop with an array?

(OP)
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.

RE: Loop with an array?

(OP)
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

RE: Loop with an array?

Still faster in one instance. Completing guitarzan code:

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

RE: Loop with an array?

(OP)
Combo, that eliminates the need for the "Sleep"?

RE: Loop with an array?

I'm afraid no.

combo

RE: Loop with an array?

(OP)
Thanks, it does run a bit quicker.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close