VB Macro in Excel

Posted on 2003-03-14
Medium Priority
Last Modified: 2010-08-05
Hello :-) I am trying to get a macro that will take a spreadsheet from excel that has 210 separate worksheets in it and separate and save them as separate files. Instead of recording these steps for each worksheet I am trying to get a loop to do it for me. I am getting errors on what I have so far. The sample listed below is only for the first 3 worksheets in the spreadsheet. Also after I have this task completed I need to e-mail all of these individual spreadsheets out through Microsoft Outlook. Does anyone know of a quick way to write a loop to do that???

The code:

Sub Separate2()
' Separate2 Macro
' Macro recorded 3/5/2003 by Tatha
Dim Wrksheet As Worksheet
For Each Wrksheet In Sheets(Array("Accolade", "Aimco", "Alpha"))
    'choose the name of the management co
    Filename = ActiveCell.Value
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Tatha\Desktop\" & Filename
Next Wrksheet
End Sub

Thank you for your time...
Question by:TAtha
  • 2

Expert Comment

ID: 8136368
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Sheets
    MsgBox wks.Name

this loops through all worksheets and outputs their sheet name, however you could add your code in here to make it save the sheet. If there is one sheet you dont want to save (eg a menu page or something) use and if clause and check the name of the sheet and make sure it not that page.

Im not sure about your active window, since the sheets are all part of the same window, maybe you want wks.delete (since you have saved it elsewhere it doesnt matter.)

It would be useful to know what errors you are getting


Accepted Solution

dmang earned 225 total points
ID: 8136508
In Excel VBE:

 add a reference to the outlook object library, then try the following

    Dim objOutlook As Outlook.Application
    Dim objMail As MailItem
    Dim olRecipient As Recipient
    Dim aName As String
    Dim aCopyTo As String
    Dim fName As String
    Dim wbName As String
    Set objOutlook = New Outlook.Application
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    wbName = ActiveWorkbook.Name
    For w = 1 To Worksheets.Count
        aName = Range("A4:G4").Select
        fName = "C:\Documents and Settings\Tatha\Desktop\" & ActiveCell.Value & ".xls"
        ActiveWorkbook.SaveAs FileName:=fName
        Set objMail = objOutlook.CreateItem(olMailItem) 'Create a new mailitem
        With objMail
            Set olRecipient = objMail.Recipients.Add(aName)
            '*** remove the next block if addresses not in outlook address book
            If Not olRecipient.Resolve Then
                .To = aName
                If Len(strCopyTo) > 0 Then
                    Set olRecipient = objMail.Recipients.Add(aCopyTo)
                    If Not olRecipient.Resolve Then
                        objMail.Recipients.Remove (objMail.Recipients.Count)
                        .CC = aCopyTo
                    End If
                End If
            End If
            .Subject = "Subject Description"
            .Body = "A message you want to provide"
            .Attachments.Add strAttachMent, , , "Workbook Description goes here"
        End With
        Set objMail = Nothing
    Next w
    Set objOutlook = Nothing

Expert Comment

ID: 8138367
Sounds like both answers sort the problems, any way of splitting points? LOl

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month8 days, 23 hours left to enroll

621 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