View Full Version : Renaming not working correctly
kaostheory
11-11-2009, 06:15 PM
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
11-11-2009, 06:51 PM
worked it out. Sooo simple i can now call myself an idiot
Sheet1.name = "ABC"
etc
kaostheory
11-11-2009, 07:58 PM
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?
:banghead:Help apprciated.:banghead:
lucas
11-11-2009, 09:01 PM
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
11-11-2009, 09:11 PM
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.
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
lucas
11-11-2009, 09:16 PM
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
11-11-2009, 09:19 PM
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
11-11-2009, 09:46 PM
I didn't go to the trouble to run this from outlook but it seems to work from excel:
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.