2007 Excel vs. 2003

[Jenny B.]

Good Afternoon,

I'm trying to switch a spreadsheet with Programming/Macros from Office 2003
to 2007 . I'm having some compatibility issues and I'm hoping someone can
help.

In Excel 2003, if you had Text Fields or Shapes and were sending to a new
workbook, these were automatically ported over (meaning you'd have to program
in a logic if you didn't want them to show up the new worksheet). I'm hoping
to continue that working in Office 2007 but that doesn't appear to be the
case. Since I have so many Text Fields and shapes I'm carrying over to the
unformatted worksheet, I'm hoping there is a solution (vs. referencing all
fields) to have it carry these over automatically.

I'm writing from an existing workbook to a new sheet and below is the code.
I have to keep the doc in XLS form even though I'm working in 2007 Excel
(some users are still on 2003). When use the routine below from Excel 2007,
it will not bring the fields over and half of the page appears blank since
Excel 2003 automatically brings them over.

Any thoughts on a quick fix?

Thank you in advance - Jenny B.


Sub Finalstop()

Dim myPath As String
Dim nRng As Range
Dim fName As String
Set nRng = Range("F64")

ActiveSheet.Copy

myPath = "I:\Document Management\Print management\Operations\B55
Docs\B55 Testing Checklist\"
fName = nRng.Value & ".xls"

ActiveWorkbook.SaveAs Filename:= _
myPath & fName, FileFormat:= _
xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
ActiveSheet.Visible = False

End Sub

 

[Barb Reinhardt]

If you're running in both Excel 2007 and 2003 and need to have different code
for each, do something like this

If Val(Application.Version) >= 12 then
'Excel 2007 code
else
'Excel 2003 code
end if

 

[Jenny B.]

Hi Barb,

Thank you so very much. I will just change up the code to the Else
Statement you provided to accommodate both versions. I was just hoping there
would be an easier way without bogging it down with more code, but I should
have known there would be more differences between the versions than I
originally thought.

Thank you for your time and solution – Jenny B.