please help me in exporting 2 access queries in same Excel file.

[chetna kalra via AccessMonster.com]

Can you please guide me with the below code?
whats wrong with it as I am getting an error of subscript out of range at
the line .Sheets("sheet1").Select

Please help.



Set xlsApp = Excel.Application


DoCmd.TransferSpreadsheet acExport, 8, "XX Qry for all EL", "T:\Shared
Services\IS and T\Shared08\chetna\Litig\legal.xls"
DoCmd.TransferSpreadsheet acExport, 8, "XX Qry for all EL totals", "T:\
Shared Services\IS and T\Shared08\chetna\Litig\legal.xls"
With xlsApp

'Open the workbook
.Workbooks.Open ("T:\Shared Services\IS and T\Shared08\chetna\Litig\
legal.xls")
'Copy the item data from 1st worksheet
.Sheets("XX_Qry_for_all_EL").Select
.Cells.Select
.Application.CutCopyMode = False
.Selection.Copy
'paste the data to sheet 1


.Sheets("sheet1").Select

.Cells.Select
.Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

.DisplayAlerts = False
'format the monetary columns
.Columns("P:T").Select
.Selection.NumberFormat = "0.00"
.Sheets("sheet1").Select
.Cells.Select
.Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array
(16, 17, 18 _
, 19, 20), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
.Selection.AutoFilter
.Selection.Columns.AutoFit

' remove the 2nd worksheet
.Sheets("XX_Qry_for_all_EL").Select
.ActiveWindow.SelectedSheets.Delete

' format totals sheet
.Sheets("XX_Qry_for_all_EL_totals").Select
.ActiveSheet.name = "Totals"
.Cells.Select
.Selection.Columns.AutoFit
.Columns("B:F").Select
.Selection.NumberFormat = "$#,##0.00"


' save as new file, then restore the legal wookbook
.ActiveWorkbook.Save

.ActiveWorkbook.SaveAs FileName:="T:\Shared Services\IS and T\
Shared08\chetna\Litig\legalEL.xls", FileFormat:=xlWorkbookNormal
.ActiveWorkbook.Close
.Workbooks.Open ("T:\Shared Services\IS and T\Shared08\chetna\Litig\
legal.xls")

.Sheets("Totals").Select
.ActiveWindow.SelectedSheets.Delete
.Sheets("sheet1").Select


.Cells.Select
.Cells.Delete
.ActiveSheet.name = "sheet1"
.ActiveWorkbook.Save
.ActiveWorkbook.Close

.DisplayAlerts = True

End With
xlsApp.Quit

MsgBox "LegalEL.xls spreadsheet complete"

 

[Guest]

Are you sure that "sheet1" exist in your spreadsheet with the exact name you
are using?

Mauricio Silva

 

[chetna kalra via AccessMonster.com]

Hi Mauricio Silva

sheet1 exist with every new excel file as it has sheet1, sheet2, sheet3 by
default when made new.

Can you help more?

Thanks in advence.