Search code examples
excelvbams-accessexport-to-excel

Export Access Query to Excel and add empty cells

I need to export an Access query into an Excel spreadsheet and add blank cells. My data from the Access query looks like this:

1018
522
347
449
271
221
7
6
238
90
185
34
117
144

But I will need it to look like this in Excel:

1018
522



347
449



271
221

And so on. I've done doCmd.TransferSpreadsheet but it doesn't allow for exporting ranges. Other examples I've seen that involve more VBA, don't go into formatting in that way. Any ideas where I should look? Thanks.

Solution

  • I don't have much time, so hope this makes sense:

    Make a make-table-query, that takes your source query, adds autonumber, and a field called Place= autonumber * 10, and creates a table called tblNew.

    1018 will have Place=1 * 10 = 10
522 will have Place= 2 * 10 = 20
347 will have Place=3 * 10 = 30
etc.

    Make a select query, qryBlank, that makes a blank line for each record with Place=20,40,60 etc. in tblNew. Each blank line must have Place = 20 +1, 40+1, 60+1 etc.

    Make a union query between tblNew and qryBlank.

    Now you have one line between the pairs. You can just add more queries like qryBlank to make more lines.

    Make a make-table query, that selects * from the union query, orders by the field Place, and creates a new table.

    Link to the new table in Excel.