Thread: Export from active worksheet to closed workbook

Under certain circumstances, yes. You can use ADO to export data from the activeworkbook to a sheet in a closed workbook as long as that destination workbook has headers, and as long as you include a header row in the source that you export from. For example:

Sub ExportDataFromThisWorkbookToClosedWorkbook()
   ' Sample demonstrating how to export data from the current workbook to a closed workbook
   Dim cn As Object, strQuery As String
   Set cn = CreateObject("ADODB.Connection")
   With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
         "Extended Properties=""Excel 8.0;HDR=Yes;"""
      .Open
   End With
   strQuery = "INSERT INTO [Sheet1$] IN '' [Excel 8.0;Database=C:\ADO Dest.xls] SELECT * FROM [Sheet1$B2:F3]"

   cn.Execute strQuery
   cn.Close
   Set cn = Nothing
End Sub

Note that there is a memory leak issue with using ADO on an open workbook, but as long as you only run one or two exports at a time (and then close Excel) it shouldn't be a problem.