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

  • Thread starter Thread starter chetna kalra via AccessMonster.com
  • Start date Start date
C

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"
 
Are you sure that "sheet1" exist in your spreadsheet with the exact name you
are using?

Mauricio Silva
 
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.
 
Back
Top