Question Pasting to Excel at a certain location?

[MOTOWN44]

Morning all

I have a table (tbl1) and I want to transfer the data from that on to a template spreadsheet(spreadsheet1) which has headers etc already visible on the top.

I appreciate that its easier to use outputtoexcel etc but i’ve been told I have to use this spreadsheet to send my data on (lawyers eh?!)

I can create a new spreadsheet and paste the data across but what im wanting to do is automate the process, paste the info, send the email with attachment etc.

The bit in struggling with is how to get paste the info from my table to a certain point in the spreadsheet, cell A8 would be the starting cell I need to paste from.

Also if possible if we could do this with out the field headers from the table that would be brilliant if not I can write in a way of deleting the header line using a WITH function (I think, if im wrong here please point me in the right direction

Any help will be greatly received

Thanks – Matthew

 

[DCrake]

Do a search on CopyFromRecordset for a solution

 

[MOTOWN44]

Ive had a play on an i cant seem to get it to define a recordset properly

ive got this so far

Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim RS As ADODB.Recordset

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("H:\sheet.xls")
Set xlWs = xlWb.Worksheets("Sheet1")
Set RS = CurrentDb.Connection.OpenRecordset("TestTable")
xlOBJ.Visible = True
'RS.Close
'Set RS = Nothing
'Set xlWs = Nothing
'Set xlWb = Nothing
'Set xlApp = Nothing

which in theory should open up the spreadsheet sheet.xls but i keep getting an "operation not support for this type of object" error

sorry if i seem a bit dim about this, i havnt even gotten to the actual copying and pasting yet!

im having flash backs on why ive never used this fucntion before!

Cheers

 

[DCrake]

Is there any reason why you are using ADODB instead of DAO?

 

[DCrake]

Example using DAO and Late binding

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)
            '/Corporate
            Dim Rs As DAO.Recordset
            
                   
            Set Rs = CurrentDb.OpenRecordset("QryIEDataGrp1")
            xlsheet.range("B13").CopyFromRecordset Rs
            
            Set Rs = CurrentDb.OpenRecordset("QryIEDataGrp2")
            xlsheet.range("X13").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__________________

 

[MOTOWN44]

No idea tbh im just going off what ive seen online.
it now saying "User defined type not defined over the DAO.Recordset which i assume is a referene library problem?

 

[DCrake]

You need to go to Tools > References and reference Microsoft DAO n.nn Object Library.