Solved

Worksheet parsing to a specific folder

Posted on 2012-03-16
9
279 Views
Last Modified: 2012-03-21
I'm trying to write an Excel sub to copy all the sheets  in a workbook and save them to a specific folder and name them after their tab names. The problem I seem to be having is with saving to a specific folder. The code I have now errors on  Set nm = ActiveSheet.Name.
Sub ParseSheets()

    Dim ws     As Worksheet
    Dim nm     As Name
    Set nm = ActiveSheet.Name
   
    For Each ws In ActiveWorkbook.Worksheets
        ws.Copy
        'ActiveWorkbook.SaveAs (ActiveSheet.Name)
        ActiveWorkbook.SaveAs Filename:= _
        "\\domain-01.com\dfs$\care-one\users\care-one_nanthony\desktop\Storage\& nm" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
       
       
        ActiveWorkbook.Close SaveChanges:=True
    Next ws
End Sub
0
Comment
Question by:janthonyn
  • 5
  • 2
  • 2
9 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37731489
That's because you declared nm as name.  It should be declared as String type

Also, you have nm in quotations on the save, and it should not be.

PS - I recommend using MOVE rather than copy.   That's because if you have any cross-sheet links, you'll have problems with their linkages (they will link back to the workbook you're copying from and if that's what you want, then its fine).

See article on the subject:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_9730-Workbook-link-problems-after-copying-tabs-to-a-new-workbook.html

Please vote YES if you think its helpful.

My suggested coding, which I just tested successfully:
Sub ParseSheets()

    Dim wkb As Workbook
    Dim ws     As Worksheet
    Dim nm     As String
    
    
    nm = ActiveSheet.Name
    
    Set wkb = ActiveWorkbook
    Application.DisplayAlerts = False
    
    For Each ws In wkb.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs Filename:= _
        "\\'domain-01.com\dfs$\care-one\users\care-one_nanthony\desktop\Storage\& nm" _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
        
        ActiveWorkbook.Close SaveChanges:=True
    Next ws
End Sub

Open in new window

Dave
0
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 20 total points
ID: 37731609
Dave, should line 16 be

        "\\'domain-01.com\dfs$\care-one\users\care-one_nanthony\desktop\Storage\" & nm _
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37731611
Yes I noted that but my change didn't seem to take

Sub ParseSheets()

    Dim wkb As Workbook
    Dim ws     As Worksheet
    Dim nm     As String
    
    
    nm = ActiveSheet.Name
    
    Set wkb = ActiveWorkbook
    Application.DisplayAlerts = False
    
    For Each ws In wkb.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs Filename:= _
        "\\'domain-01.com\dfs$\care-one\users\care-one_nanthony\desktop\Storage\" & nm _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
        
        ActiveWorkbook.Close SaveChanges:=True
    Next ws
End Sub

Open in new window


Dave
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 42

Expert Comment

by:dlmille
ID: 37731637
@ssaqibh - are you not getting any sleep these days?  Mines getting better, lately.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37731649
You mean sleep is inversely proportional to the points gotten at EE?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37731651
Could be but....

Nope - I just notice you round the clock and was concerned you weren't getting sleep or sun ;)

Dave
0
 

Author Comment

by:janthonyn
ID: 37737991
Thank you both very much for your help with this one. I tested this solution, but did not get good results. The sub runs through one time and saves the first tab in the correct folder but it is named "Sheet1". After that the sub exits and doesn't parse or save any other sheets. I opened Sheet1.xls and the tab is named "DATA." and doesn't include any of the data or structure of any of the tabs in the original workbook.  I'm at a loss for why this didn't work.
0
 
LVL 42

Accepted Solution

by:
dlmille earned 480 total points
ID: 37738530
Actually, I was just repairing your syntax errors, not looking at your logic, as I thought that was the help you were looking for.

However, I see the problem in that your file name is set only once and its outside your loop.  Try this, and review the changes I've made before you run it to ensure you understand what I did.

Sub ParseSheets()

    Dim wkb As Workbook
    Dim ws     As Worksheet
    Dim nm     As String
    
    
    Set wkb = ActiveWorkbook
    Application.DisplayAlerts = False
    
    For Each ws In wkb.Worksheets
        nm = ws.Name
        ws.Copy
        ActiveWorkbook.SaveAs Filename:= _
        "\\'domain-01.com\dfs$\care-one\users\care-one_nanthony\desktop\Storage\" & nm _
        , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
        
        ActiveWorkbook.Close SaveChanges:=True
    Next ws
End Sub

Open in new window


Dave
0
 

Author Closing Comment

by:janthonyn
ID: 37747870
Many thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

820 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