Solved

Excel Macro - skip sheet if not present?

Posted on 2013-11-25
12
473 Views
Last Modified: 2013-11-26
Each week I get an Excel workbook containing cigarette pricing for the following week.  I have to save a number of sheets in this workbook (but not all of them) off to CSV files so that they can be imported into our point of sale systems. I used the macro recorder to record the process, and it works great so long as none of the specified sheets are missing.  How can I manually edit this macro to tell Excel to skip any sheets that it cannot find?

Sub CigPackSave()
'
' CigPackSave Macro
' Macro to save cigarette worksheets as individual CSV Files.
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    ChDir "C:\Users\jplemons\Desktop\temp"
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\cigpack.csv" _
        , FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG29 G IN").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG29.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG28 G IL").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG28.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG27 G OH").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG27.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG26 G VA").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG26.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG25 G TN").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG25.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG24 G TN").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG24.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG23 G TN").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG23.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG22 G TN").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG22.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG21 C TN").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG21.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG20 G AL").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG20.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG19 C IN").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG19.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG18 G AL").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG18.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG17 G AL").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG17.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG16 G IL").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG16.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG15 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG15.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG14 G AL").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG14.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG13 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG13.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG12 G KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG12.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG11 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG11.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG10 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG10.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG09 G KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG09.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG08 G KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG08.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG07 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG07.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG06 G KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG06.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG05 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG05.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG04 G KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG04.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG03 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG03.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG02 G KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG02.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    Sheets("CG01 C KY").Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\jplemons\Desktop\temp\CG01.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWindow.ScrollWorkbookTabs Sheets:=17
End Sub
0
Comment
Question by:btkrausen
  • 7
  • 5
12 Comments
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39674892
Fairly simple workaround, at the beginning of the code put this line:

On Error Resume Next

This will basically ignore any error and continue to the next step of the script.

However, downside being it will ignore ALL errors.

Thanks,
Rob H
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39674902
Also, could be simplified considerably by creating a loop rather than repeating for each and every sheet.

Thanks
Rob H
0
 

Author Comment

by:btkrausen
ID: 39674906
Honestly, as simple as this process is, that might not be a bad option.  I'm going to give it a try.
0
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 32

Expert Comment

by:Rob Henson
ID: 39674940
Simplified to

Sub CigPackSave()

DestDir = "C:\Users\jplemons\Desktop\temp\cigpack"

For Each Worksheet In ActiveWorkbook.Worksheets

    ShtName = Worksheet.Name
    Sheets(ShtName).Copy
    NewFile = Left(ShtName, 4) & ".csv"
    ActiveWorkbook.SaveAs Filename:=DestDir & NewFile _
        , FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close

Next Worksheet

End Sub

Open in new window

It looks like you do something different for the first sheet, the one that gets named cigpack.csv

What is the name of this sheet so it can be dealt with as an exception?

Thanks
Rob H
0
 

Author Comment

by:btkrausen
ID: 39674964
So, the macro will run with that line in it, but there's a side effect.  The macro still creates .csv files for all named sheets and those csv files contain data copied, presumably, from one of the other tabs.  I'm not sure which one at this point, but I can probably take some time later today to track it down. Does that make sense?
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39675040
It will be data from the previous correctly selected sheet.

The Error handler is merely skipping the line that Selects the sheet, the next line is still saving the current sheet as a csv with the name specified.

Strictly speaking an error handling routine would normally be set to ignore the Save As step as well.

Have you tried the condensed version?

Thanks
Rob H
0
 

Author Comment

by:btkrausen
ID: 39675114
Rob,

I hadn't refreshed my screen in a while, so I missed the condensed version - sorry.  I tried the condensed version and it worked really well with one minor change - I set the destination directory to be simply: C:\Users\jplemons\Desktop\temp\

The first worksheet in my original macro is named "PROMOTIONS" and then saved as cigpack.csv.

How can I exclude the sheets named "INDEX" and "NOTES"?  Is there any way to make it so that I don't have to click save after every one?

Thanks!
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39675453
Shouldn't be a problem to miss specific sheets and switching off event warning should handle the messages.
0
 

Author Comment

