Automation works with Excel 2003 but NOT Excel 2007

[SBilo]

I have an old VB6 application that creates Excel workbooks which include
pivot tables, graphing, forms and vba code to handle button and double-click
events on the workbook. This was coded with some old version of MsOffice and
has been working fine for a number of years including with Office 2003.

I'm encountering a number of issues when attempting to run this VB6
application with Office 2007.

The first problem is while the VB6 application is assigning a range to the
PrintArea, i.e. "With xlSheet.PageSetup.PrintArea = strTemp" where strTemp
has a value = $A$5:$E$18. The error returned to VB6 is -2147352560
Automation error Invalid callee. I can get around this error by changing
the line of code to be: .PrintArea =
xlApp.ActiveCell.PivotTable.TableRange2.Address Not quite certain why this
is happening or this bypasses the error but at least a workbook gets created
and populated with data.

The bigger problem is that the workbook that gets created with Excel 2007
does NOT contain the various VBA objects and code. Some code stubs are
created but don't contain inner code. e.g.

Private Sub cmdBreakdown_Click()

End Sub

but if run with Excel 2003 this procedure contains code such as:

Private Sub cmdBreakdown_Click()
Dim intCol As Integer, intTemp As Integer
intTemp = 15
intCol = 131
Do Until Cells(intTemp, intCol) = ""
If Trim(Cells(intTemp, intCol)) = strBreakdownLevel1 And Trim(Cells(intTemp,
intCol + 1)) = strBreakdownLevel2 And Trim(Cells(intTemp, intCol + 2)) =
strBreakdownLevel3 Then
lngBreakdownSum = lngBreakdownSum + Cells(intTemp, intCol + 5)
End If
intTemp = intTemp + 1
Loop
DataBreakdown.Show
End Sub


In Excel 2007 under Macro Security I have enable all ActiveX controls,
enabled all macros, allowed Trust access to the VBA project, enable all data
connections, etc.

Any ideas of what changed between office 2003 and office 2007 that is
preventing these code items, forms and modules from getting copied from the
VB6 application into the workbook ?

regards.

 

[Ronald Dodge]

I am not sure if this has any bearing on your code, but generally speaking,
relying on a particular object (I.e. a worksheet in this case) to be the
active object isn't a good idea unless you can reassure that the object will
always be the active object when the code for that object is ran. Due to
various issues that I ran into, which goes back to XL97 and I'm quite sure
it would apply to even XL07, I generally avoid relying on active objects and
I also avoid using Activate and Select methods.

In your code, your "Cells" object is relying on the active worksheet being
the correct worksheet on being the worksheet that you want it to process.
If this isn't the case or you don't like the idea of another worksheet
possibly being active, you probably will want to prequalify your
objects/properties such as the following:

Thisworkbook.Worksheets("Sheet1").Cells(intTemp, intCol + 5)

If you going to reference the same object multiple times, you more than
likely will want to assign that object to an object variable.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[SBilo]

It is active because it is being created in the VB6 code at that point in
time ...

Set xlApp = CreateObject("Excel.Application")

Set xlBook = xlApp.Workbooks.Add(1)
xlBook.Parent.Windows(xlBook.Name).Visible = True
xlBook.SaveAs strTemp

Set xlSheet = xlBook.ActiveSheet
strCodeName = xlSheet.Name
xlSheet.Name = "System"

... code here to get data and stuff if into a pivot table

... then code to call function for formatting the worksheet

' Format the worksheet for printing
.Cells(13, 1).Select
strTemp = xlApp.ActiveCell.PivotTable.TableRange2.Address
If Not FormatFBComplianceStationSummarySheet(xlApp, xlSheet,
strTemp, strType) Then GoTo ErrorHandler

.... function below



Private Function FormatSummarySheet(xlApp As Excel.Application, xlSheet As
Excel.Worksheet, strTemp As String, strType As String) As Boolean


With xlSheet.PageSetup
.LeftHeader = ""
.CenterHeader = Format(gdteMonth, "mmm yyyy") & strType & " Summary "
.RightHeader = ""
.LeftFooter = Format(Date, "mmm dd, yyyy")
.RightFooter = "Pg " + "&P" + " of " + "&N"
.LeftMargin = xlApp.InchesToPoints(0.5)
.RightMargin = xlApp.InchesToPoints(0.5)
.TopMargin = xlApp.InchesToPoints(1.25)
.BottomMargin = xlApp.InchesToPoints(0.75)
.HeaderMargin = xlApp.InchesToPoints(0.75)
.FooterMargin = xlApp.InchesToPoints(0.25)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintTitleRows = xlSheet.Rows(14).Address
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.PrintArea = strTemp
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1000
End With

 

[Ronald Dodge]

One way to get around the code issue that you described, you could setup one
workbook as a template, then within your main code, you could use the method
of "SaveCopyAs" on the template workbook. This way, everything in the
template workbook gets saved as a new workbook.

