?
Solved

VB Macro in Excel

Posted on 2003-03-14
3
Medium Priority
?
206 Views
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
    Range("A4:G4").Select
    Filename = ActiveCell.Value
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\Tatha\Desktop\" & Filename
    ActiveWindow.Close
Next Wrksheet
End Sub

Thank you for your time...
0
Comment
Question by:TAtha
  • 2
3 Comments
 
LVL 3

Expert Comment

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

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

0
 
LVL 9

Accepted Solution

by:
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
        Workbooks(wbName).Activate
        aName = Range("A4:G4").Select
        Sheets(Worksheets(w).Name).Select
        Sheets(Worksheets(w).Name).Copy
       
        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
            Else
                .To = aName
                If Len(strCopyTo) > 0 Then
                    Set olRecipient = objMail.Recipients.Add(aCopyTo)
                    If Not olRecipient.Resolve Then
                        objMail.Recipients.Remove (objMail.Recipients.Count)
                    Else
                        .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"
            .Send
        End With
        Set objMail = Nothing
    Next w
   
    Set objOutlook = Nothing
0
 
LVL 3

Expert Comment

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

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