Copy from Access to Excel - blank cells do not behave as blanks 1

[donbolinger]

When I use the following Macro command:

RunCommand / OutputToExcel

It copies the data to Excel, and any blank cells appear to be blank however, when you use the End key plus the down arrow key to move to the next blank cell, it brings you to the bottom of the column. This leads me to believe there is something hidden in those blank cells but I can't figure out how to remove whatever might be in there.

This also happens when you just copy data from a table or query in Access and paste it into Excel.

Any ideas?
Thanks,
Don

 

[SkipVought]

Hi,

Do not use Copy 'n' Paste.

Rather use Data > Import External Data > Database Query to grab data in your Access dataase.

Once you have a querytable added to your sheet, it's just a matter of Data > Refresh to get a current resultset.

Should solve your END ARROW problem.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

The reason why they look empty but the End>down doesn;t stop on them is that when you export from Access to Excel, any blanks are exported as zero length strings - the equivalent of putting ="" in a cell and then doing copy/pastespecial>values.

To avoid this scenario, as Skip has suggested, query the data into excel rather than copy/pasting from Access

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff,

Hope you guys will cover for us colonists today? ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

lol - Sorry Skip - been a tad busy at work recently (2 men down out of 5). Hope you've had a coupla beers!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[donbolinger]

The Import External Data in Excel worked - thanks!

Still annoyed that the RunCommand / OutputToExcel in Access treats blank cells the way it does. Now I have to create a macro in Excel to set up the Import query on all of my associates' PCs...

Thanks for your help,
Don

 

[xlbo]

This is because Excel cannot handle nulls. In a recordset, you can have an absence of data but the nearest thing excel can get is a blank cell. A null pasted into excel must therefore be treated as something it isn't - the closest representation is a zero length string (as you must paste "something" - you cannot physically paste "nothing")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question