by:btkrausen
ID: 39675829
I was able to switch off the warnings and write the exceptions for the specific tabs that I wanted skipped - see code below.  Can you assist with the renaming of the PROMOTIONS sheet to cigpack.csv?

Sub CigPackSave()

DestDir = "C:\Users\jplemons\Desktop\temp\"

Application.DisplayAlerts = False

For Each Worksheet In ActiveWorkbook.Worksheets
  If Worksheet.Name <> "INDEX" And Worksheet.Name <> "BASE PRICING" And Worksheet.Name <> "DISCOUNTING" And Worksheet.Name <> "PRICING SUMMARY" And Worksheet.Name <> "PRINT PAGE" And Worksheet.Name <> "NOTES" Then
    ShtName = Worksheet.Name
    Sheets(ShtName).Copy
    NewFile = Left(ShtName, 4) & ".csv"
    ActiveWorkbook.SaveAs Filename:=DestDir & NewFile _
        , FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
  End If
Next Worksheet

End Sub

Open in new window

0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39676245
Try this:

Sub CigPackSave()

DestDir = "C:\Users\jplemons\Desktop\temp\"

Application.DisplayAlerts = False

For Each Worksheet In ActiveWorkbook.Worksheets
    ShtName = Worksheet.Name
    Select Case ShtName
    Case "INDEX", "BASE PRICING", "DISCOUNTING", "PRICING SUMMARY", "PRINT PAGE", "NOTES"
    GoSub Ignore

    Case "PROMOTIONS"
    GoSub Promotions
    
    Case Else
    GoSub CreateCSV
    
    End Select

Next Worksheet

Application.DisplayAlerts = True

Exit Sub
Ignore:
    Return

Promotions:
    Sheets(ShtName).Copy
    NewFile = "cigpack.csv"
    ActiveWorkbook.SaveAs Filename:=DestDir & NewFile _
        , FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
    Return
    
CreateCSV:
    Sheets(ShtName).Copy
    NewFile = Left(ShtName, 4) & ".csv"
    ActiveWorkbook.SaveAs Filename:=DestDir & NewFile _
        , FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
    Return
End Sub

'  If Worksheet.Name <> "INDEX" And _
'  Worksheet.Name <> "BASE PRICING" And _
'  Worksheet.Name <> "DISCOUNTING" And _
'  Worksheet.Name <> "PRICING SUMMARY" And _
'  Worksheet.Name <> "PRINT PAGE" And _
'  Worksheet.Name <> "NOTES" Then CreateCSV

Open in new window


This assesses the sheet name and uses Select Case options to determine what to do. I have split the three options into 3 subroutines:

Ignore - This Case is hard coded for specific sheet names, to ignore more just add them on the end comma separated and enclosed in double quotes.

Promotions - Deals with specific Case

CreateCSV - Deals with everything else.

Might have been better to deal with CG sheets as specific cases eg Left(ShtName,2) = "CG", any sheets not covered by above can then be flagged if so required rather than falling into the "everything else" bucket.

I have also added the line to switch back on Display Alerts.

The chunk of commented out text at the bottom of the script was another alternative but using line separators to ease the reading.

I haven't tested any of this as I don't have the file with multiple sheets.

Thanks
Rob H
0
 

Author Closing Comment

by:btkrausen
ID: 39677465
Rob,

That worked perfectly!  Thank you for providing me with some of the logic behind your code - it has really helped me better understand what's going on.  Will make future macros a little easier.

Thanks!
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39677491
Created Test File, attached.

Note change to first two lines of script setting Destination Directory of CSV files. I have commented out your specific directory and set it to save in a subfolder of the Active Directory called CSV. To test in your scenario you will have to create the CSV folder.

This slight modification to script above checks sheet name for specifics to ignore or treat differently and then for naming convention starting with CG.

If any additional sheets are found it will generate an error message at the end listing those sheets otherwise it gives a confirmation message for all sheets processed.

I also switched off Screen Updating so you don't have the flashing between sheets as the files are created.

By switching off DisplayAlerts the csv files will automatically overwrite existing files.

Thanks
Rob H
Create-CSVs.xlsm
0

Featured Post

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question