Access Report to Excel Template

[MrDeeds]

I want to take a report from Access and push it to an Excel template. The Excel template has logos etc in rows 1-10. I want the Access report to begin data on Row 11. Then I want the Save as box to pop up to force user to save the file as an xls. So the template is always there to receive the Access data. Any ideas? Thanks.

 

[MrDeeds]

Yes...set range to A20
Correct named sheet.

 

[Norie]

Transpose can sometimes have problems for various reasons, usually something to do with the data in the array you are trying to transpose.

Try this.

rng.CopyFromRecordset objRS

 

[MrDeeds]

Replaced rng.Resize... line with above code. Same error message...stops at that line.

 

[Kreszch68]

just for debugging

replace the rng.resize line with

rng.value = vdata

After running the code there should be 1 cell filled with a value from the recordset.
Other, if you try what Norie suggested, you need to remark the next lines

vdata = .getrows()
.close

in the section where the recordset is opened.

 

[Norie]

Kreszch

I've had problems using GetRows in the past usually involving Transpose, but sometimes when it doesn't return all records.

 

[Kreszch68]

Norie, retrieving records using GetRows with ADODB is different from the GetRows method when using ADO. The last one requires an exact count of the records, while ADODB will always return all records. Well, as far as I know.
Anyhow, I agree that the transpose function isn't the most elegant way.

The code below uses the copy from record method, so this should work for sure.

Public Sub CreateExcelInfo()
Dim oExcel As New Excel.Application
Dim WB As New Excel.Workbook
Dim WS As Excel.Worksheet
Dim rng As Excel.Range
Dim objConn As New ADODB.Connection
Dim objRs As New ADODB.Recordset
Const sFileNameTemplate As String = "PathAndNameXLS file"
Dim sSQL As String
Set objConn = CurrentProject.Connection
sSQL = "Select * from qTheQuery" 'This has to be the name of the query your report is using to display data
With oExcel
    .Visible = True
               'Create new workbook from the template file
                Set WB = .Workbooks.Add(sFileNameTemplate)
                        With WB
                             Set WS = WB.Worksheets("Sheet1") 'Replace with the name of actual sheet
                             With WS
                                      objRs.Open sSQL, objConn, adOpenStatic, adLockReadOnly
                                      Set rng = .Range("A1") 'Starting point of the data range
                                      rng.CopyFromRecordset objRs
                                      objRs.Close
                             End With
                        End With
    .Quit
End With
 
Set objConn = Nothing
Set objRs = Nothing
End Sub

 

[Norie]

Kreszch

ADO and ADODB are the basically the same thing.

Of course you've also got ADOX, MD and RDS.

So I'm not sure what you mean.

GetRows is meant to return all records if you omit the first argument but sometimes it doesn't, or at least doesn't seem to.

That's sometimes down to problems with the code.

For example the SQL statement is incorrect or badly formed.

 

[Kreszch68]

Yes, ok this is a very bad typo

I mean, DAO and ADODB.

In DAO you'll need to add the recordcount to retrieve all records, in ADO you don't.

 

[Norie]

I wondered, even had a quick Google as I thought I'd missed some other method.

Mind you I've still experienced problems with GetRows in ADO.

Though when I think about it the user^* was probably the problem, at least some of the time.

* Who might just have been me.

 

[MrDeeds]

Thanks so much...got it to work how I need. Thanks again.