I have a large application that creates a report following a test. The report is about 250 pages, with each page represented as a group in the data portal. When we deliver the report the customer also wants an excel file with the X-Y data pairs for each of the graphs in the report. I know how to do the data selection and the copy/paste to excel. Where I run into problems is when I am adding worksheets to Excel, I can't figure out how to direct the new worksheet to be added to the end.
According to the VBA info about Excel there are parameters Before and After that should allow you to do this. I just can't figure out the syntax to include these parameters. The line of code in question is in red.
If you have nothing but the .Add statement the code works but the worksheets show up in reverse order. I have tried a number of different ways and the script just dies without even showing an error. I know a work around which is to cycle through the list of groups backwards, but that just doesn't sit well with me. I would prefer to do things in a logical manner being from first to last.
If there are any questions please let me know.
Thanks in advance.
Wayne
FilePath = autoactpath & "\dataoutputtemplate.xls"
ExOpen = True
WorkBookName = NameSplit(FilePath, "N") & ".XLS"
On Error Resume Next
Set Excel = GetObject(,"Excel.Application") ' Get Excel via OLE if it is open already
If Err.Number <> 0 Then
Err.Clear
Set Excel = CreateObject("Excel.Application") ' Start Excel via OLE
If Err.Number <> 0 Then
'Exit Sub
End If
ExOpen = False
End If
Excel.Visible = true
IsWorkbookOpen = Excel.Workbooks(WorkBookName) Is Nothing = False
If ExOpen Then
If Not IsWorkbookOpen then
set ExcelWkb = Excel.Workbooks.Open(FilePath) ' Open Excel File if not open already
End If
else
set ExcelWkb = Excel.Workbooks.Open(FilePath) ' Open Excel File since excel was not already open
End If
Err.Clear
On Error Goto 0
GrpStart = GroupIndexGet("Globals")
NumGrp = GroupCount - GrpStart
ReDim PageList(NumGrp)
ReDim SectList(NumGrp)
b = 1
c = 1
for a = GrpStart to GroupCount
CurGrpName = GroupPropValGet(a,"name")
If InStr(CurGrpName,"List") then
SectList(b) = Mid(CurGrpName,5) 'Removes "List" from the Group Name to be used in the Table of Contents
b = b + 1
End If
If InStr(CurGrpName,"_") then
PageList(c) = CurGrpName 'Gets a list of groups that need to be exported
c = c + 1
End If
next 'a (cycle through groups to gather names)
'Add Table Of Contents Sheet to workbook
Set ExTOC = Excel.Workbooks(WorkBookName).WorkSheets.Add
ExTOC.name = "Table Of Contents"
For d = 1 to c-1 'c is the number of valid sheets in the report that will be exported
Set ExcelSht = Excel.Workbooks(WorkBookName).WorkSheets.Add (After:=Last sheet in workbook)
ExcelSht.Name = PageList(d)
Next 'd next report page to be exported