Main reason why I mentioned about the Activate and Select methods as well as
Active(object) is cause I have experienced issues in the past that I didn't
like. Mostly when I'm running VBA code within Excel and I know it's going
to take it a while to run (such as when I run all of my production reports,
many of which is pulling data from the main DB system, putting into Excel,
manipulating the data, then formatting the data, and even once a week
printing out the machine center charts), I may be working in some other
application with Excel in the background. Well with the Activate method, it
at times has a tendency of causing Excel to become the active window in the
middle of running the code, which I haven't been able to fully avoid due to
what I have to do to get the ShowCase Query add-in to be able to pull the
data. I have talked with the software's vendor about the issue, which they
didn't promise anything, but they may look into the issue to see if they can
allow for allowing advanced users such as myself to be able to use workbook
and worksheet objects instead of having to rely on the Activate method to
make sure the workbook/worksheet is the active object before being able to
pull the data. That's cause I hate it when I'm working in some other
application directly with my reports being ran within Excel and all of a
suddenly, Excel becomes the active program cause of this Activate method.
That's just one such type issue I have ran into with using this method, so I
prefer to prequalify my objects and properties with defined object variables
rather than relying on either implied active objects or using active object
variables.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[SBilo]

FYI The resolution to the main problem was:

Where it used to blow up before:

' Create the SYSTEM workbook
Set xlBook = xlApp.Workbooks.Add(1)
xlBook.Parent.Windows(xlBook.Name).Visible = True
Strtemp = D:\Compliance Report .xlsm
xlBook.SaveAs strTemp <--- WOULD BLOW UP HERE



Now afterwards THIS WORKS:
' Create the SYSTEM workbook
Set xlBook = xlApp.Workbooks.Add(1)
xlBook.Parent.Windows(xlBook.Name).Visible = True
Strtemp = D:\Compliance Report .xlsm xlBook.SaveAs FileName:=strTemp,
FileFormat:=xlOpenXMLWorkbookMacroEnabled




FYI The resolution to the second problem was:

change where the lines that had
..PrintArea = strTemp ‘ (NOTE strTemp has a range value string)


to the following ‘(NOTE which contains the SAME range
value as strTemp …. Go figure)
..PrintArea = xlApp.ActiveCell.PivotTable.TableRange2.Address

 

[Ronald Dodge]

Quote:

Strtemp = D:\Compliance Report .xlsm xlBook.SaveAs FileName:=strTemp,

Both commands on the same line? This seems awkward to me.

Even if the 2 commands are on separate lines, it seems weird that you had to
assign the argument name to the variable prior, which is optionally
available to do, but the other way of using methods is to just go in order
of the way the arguments are listed without having to use the argument
names.

Now, for the SaveAs method, if the code is attempting to overwrite a file
that is already existing with that same file name in the same file location,
then it will error out and when the app is invisible, it will blow cause
then it can't even display the alert.

Also, note, even setting the "DisplayAlerts" property on the application
doesn't prevent this particular dialog box from popping up, so the way to
get around that issue is to use the "SaveCopyAs" method in place of the
"SaveAs" method.

One other big difference between these 2 methods:

With the SaveAs method, any formulas of other currently open workbooks
refering to the workbook that is being saved via the SaveAs method will be
adjusted to the new file location. This however does not take place when
using the SaveCopyAs method.



With my production reporting system, I use the "SaveCopyAs" method as it
avoids the displaying of the dialog box and also since the purpose of using
the "SaveCopyAs" is to backup my report files in the event that the
following takes place:

The main Excel file crashes and can not be repaired efficiently without
having to go through a bunch of work

IT department takes too long of a time period to get around to restoring the
lost report file(s)


This situation happened to an entire department's folder about 9 years ago
and it took the IT department 3 full weeks before even getting around to
restoring the folder. Oh, after that situation, I had to think long and
hard about that one, even though it wasn't the department that I was in that
was impacted by it. However, if it took IT department that long and only
cause I got back onto them about it, what was it going to be like, if even
just one of my report files got corrupted like that?

I certainly didn't like the idea of having to rebuild the file completely
from scratch. At that time, we used Excel 97, which was a very unstable
version for me, even SR-2 was very unstable as Excel seemed to be crashing
on me weekly. I also didn't like the various bugs in Excel 97, which MS
sent me Office 2000 free of charge as a fix to one of the charting bugs in
Excel 97, as it was already fixed in Excel 2000, and they weren't going to
go back and fix it in Excel 97. To tell you the truth, I would have rather
worked with Lotus 1-2-3, ver 2.3 (You got it, the DOS version of the
spreadsheet program), than I would have with Excel 97, SR-2, due to all of
the technical issues that I faced with the Excel program. Excel 2000
however changed all of that as Excel 2000 had a bunch of those technical
issues resolved.

As a result of all of these things, not only did I make a copy of the files
and store in a separate location, but I also created a secondary backup
system that saved copies onto the local hard drive as a supplimentary backup
system to the primary backup system. I built this into my production
reporting system and I have been using it ever since. This backup system
not only had the benefits that I planned for, but it also had unplanned
benefits that I ended up using it for. I currently have 4 different backup
systems in place in addition to the primary backup system that the IT
department has in place. I have had to use each one of the different backup
systems at some point of time, though not all 4 at the same time, but a
different one at a different point of time depending on what went out and
when it went out. By having these backup systems in place, I have not lost
one bit of data over the years of reporting numbers.

I have to admit one of the 4 backup systems was actually created by MS
themselves, though indirectly, but that's how it worked out. I have had to
use that backup system at times too.

One of the 4 backup systems was created for when the file server itself goes
down but yet, the network itself is still up and running, which has allowed
the operators to keep reporting their data to the files without having to
worry about running into saving issues.

The last backup system, which I coincidentally created, wasn't really
created as a backup system, but worked out as such, and it also has been
used in rare circumstances for restoring data.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000