Macros going from 97 to 2003 - will they work?

[Myke]

Hi,

Our office is going from Excel 97 to 2003 in a couple of months time. I've done some basic macro's for a lot of sheets and have been told that they wont function the same under 2003. Is this true? and if it is, how difficult is it to convert them so that they will run under 2003????

 

[dk]

Hi,

Most code will work fine under 2003 if it was created in 97. There haven't been that many changes over the last few years. Problems are normally experienced when macros created in a recent version are used in an older version of Excel i.e. different functions, objects and function parameters.

If you want more clarification then post an example or two of your macros. People here will be able to check them for you.

HTH.

 

[Davers]

Hi Myke,

I've upgraded from 97, to 2000, to XP, to 2003, degraded to 2000, and upgraded back to XP....the only time I had problems with my macros is when I created a macro using a newer version of Excel, and then down graded to a previous version of Excel....I would get reference errors. For instance, my email code uses "Microsoft Outlook 10.0 Object Library" which is for XP...when I downgraded to 2000 and tried to run the code, it said it couldn't find the object reference...in 2000, I believe it was called "Microsoft Outlook 9.0 Object Library", so I had to switch the reference. I believe in 2003....they've upgraded the references again...

Hope this helps a bit...

Dave

P.S...other than that...I like the whole 2003 look...especially Outlook, however, in our office environment, we have Office versions from 98 all the way up to XP...(I was the only one with 2003) and it created too many compatibility issues for my reports...

 

[dk]

Davers,

In your case you would have been best off using late binding. That's the ONLY way to ensure code compatability when using different versions of MS software.

Also, I agree with you that 2003 is a nice looking piece of kit and I've finally got used to the way Outlook looks

Dan

 

[Davers]

Hey Dan,

Heh...after months of using my E-mail code that could only be used from certain computers... I took the time to search this board and converted my code to late binding....it still uses Microsoft Scripting Runtime though...because of this, I also found a snippet on how to left align the body of the e-mail...kind of cool...and just in case someone reads this and says.."man, i wish he woulda posted that code...I needed something like that..." here it is:

Sub sendMail()
'References needed:
'Microsoft Scripting Runtime

Dim appOutlook As Object
Dim MailItem As Object
Dim nowTime, myDir, myFile, thisBook, strTempFilePath, mySub, myTo, myCC As String

nowTime = Format(Range("D16").Value, "mm-dd-yyyy")
myDir = Range("B18").Value
mySub = Range("B25").Value
myTo = Range("B26").Value
myCC = Range("B27").Value
    
Set rngeSend = Application.Range("A1:O11")
Set FSObject = CreateObject("Scripting.FilesystemObject")
Set appOutlook = CreateObject("Outlook.Application")
Set MailItem = appOutlook.CreateItem(olMailItem)

tmpFile = FSObject.GetSpecialFolder(2)
tmpFile = tmpFile & "\myRange.htm"

ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
    tmpFile, rngeSend.Parent.Name, rngeSend.Address, xlHtmlStatic).Publish True
Set TStream = FSObject.OpenTextFile(tmpFile, ForReading)
strHTMLBody = TStream.ReadAll
strHTMLBody = Replace(strHTMLBody, "align=center", "align=left", , , vbTextCompare)

TStream.Close
Kill tmpFile

MailItem.HTMLBody = strHTMLBody
MailItem.Subject = (mySub & nowTime)
MailItem.To = (myTo)
MailItem.CC = (myCC)
MailItem.Attachments.Add myDir
MailItem.Display
    
Set MailItem = Nothing
Set appOutlook = Nothing
Set FSObject = Nothing
Set TStream = Nothing
    
End Sub

I use cell references to set my subject, to, and cc...and it attaches a file as well...saves me about 30 seconds or so per report in the morning....roughly 8 to 12 reports depending on what day of the week...that's enough time saved for another cup of coffee!!! Thanks MrExcel Board!!! I love you guys!!!

Have a good day, and thanks everybody for all the help!!!!!!

Dave