Export Access Query to Excel and add empty cells

Question

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

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

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.

What have you already attempted in VBA to get the result you want? — Mr. Mascaro
– Mr. Mascaro, Commented Sep 29, 2014 at 20:38
I've tried adapting both of these answers: stackoverflow.com/questions/19547118/… and stackoverflow.com/questions/20810306/… but neither seems to work with adding empty cells. — extispex
– extispex, Commented Sep 29, 2014 at 20:54

MNor · Accepted Answer · 2014-09-29 21:26:54Z

0

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.

answered Sep 29, 2014 at 21:26

MNor

836 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

extispex Over a year ago

Interesting idea. I will try that tomorrow and let you know how it goes.

extispex Over a year ago

I couldn't use your method because exactly because I only have read permissions on the data, so make table won't work. But I took your idea in a different way. I created a separate query to make blank lines and unioned the two queries together multiple times. It's a horribly way to do it, I'm sure, but it gets me exactly what I needed. So thanks for the inspiration and I'm sure your way would help others so I marked it as answered.

MNor Over a year ago

Sweet. Work-arounds is what Access is all about.

Collectives™ on Stack Overflow

Export Access Query to Excel and add empty cells

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related