Export to Excel to Specific Row 1

[madhouse]

I have a problem which probably sounds more complicated than it actually is.

Basically I have a table called tblData which contains 5 fields that I want to export out to an Excel worksheet - there will only ever be one record in this table since it gets cleared out daily. I have no problem with exporting data to an Excel worksheet as I've done this before. However, I have only ever appended data to the last row of a worksheet that contains no column headers, footers etc. But on this occasion the first 3 rows of the worksheet contain titles and column headers, the next 31 rows is where the data will get exported to and then the next row (row 35) contains the columns footers & totals. Also, the first column of the worksheet contains titles for each row. Hope your with me so far!
The 31 rows mentioned above correspond to the days of the month, except obviously for months that have 30 days are less where there will then be some blank rows.
So each day I wish to export the data from tblData into the next blank row under the column headers. The way I have previously exported from Access to Excel won't work because the procedure I use reads from the bottom of the worksheet upwards until it finds the first row containing data. But doing it this way will mean the first row of data it finds will be the one containing the column footers & totals.

I know there is probably a straightforward solution but I just can't seem to find it. So if anyone can help I would be most grateful, and if you could provide some sample code I will be even more grateful

 

[danvlas]

Sub ExcelCommand()
'change 15 to whatever row number you want
Const myrow = 15
Dim myapp
Dim mywb, myws, myrange
Dim rst As DAO.Recordset
Dim i As Integer
Set myapp = CreateObject("Excel.Application&quot
'Change the path and name of the file
Set mywb = myapp.Workbooks.Open("C:\TestXls.xls&quot
'Change the name of the sheet
Set myws = mywb.Worksheets("Shet1&quot
'Put your recordset here
Set rst = CurrentDb.OpenRecordset("Select * From alphabet&quot

'It starts with the first column,
'but no big deal to move it to the right...
For i = 1 To rst.Fields.Count
myws.cells(myrow, i) = rst.Fields(i - 1)
Next
rst.Close
Set rst = Nothing
mywb.Close acSaveYes
Set myws = Nothing
Set myapp = Nothing
'That's it
End Sub

It also works with TransferSpreadshet method, but it also transfers the table heading, which you don't want...

Good luck

Daniel Vlas
Systems Consultant

 

[madhouse]

Thanks for the reply danvlas - your example is not quite what I'm looking for but it's a start. The main problem I see with your example is that you have to hard code what row you the data exported to. Since I will be looking at running the export on a daily basis I was looking for a way of not having to hard code the row number.

 

[danvlas]

myws.usedrange.rows.Count gives you the last used row at the end of the worksheet

You could check the first empty row within the used range by looping through it:

For i = 1 to myws.usedrange.rows.Count
If myws.Cells(1, i) = "" Then
'This is the row, so assign the value of i to the variable
Exit For
End If
Next

HTH


Daniel Vlas
Systems Consultant