Solved

Worksheet parsing to a specific folder

Posted on 2012-03-16
9
278 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 41

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 41

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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 41

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 41

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 41

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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