Renaming not working correctly

[kaostheory]

Hey Guys


How would I go about renaming a excel spreadsheet to a specific name. I need the following renamed
Sheet 1 = ABC
Sheet 2 = DEF
Sheet 3 = HIJ


Im new to the whole VBA thing and not sure where to start

[kaostheory]

worked it out. Sooo simple i can now call myself an idiot

Sheet1.name = "ABC"

etc

[kaostheory]

Ok I have a problem I have run into. The workbook starts with 3 spreadsheets preset and I am adding a 4th then Im renaming worksheets and when it is renaming its going thru and when it get to rename "s4" to Other its renaming s3 to "Other"

What am I doing wrong? If more code is needed please advise

Set excBook = excApp.workbooks.Add
Set s1 = excBook.Worksheets(1)
s1.name = "Test 1"
Set s2 = excBook.Worksheets(2)
s2.name = "Test 2"
Set s3 = excBook.Worksheets(3)
s3.name = "Test 3"
excBook.Worksheets.Add
Set s4 = excBook.Worksheets(4)
s4.name = "Other"


This is driving me freaking nuts. What am I doing wrong?

Help apprciated.

[lucas]

Help me understand. You are trying to create a new workbook that comes with 3 sheets by default.

Then you are renaming the 3 sheets.

You are then adding a sheet and renaming it.

How am I doing?

[kaostheory]

The whole Macro so far is below. What its doing is pulling all emails from a specified folder. Exporting them to excel and adding to a specific spreadsheet dependant on the Subject line in the email. Theres 4 spreadsheets with 3 static subjects and a 4 case which if it doesnt fit into the first 3 is added to this sheet.


[VBA]
Sub ExportToExcel()

Set excApp = CreateObject("Excel.Application")

subj1 = "Test 1"
subj2 = "Test 2"
subj3 = "Test 3"

Set excBook = excApp.workbooks.Add
Set s1 = excBook.Worksheets(1)
s1.name = "Test 1"
Set s2 = excBook.Worksheets(2)
s2.name = "Test 2"
Set s3 = excBook.Worksheets(3)
s3.name = "Test 3"
excBook.Worksheets.Add
Set s4 = excBook.Worksheets(4)
s4.name = "Other"


For Each s In excBook.Worksheets
s.Columns(1).columnwidth = 15
s.Columns(2).columnwidth = 100
s.cells(1, 1) = "Date"
s.cells(1, 2) = "Body"
Next s

Dim oOutlook As Outlook.Application
Dim oNs As Outlook.NameSpace
Dim oFldr As Outlook.MAPIFolder


Set oOutlook = New Outlook.Application
Set oNs = oOutlook.GetNamespace("MAPI")
Set oFldr = oNs.Folders("Mailbox - Test").Folders("Test")
r1 = 2
r2 = 2
r3 = 2
r4 = 2

For Each m In oFldr.Items

Select Case m.Subject
Case subj1
s1.cells(r1, 1) = m.ReceivedTime
s1.cells(r1, 2) = m.Body
r1 = r1 + 1
Case subj2
s2.cells(r2, 1) = m.ReceivedTime
s2.cells(r2, 2) = m.Body
r2 = r2 + 1
Case subj3
s3.cells(r3, 1) = m.ReceivedTime
s3.cells(r3, 2) = m.Body
r3 = r3 + 1

End Select


Next m

Set oFldr = Nothing
Set oNs = Nothing
Set oOutlook = Nothing

excBook.SaveAs "C:\test.xlsx"

excBook.Close False

MsgBox "Done"

End Sub
[/VBA]

[lucas]

are running this from Outlook?

I request that you put option explicit at the top of your module and dim all variables appropriatly.....post here if you need help deciding.

[kaostheory]

so you are running this from Outlook?

Yes. This is being run from outlook. Everything else works just the spreadsheet rename bit im having troubles.

I request that you put option explicit at the top of your module and dim all variables appropriatly.....post here if you need help deciding.

Help would be muchly appreciated

[lucas]

I didn't go to the trouble to run this from outlook but it seems to work from excel:

[VBA]Option Explicit
Sub a()
Dim Wkb As Workbook
Dim excBook As Workbook
Dim S1 As Worksheet
Dim S2 As Worksheet
Dim s3 As Worksheet
Dim s4 As Worksheet
Dim TotalSheets As Variant
TotalSheets = Worksheets.Count - 1
Set excBook = Workbooks.Add
Set S1 = excBook.Worksheets(1)
S1.Name = "Test 1"
Set S2 = excBook.Worksheets(2)
S2.Name = "Test 2"
Set s3 = excBook.Worksheets(3)
s3.Name = "Test 3"
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Other"
End Sub[/VBA]