Unable to copy Excel worksheets in VBA

[elliotgr]

Here is the code I am using in Access 2007 to manipulate Excel.
The sheets are renamed, but the workbooks remain hidden, and will not copy. It fails on the Copy After command.
I am using Excel 2007.
Any idea what is wrong?

....
DoCmd.TransferSpreadsheet acExport, 10, "tbl_A, "C:\A.xlsx, True
DoCmd.TransferSpreadsheet acExport, 10, "tbl_B", "C:\B.xlsx, True

Set MyXl = GetObject("C:\A.xlsx")
Set MyXl1 = GetObject("C:\B.xlsx")

MyXl.Worksheets(1).Name = "A_Sheet"
MyXl1.Worksheets(1).Name = "B_Sheet"

MyXl1.Worksheets("B_Sheet").Copy After:=MyXl.Worksheets("A_Sheet")
....

 

[DCrake]

Obviously this is not the whole code, so need to see more to give an accurate answer.

 

[elliotgr]

Thanks David
I am using the above snippet as it stands in a test sub and it will not work.
Not sure how the rest of the 6000 plus lines would go down?
However it appears there may be a problem using the Copy After function if Excel is not opened.
I used the following code in blue to force the two spreadsheets to become visible before the Copy After command runs and now it works

....
DoCmd.TransferSpreadsheet acExport, 10, "tbl_A, "C:\A.xlsx, True
DoCmd.TransferSpreadsheet acExport, 10, "tbl_B", "C:\B.xlsx, True

Set MyXl = GetObject("C:\A.xlsx")
Set MyXl1 = GetObject("C:\B.xlsx")

MyXl.Worksheets(1).Name = "A_Sheet"
MyXl1.Worksheets(1).Name = "B_Sheet"

MyXl.Application.Visible = True
MyXl.Windows("A.xlsx).Visible = True
MyXl1.Application.Visible = True
MyXl1.Windows("B.xlsx).Visible = True

MyXl1.Worksheets("B_Sheet").Copy After:=MyXl.Worksheets("A_Sheet")

Not pretty, but it works.
Any idea how to get around this problem?

 

[DCrake]

So basically you have one or more tables/queries that you want to export to Excel, however you want them to appear in the same workbook but on different worksheets in the order you specify.

If this is what you want I can tell you how to do this.

 

[elliotgr]

100% correct.
Don't tell me there is an easier way?

 

[DCrake]

Here is a code snippet of how I do it

Firstly create a new workbook that has your column headings in and any formatting you want and save it in the current project path

DestPath = Where you save the workbook to
TargetFile = name of the file you want to save it as

Public Function ExportIE2Excel(DestPath As String)
On Error Resume Next
Dim ssql As String
    
'Export the query and open Excel
   'Start a new session in Excel
   'If default template exists use that
    If Dir(CurrentProject.Path & "\TemplateAllOutput.xlsx") <> "" Then
        '/ If an earlier version exists then delete it first
        If Dir(DestPath & TargetFile) <> "" Then
            Kill DestPath & TargetFile
            DoEvents
        
        End If
        FileCopy CurrentProject.Path & "\TemplateAllOutput.xlsx", DestPath & TargetFile
        
        'This uses late binding method
        Set xlapp = CreateObject("Excel.Application")
        Set xlbook = xlapp.Workbooks.Open(DestPath & TargetFile)
        Set xlsheet = xlbook.Worksheets(1)
        
        Dim Rs As DAO.Recordset
            
            '/Query one on sheet 1       
            Set Rs = CurrentDb.OpenRecordset("QryIEDataGrp1")
            xlsheet.range("B1").CopyFromRecordset Rs

         Set xlsheet = xlbook.Worksheets(2)
            '/Query 2 on sheet 2
            Set Rs = CurrentDb.OpenRecordset("QryIEDataGrp2")
            xlsheet.range("B1").CopyFromRecordset Rs
           
            
            Rs.Close
            Set Rs = Nothing
            
            xlsheet.Columns("A:AL").EntireColumn.AutoFit
            xlsheet.range("C3:AL69").NumberFormat = "£#,##0.00"
            
            For x = 65 To 13 Step -1
               If xlsheet.range("B" & x).Value = "" Then
                  xlsheet.range("B" & x).EntireRow.Delete
              End If
            Next
            
            xlbook.Save
            xlapp.Quit
    
    End If

End Function

 

[elliotgr]

Thanks David
Makes sense.
I have never used the CopyFromRecordset so am keen to try.
Thanks for the help.
Glen