Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting from Excel 2003 to Excel 2010
I have some context information below, which is untopical. Please bear
with me. I've just bought a Lenovo X230 laptop to replace my X61S . I have Windows 7 Enterprise Service Pack 1 32-bit. [I googled to find how to get this information; a link required Silverlight to be installed to give me the information that I needed to do Start/Computer/Properties. I actually needed Start/Computer/System Properties.] That is REALLY good for me. I have some personally important software - mail and news handler - which does not work on 64-bit. While I do not have W7 Media, I can add and remove Windows features - I tried with Telnet Client. I have Microsoft Office Professional 2010. Again, lack of media does not seem to be a problem. Now I get to my difficulties. In 2003, I run with low security and happily put VBA macros in .xls files. 2010 seems willing to read such files, but, by default, creates .xlsx files, which I can't persuade Excel to hold macros. I have many .xls files in which I write an auto_open macro: Option Explicit Public Sub auto_open() Application.Run "PERSONAL.XLS!CheckDateConsistency" End Sub "PERSONAL!CheckDateConsistency" fails, 2003 does not type default. 2010 prefers to have macros in .xlsm files. I can probably cope with the default file type being .xlsm, but the default file type is .xlsx. If I copy my PERSONAL.XLS to C:\Users\Lenovo\AppData\Roaming\Microsoft\Excel\XL START [...AppData... as a hidden directory was an issue til I changed with ATTRIB -H] when I run any macro in PERSONAL.XLS, loading fails, I get "Compile error: Can't find project or library", focus is (spuriously) put on a call of the function Left and I am shown a list of libraries including MISSING: Microsoft Office Runtime 1.0 Type Library and MISSING: Microsoft Office Control 1.0 Type Library. Unchecking both allows PERSONAL.XLS to compile without error. I have files copied and openable. 2010 uses 7 lines above the column titles; 2003 uses 6. A small difference, but my new 12.5" screen is 16:9, my old one is 4:3. Ctrl+F1 toggles hiding and showing the ribbon. I would appreciate pointers to some good porting documentation. I want to move to 2010 with minimal pain and change. ;) (I also make some use of Word. I trust Excel lessons will easily port.) -- Walter Briscoe |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting from Excel 2003 to Excel 2010
Hi Walter,
Am Thu, 31 Jul 2014 16:24:37 +0100 schrieb Walter Briscoe: Public Sub auto_open() Application.Run "PERSONAL.XLS!CheckDateConsistency" End Sub "PERSONAL!CheckDateConsistency" fails, 2003 does not type default. 2010 prefers to have macros in .xlsm files. I can probably cope with the default file type being .xlsm, but the default file type is .xlsx. in xl2010 the personal macro workbook in named PERSONAL.xlsb Open Excel2010 = Record Macro = Save it in personal macro workbook = Cancel Now your PERSONAL.xlsb is created. Paste your Auto_Open macro into a module of the PERSONAL.xlsb and call it with Public Sub auto_open() Application.Run "PERSONAL.XLSB!CheckDateConsistency" End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting from Excel 2003 to Excel 2010
Walter,
I use xl2010 pro as well, but xl2003 is also installed on the same machine (this one) along with xl2007 running under Win7 x64 OS as x86. Both xl2007/2010 open my xl2003 PERSONAL.XLS file with no problem, and macros all work same as in xl2003. I've made *some* macros 'version-aware' so they work in early/late Excel. I have never needed to record macros in the later versions such that I end up creating 'personal.xlsb'. All my VBA projects are still developed in xl2003. They all work fine in the later versions because I've made their code 'version-aware'. This often requires revision in the later version so I have access to the objects/methods/functions not included in xl2003. The VBA project still saves in the original xl2003 format. I put my PERSONAL.XLS file in the default location... C:\Users\Garry\AppData\Roaming\Microsoft\Excel\XLS TART ...so Excel knows where to find it at startup. Since I share this file over my home network with 3 other machines running versions xl8 and later, I didn't want to have 2 versions of it. So far it works a dream! I just copy it to a network share where the other machines can access it at startup. One of those machines is a netbook, meaning it's portable and so I store its copy local in the same folder as mentioned. It and this one both are Win7 x64 OS, with the netbook running Home Premium. (The other 2 machines are Dell Precision series portable workstations running XP Pro x86) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting from Excel 2003 to Excel 2010
Thanks, Garry & Claus.
In message of Thu, 31 Jul 2014 15:43:30 in microsoft.public.excel.programming, GS writes Walter, I use xl2010 pro as well, but xl2003 is also installed on the same machine (this one) along with xl2007 running under Win7 x64 OS as x86. How would I add 2003 to a machine running 2010. I have media for 2003, but not for 2010. How do you select the version of Excel? Both xl2007/2010 open my xl2003 PERSONAL.XLS file with no problem, and macros all work same as in xl2003. I found that. I did a quick run with a large macro. There seemed to be an issue with the membership of an object. (TextFrame not containing AutoMargins in 2010.) If that is the level of incompatibility, I am much more comfortable. I've made *some* macros 'version-aware' so they work in early/late Excel. I have never needed to record macros in the later versions such I googled and found Application.Version = 11.0 in 2003 and 14.0 in 2010. that I end up creating 'personal.xlsb'. All my VBA projects are still developed in xl2003. They all work fine in the later versions because I've made their code 'version-aware'. This often requires revision in the later version so I have access to the objects/methods/functions not included in xl2003. The VBA project still saves in the original xl2003 format. I put my PERSONAL.XLS file in the default location... C:\Users\Garry\AppData\Roaming\Microsoft\Excel\XLS TART I did the equivalent operation. It was convenient that I found an empty XLSTART folder. ..so Excel knows where to find it at startup. Since I share this file over my home network with 3 other machines running versions xl8 and later, I didn't want to have 2 versions of it. So far it works a dream! I just copy it to a network share where the other machines can access it at startup. One of those machines is a netbook, meaning it's portable and so I store its copy local in the same folder as mentioned. It and this one both are Win7 x64 OS, with the netbook running Home Premium. Do you run 64 bit Office at all? I would not expect any advantage to me from doing so. I don't care about 64-bit addresses and have no need for 64-nit int. (The other 2 machines are Dell Precision series portable workstations running XP Pro x86) I am much more confident than I was at first sight. ;) -- Walter Briscoe |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting from Excel 2003 to Excel 2010
How would I add 2003 to a machine running 2010. I have media for
2003, but not for 2010. What do you mean by "I have media for 2003"? My MSO11 Pro is on a disc. My MSO14 Pro is a download. Just install both, but the last install will become the default. If you want 2010 as default then run 'Repair' in 'Programs and Features' to restore it as default. How do you select the version of Excel? I use 'Freebar' from sourceforge! It's a VB6 replacement for the Office Toolbar. I put a shortcut on there for whatever apps I want and just select from there to run whatever version I want. Fortunately, each version has a unique icon, making it easy to distinguish them. The toolbar also displays a 'tooltip' when you hover over its icons. Note that the usual notification about personal.xls being already open displays when running more than 1 version. You can get Freebar here... http://freebar.sourceforge.net/en/ -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting from Excel 2003 to Excel 2010
In message of Fri, 1 Aug 2014 03:31:38 in
microsoft.public.excel.programming, GS writes How would I add 2003 to a machine running 2010. I have media for 2003, but not for 2010. What do you mean by "I have media for 2003"? I mean I have Office 2003 on a disc. My MSO11 Pro is on a disc. My MSO14 Pro is a download. Just install I infer I can reload MS014,given a URL and some possible magic.;) both, but the last install will become the default. If you want 2010 as default then run 'Repair' in 'Programs and Features' to restore it as default. How do you select the version of Excel? I use 'Freebar' from sourceforge! It's a VB6 replacement for the Office Toolbar. I put a shortcut on there for whatever apps I want and just select from there to run whatever version I want. Fortunately, each version has a unique icon, making it easy to distinguish them. The toolbar also displays a 'tooltip' when you hover over its icons. Note that the usual notification about personal.xls being already open displays when running more than 1 version. You can get Freebar here... http://freebar.sourceforge.net/en/ Thanks, that is likely to be REALLY useful. I referred to some object model issues. A long time ago, I wrote Sub ShowShapes() to dump ActiveSheet Shapes to the Immediate Window. It does not work in 2010. I have commented On Error calls to show some of the discrepancies. I created a Macro to create a "Hello, World" textbox and added a call of Showshapes to it. In 2003, this is written to the Immediate Window. 1 shapes Ix Name Shapetype Left,Top,Width,Height AM, AS, M(L, T, R, B) Text 1.0 Text Box 4 msoShapeRectangle 195,107.25,164.25,87 Tr, Fa, M(7.2,3.6,7.2,3.6) F(Regular,Arial,8): "Hello, World" In 2011, I get a 1004 error, when I try to access ActiveSheet.Shapes(1).TextFrame.AutoMargins Without suppressing On Error statements, the code writes 1 shapes Ix Name Shapetype Left,Top,Width,Height AM, AS, M(L, T, R, B) Text 1.0 Text Box 4 msoShapeRectangle 195,107.25,164.25,87 Tr, Fa, M(7.2,3.6,7.2,3.6) I would value any insight into the deficiencies in my code. In 2011, ActiveSheet.Shapes(1).TextFrame.AutoMargins shows in the Locals Window as <Application-defined or object-defined error I use shapes as I am given worksheets containing shapes. ;( Option Explicit Sub Macro1() ' ' Macro1 Macro ' Macro recorded 01/08/2014 by IBM ' ' If ActiveSheet.Shapes.Count < 0 Then ActiveSheet.Shapes(1).Delete ActiveSheet.Shapes.AddTextbox(msoTextOrientationHo rizontal, 195#, 107.25, _ 164.25, 87#).Select Selection.Characters.Text = "Hello, World" With Selection.Characters(Start:=1, Length:=12).Font .Name = "Arial" .FontStyle = "Regular" .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With ShowShapes Stop End Sub Public Sub ShowShapes() ' Noddy to dump shapes on ActiveSheet Dim GroupName As String Dim i As Long, j As Long Dim o As ShapeRange Dim s As String Dim Saved As Boolean Dim v As Shape Dim W As Shape Saved = ActiveWorkbook.Saved Debug.Print ActiveSheet.Shapes.Count & " shapes" Debug.Print Left("Ix ", 6) & _ Left("Name" & " ", 14) & _ Left("Shapetype ", 19) & _ Left("Left,Top,Width,Height" & " ", 28) & _ "AM, AS, M(L, T, R, B) Text" i = 0 For Each v In ActiveSheet.Shapes i = i + 1 If Not v.Name Like "Group *" Then Debug.Print ShapeLine(i, 0, v) Else GroupName = v.Name ' Can't analyse a group without destroying it Debug.Print ShapeLine(i, 0, v) & "consists of " & _ v.GroupItems.Count & " items" Set o = v.Ungroup j = 0 For Each W In o j = j + 1: Debug.Print ShapeLine(i, j, W) Next W o.Group ' Recreate group ' Restore default name V is destroyed by ungroup ActiveSheet.Shapes(i).Name = GroupName End If Next v ActiveWorkbook.Saved = Saved End Sub Private Function ShapeLine(ByVal Imain As Long, ByVal Isub As Long, _ ByVal v As Shape) As String Dim ShapeType As String Dim Text As String ShapeType = TXAutoShapeType(v) Text = ShapeText(v) ShapeLine = Left(Imain & "." & Isub & " ", 6) & _ Left(v.Name & " ", 14) & _ Left(ShapeType, 19) & _ Left(v.Left & "," & v.Top & "," & v.Width & "," & _ v.Height & " ", 28) & _ Text End Function Private Function TXAutoShapeType(ByVal x As Shape) As String Dim s As String Select Case x.AutoShapeType Case msoShapeMixed: s = "msoShapeMixed" Case msoShapeRectangle: s = "msoShapeRectangle" Case Else Debug.Print "Untranslated AutoShapeType: " & x.AutoShapeType & _ "." Debug.Print "cf. x.AutoShapeType in Locals window to get name" Debug.Assert False ' Force error End Select s = Left(s & " ", 20) TXAutoShapeType = s End Function Private Function ShapeText(ByVal v As Shape) As String ' On Error statements in this are suppressed. Dim s As String Dim i As Long Dim j As Long ' On Error Resume Next With v.TextFrame ' ' Crashes here with a 1004 as AutoMargins has invalid contents. ' s = IIf(.AutoMargins, "Tr, ", "Fa, ") & _ IIf(.AutoSize, "Tr, ", "Fa, ") s = s & "M(" & .MarginLeft & "," & .MarginTop & "," & _ .MarginRight & "," & .MarginBottom & ") " With .Characters.Font If Err.Number < 0 Then _ Exit Function ' Return empty string if no textframe ' On Error GoTo 0 ' Any errors now are fatal s = s & "F(" & .FontStyle & "," & .Name & "," & _ .Size & "): """ End With ' On Error Resume Next j = .Characters.Count If Err.Number < 0 Then ' On Error GoTo 0 ' Any errors now are fatal s = s & "NO TEXT" Else ' On Error GoTo 0 ' Any errors now are fatal ' Text limits itself to 255 bytes For i = 1 To j Step 255 s = s & .Characters(Start:=i).Text Next i End If End With s = s & """" ShapeText = s End Function -- Walter Briscoe |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting from Excel 2003 to Excel 2010
I don't do much with 'Shapes' but I'll play around with it over the
weekend... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting from Excel 2003 to Excel 2010
In message of Fri, 1 Aug 2014 18:49:50 in
microsoft.public.excel.programming, GS writes I don't do much with 'Shapes' but I'll play around with it over the weekend... Thanks, Garry. I did not say that my Sub Macro1() should be run in a new WorkSheet. I like to construct failures which are standalone and can easily be ported by anyone interested. I shall play too. ;) -- Walter Briscoe |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting from Excel 2003 to Excel 2010
In message of Sat, 2 Aug 2014
08:03:51 in microsoft.public.excel.programming, Walter Briscoe writes In message of Fri, 1 Aug 2014 18:49:50 in microsoft.public.excel.programming, GS writes I don't do much with 'Shapes' but I'll play around with it over the weekend... Thanks, Garry. I did not say that my Sub Macro1() should be run in a new WorkSheet. I like to construct failures which are standalone and can easily be ported by anyone interested. I shall play too. ;) I have a hypothesis: Excel text boxes do not support automatic margins in 2010. In 2003, I right-clicked the box right hand edge and selected Format Text Box... I selected the Margins Tab and found Automatic checked and the margins blank. When I unchecked Automatic, the margins were given values of Left: 0.25 cm, Top: 0.13 cm, Right: 0.25 cm and Bottom: 0.13 cm. In 2010, I right-clicked the box right hand edge and, in the absence of Format Text Box..., I selected Format Text Effect... I selected the Text Box entry and found the same margins as given above, but no automatic margin control. Hence my hypothesis. I shall have to write some version-dependent code. If I had support, I would raise a query with Microsoft on a possible undocumented change. -- Walter Briscoe |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting from Excel 2003 to Excel 2010
So far I found in 2003/2010 ObjectBrowser...
ShapeTextFrameAutoMargins ...is a common property of type Boolean. Perhaps the issue is that of using a fully qualified ref to the specific 'Shape' object in question? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting from Excel 2003 to Excel 2010
In message of Sat, 2 Aug 2014 06:07:09 in
microsoft.public.excel.programming, GS writes So far I found in 2003/2010 ObjectBrowser... ShapeTextFrameAutoMargins ..is a common property of type Boolean. Perhaps the issue is that of using a fully qualified ref to the specific 'Shape' object in question? Why might that be a problem? How would I access it otherwise? I now have some code which "seems to work" in both Excel 2003 and 2010. I simplified my driving code: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 01/08/2014 by IBM ' ' Dim sh As Shape If ActiveSheet.Shapes.Count < 0 Then ActiveSheet.Shapes(1).Delete Set sh = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHo rizontal, 195#, 107.25, 164.25, 87#) sh.TextFrame.Characters.Text = "Hello, World" ShowShapes Stop End Sub I removed the On Error logic from Function ShapeText and put some version specific code in place. It is GOOD to be rid of error handling code. ;) It is POOR to have version specific code. ;( Private Function ShapeText(ByVal v As Shape) As String Dim s As String Dim i As Long Dim j As Long Dim TF As TextFrame Dim Ch As Characters Set TF = v.TextFrame With TF If Application.Version = "14.0" Then s = "Un, " ' Automargins does not seem defined in Excel 2010 Else s = IIf(.AutoMargins, "Tr, ", "Fa, ") End If s = s & IIf(.AutoSize, "Tr, ", "Fa, ") s = s & "M(" & .MarginLeft & "," & .MarginTop & "," & _ .MarginRight & "," & .MarginBottom & ") " Set Ch = .Characters If Ch.Text = "" Then ShapeText = s & "NO TEXT": Exit Function With Ch.Font s = s & "F(" & .FontStyle & "," & .Name & "," & .Size & "): """ End With j = Ch.Count ' Text limits itself to 255 bytes in 2003. Safe limit not checked in 2010 For i = 1 To j Step 255 s = s & .Characters(Start:=i).Text Next i End With s = s & """" ShapeText = s End Function Characters is a curious member of TextFrame, behaving as both a Property and a Method. I find it a pity that View\Locals does not show it as a Property. I am now comfortable that I can move on to the next porting issue. Thanks to both Claus and Garry. -- Walter Briscoe |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting from Excel 2003 to Excel 2010
It is GOOD to be rid of error handling code. ;)
I find error handling an essential part of programming and so consider it just part of 'good practice'! It is POOR to have version specific code. ;( I find that making code to work in all versions needs to be 'version-aware' and so also consider that as being a 'good practice' part of programming!<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting from Excel 2003 to Excel 2010
I meant to point out that as of MSO 2010, VBA7 is used. Earlier
versions run VBA6! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2010 to auto-generate an email to recipients in outlook 2010 | Excel Programming | |||
Converting macros from Excel 2003 to Excel 2010 - Client Access dialog | Excel Programming | |||
How do I update calenders on Office 2003 to 2010 in Excel | Excel Discussion (Misc queries) | |||
Excel 2003 to Excel 2010 | Excel Discussion (Misc queries) | |||
Porting Excel 2003 | Setting up and Configuration of Excel |