Excel 2007 VBA and Macros

[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.

 

[Arvin Meyer [MVP]]

Are you possibly running into some limitations of permissions due to the
lack of a cert?

Try moving your application to and, creating your new spreadsheet in,
Trusted Location. Use the Office button and Options to get there and create
the location. If you can't find it in Excel (I'm not at an Office 2007
machine to check right now), it's definitely in that location in Access.
Also, if you're running in Vista, you can try logging in as administrator
and trying to run the application. If it works, Vista is the problem and
you'll need to deal with the permissions issue.

 

[SBilo]

I tried your suggesion, I added the location of the VB6 application to
Excel2007 as a trusted location. That did NOT help. The resulting workbooks
have data with pivot tables but NO forms and NO code gets passed into the VBA
project area ... it did with Excel2003 and Excel2000.

And the VB6 application still crashes if I leave the .printarea = $A$5:$E$18.

Any other ideas of what I can try?

 

[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