D
deltaquattro
Hi,
I have a macro containing workbook Main.xlm (Excel 2007) which creates
some Pivot Tables and Pivot Charts, and I would like to save them in
another workbook, let's call it Report.xlsx, without including:
1. the VBA macros
2. some other sheets which are not part of the Pivot Charts
All this must be done without breaking the link among Pivot Tables and
Pivot Charts, i.e. when I open Report.xlsx, I want to still be able to
modify the Pivot Charts, filter, change rows, fields, etc.
In Excel 2000 I just had to Move the sheets containing Pivot Tables
and Pivot Charts from Main.xls to Report.xls. With Excel 2007, if I
try this, the Pivot Charts become plain charts, which is really not
making the internal customer happy. Can you help me? I tried the
following:
Code:
Sub CreateReport(NonPivotSheets() As String, wbkName As String)
'Create a workbook containing all sheets except the ones in
NonPivotSheets, to workbook wbkName.
Dim Path As String, ReportFullName As String, MainFullName As String,
MainName As String, wbk As Workbook
'Save the path and name of the Main workbook
MainFullName = ThisWorkbook.FullName
MainName = ThisWorkbook.Name
Path = ThisWorkbook.Path
ReportFullName = Path & "\" & wbkName
'Create the Report
Application.DisplayAlerts = False
' I'm deleting the sheets which I don't want to move the Report, from
the Main workbook: this is
' not a problem, because I'm only saving the changes in the Report
workbook
ActiveWorkbook.Sheets(NonPivotSheets).Delete
'I save the changes as the Report workbook: NOTE the file format
option, to force
'saving in a format without macros
ActiveWorkbook.SaveAs FileName:=ReportFullName,
FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
'I set a reference to the Report workbook
Set wbk = ActiveWorkbook
'I reopen the Main workbook, that was closed by the SaveAs method
before
Workbooks.Open MainWbkFullName
Workbooks(MainName).Activate
'I need to close the Report workbook, because otherwise Excel prompts
the user to save the Report workbook in a format including macros,
which I don't want
wbk.Close SaveChanges:=True
'HERE is the problem: I would like to conclude execution by reopening
the Report workbook and
'displaying it, but for some reasons it doesn't work, and I don't get
any error message!
Workbooks.Open ReportFullName
End Sub
So, the problem is in the last line: no matter what I do, I can't get
VBA to reopen the Report workbook. Can you help me? I'm reallyn stuck
this time. Thanks,
Best Regards
Sergio Rossi
I have a macro containing workbook Main.xlm (Excel 2007) which creates
some Pivot Tables and Pivot Charts, and I would like to save them in
another workbook, let's call it Report.xlsx, without including:
1. the VBA macros
2. some other sheets which are not part of the Pivot Charts
All this must be done without breaking the link among Pivot Tables and
Pivot Charts, i.e. when I open Report.xlsx, I want to still be able to
modify the Pivot Charts, filter, change rows, fields, etc.
In Excel 2000 I just had to Move the sheets containing Pivot Tables
and Pivot Charts from Main.xls to Report.xls. With Excel 2007, if I
try this, the Pivot Charts become plain charts, which is really not
making the internal customer happy. Can you help me? I tried the
following:
Code:
Sub CreateReport(NonPivotSheets() As String, wbkName As String)
'Create a workbook containing all sheets except the ones in
NonPivotSheets, to workbook wbkName.
Dim Path As String, ReportFullName As String, MainFullName As String,
MainName As String, wbk As Workbook
'Save the path and name of the Main workbook
MainFullName = ThisWorkbook.FullName
MainName = ThisWorkbook.Name
Path = ThisWorkbook.Path
ReportFullName = Path & "\" & wbkName
'Create the Report
Application.DisplayAlerts = False
' I'm deleting the sheets which I don't want to move the Report, from
the Main workbook: this is
' not a problem, because I'm only saving the changes in the Report
workbook
ActiveWorkbook.Sheets(NonPivotSheets).Delete
'I save the changes as the Report workbook: NOTE the file format
option, to force
'saving in a format without macros
ActiveWorkbook.SaveAs FileName:=ReportFullName,
FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
'I set a reference to the Report workbook
Set wbk = ActiveWorkbook
'I reopen the Main workbook, that was closed by the SaveAs method
before
Workbooks.Open MainWbkFullName
Workbooks(MainName).Activate
'I need to close the Report workbook, because otherwise Excel prompts
the user to save the Report workbook in a format including macros,
which I don't want
wbk.Close SaveChanges:=True
'HERE is the problem: I would like to conclude execution by reopening
the Report workbook and
'displaying it, but for some reasons it doesn't work, and I don't get
any error message!
Workbooks.Open ReportFullName
End Sub
So, the problem is in the last line: no matter what I do, I can't get
VBA to reopen the Report workbook. Can you help me? I'm reallyn stuck
this time. Thanks,
Best Regards
Sergio Rossi