I have 6 tabs. I want to export 1st, 3rd and 5th tabs into an excel 1st sheet, 2nd sheet and 3rd sheet.
So, what i’m trying are:
export a report that have multiple tabs.
export each tab into each sheet in an excel.
I have created a code below but is not exporting correctly.
Could someone help me finding out what is wrong?
Sub menuoptions()
Dim Rep As Report
Dim num_of_reps As Integer
Dim i As Integer
Dim BOCmdBar As CmdBar
Dim BOCmdBarControls As CmdBarControls
Dim BOControlButton As Control
Dim BOCmdBarPopup As CmdBarPopup
Dim BOCmdBarButton As CmdBarButton
Dim objXlWkbk1 As Object
Dim objXlSheet As Object
Dim oXLApp As Object
Dim sXLSheetName(6) As String
sXLSheetName(1) = "Sheet1"
sXLSheetName(2) = "Sheet2"
sXLSheetName(3) = "Sheet3"
num_of_reps = ActiveDocument.Reports.Count
Set BOCmdBar = Application.CmdBars.Item(2)
Set BOCmdBarControls = BOCmdBar.Controls
Set BOCmdBarPopup = BOCmdBarControls.Item(2)
Set BOCmdBarButton = BOCmdBarPopup.CmdBar.Controls.Item(20)
Set oXLApp = CreateObject("Excel.Application")
oXLApp.Workbooks.Open "C:\test.xls"
Dim j As Integer
j = 0
For i = 1 To num_of_reps
If i Mod 2 = 1 Then
j = j + 1
Set Rep = ActiveDocument.Reports.Item(i)
BOCmdBarButton.Execute
oXLApp.Sheets(sXLSheetName(j)).Select
oXLApp.Range("A1").Select
oXLApp.Sheets(sXLSheetName(j)).Paste
End If
Next i
oXLApp.ActiveWorkbook.Save
oXLApp.ActiveWorkbook.Close
oXLApp.Quit
End Sub
Your code does a pretty neat trick by copying the whole report and pasting it in excel. However, it is a shortcut that might fail in certain scenarios. Olivier and I had coded an add-in that allows you to do xls and other conversions. However, it uses the export to txt function to export the report to xls. See if this helps you…
I could modify this code to help if you explain what problems you are facing while exporting?
The problem is that somehow BO is not recognizing the “Sheet” part. The function runs fine. I don’t receive any error message. However, when I check the excel file, only the first tab report is pasted. And, funny thing is that it is pasted on the 1st worksheet sometime, and the other times on the 3rd worksheet…
It looks like you are doing a Copy All and pasting the contents in Excel. Its not that it will only do this for first tab. It all depends on which report tab you were active while you call the macro. Your loop looks fine. I would suggest you to activate the corresponding report tabs before invoking the Copy All and pasting it in Excel. Jus add this…
ActiveDocument.Reports.Item(i).Activate
after you are doing that j=j+1
For i = 1 To num_of_reps
If i Mod 2 = 1 Then
j = j + 1
Set Rep = ActiveDocument.Reports.Item(i)
[color=blue]Put the above code here.[/color]
BOCmdBarButton.Execute
oXLApp.Sheets(sXLSheetName(j)).Select
oXLApp.Range("A1").Select
oXLApp.Sheets(sXLSheetName(j)).Paste
End If
Next i
(BOB member since 2002-08-22)
(BOB member since 2002-11-08)