xlObj.ActiveSheet.Copy - subscript out of range

  • Thread starter ragtopcaddy via AccessMonster.com
  • Start date
R

ragtopcaddy via AccessMonster.com

I am having a problem with the following code. I get a "Subscript out of
range" msg on the ".ActiveSheet.Copy" line. The code was set up previously
and ran OK without the "." in the front of ".ActiveSheet.Copy", so it was
not a part of the xlObj. Then it started to not work, so I changed it. The
1st 2 .Activesheet methods work fine. There are definitely 3 worksheets in
the target workbook.

DoCmd.TransferText acExportDelim, , strQuery, strPath & strCSVFile, True,
""
Set xlObj = CreateObject("excel.application")
With xlObj
With .Workbooks
.Open strPath & strFile
.Open strPath & strCSVFile
End With
.ActiveSheet.Select
.ActiveSheet.Name = strQuery
.ActiveSheet.Copy After:=Workbooks(strFile).Sheets(3)
.Save
.Quit
End With
Set xlObj = Nothing
Kill (strPath & strCSVFile)
 
Top