Multi-Line Access Field Converts to 'Single' Line on Excel Output

JonXL

Active Member
Joined
Feb 5, 2018
Messages
469
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,

I'm running into an issue with exporting to Excel where two lines within a field show up as one line in the Excel cell. Here's what's happening in detail...
  • I'm using DoCmd.TransferSpreadsheet to export a query
  • The query is based on a table with address fields; the fields are separated out into Address line, City, State, and ZIP
  • In the query, I combine the address information into a single output with the CSZ (City, State, ZIP) on a separate line with this code:
    • [Address] & Chr(13) & Chr(10) & [City] & ', ' & [State] & ' ' & [ZIP]
  • In the Access query - when viewed in Access - the field looks right with the newline showing the CSZ separate from the Address
  • However... in the Excel export, the cell shows them all in one line (as if the query were [Address] & [City] & ', ' & [State] & ' ' & [ZIP]), except:
    • When I click into the cell, they switch to two lines
    • If I enter out of the cell, they stay on two lines
It seems pretty clear that the new line information is making it into the cell, but for some reason Excel doesn't display it unless I click into each of the cells - not an option.

Is anyone aware of a solution to this? I'm thinking something on the lines of a different way to indicate the new line that Excel more happily recognizes would be preferred as the whole export process is pretty set (it's all based on tables for different export jobs and the code behind the exporting is the same for all export jobs on the export table; so I'd prefer a way that just involves modifying the query...).

Any thoughts on how to make the address show up on the Excel output on two lines is appreciated!

Thanks,
Jon
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,129
Office Version
  1. 365
Platform
  1. Windows
IIRC, in Excel vba a line wrap is just Chr(10). Try it without Chr(13).
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
885
When I wish to put text on the next line in a cell I use Alt + Enter ?
Could you emulate that.?
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
469
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
IIRC, in Excel vba a line wrap is just Chr(10). Try it without Chr(13).

I was pretty hopeful when I read your reply because it reminded me of a project I did a while back where I had to put line breaks into a cell from within Excel VBA and that I used Chr(10) to get the job done.

But, alas, it was hope dashed. This didn't do the trick either. :(

Unless I have an epiphany or just happen across the answer, I'll just have to leave it with a space between the two rows - on the odd chance someone needs that address, they should be able to figure it out. I don't know that this is going to be worth some of the work I know would fix the problem.

Thank you for the suggestion!
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
469
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

When I wish to put text on the next line in a cell I use Alt + Enter ?
Could you emulate that.?
I mean... I could emulate that, but the effort to do so probably wouldn't be worth the payoff given how little this field will be used. I was just thinking it'd be nice if there were an easy way to make it look pretty anyway that didn't involve a bunch of custom scripting for just this one report.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,129
Office Version
  1. 365
Platform
  1. Windows
I don't recall the last time I used transfer spreadsheet. It's odd that activating the cell results in what you want.
Next time, try a re-calc on the worksheet (shift+F9) and see if that does the same thing. If so, maybe you could incorporate that - say on wb opening. Automation is the only other thing I can think of, but it sounds like that would be more trouble than it's worth.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
469
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Next time, try a re-calc on the worksheet (shift+F9) and see if that does the same thing. If so, maybe you could incorporate that - say on wb opening.

Unfortunately that didn't work either. I would have certainly updated the code to do a recalc on all the exports if it had.

After playing around, though, I found that no matter how wide the cell is, if you highlight the column and choose Wrap Text, Excel will break it at the new line characters. Based on that, I think I've come up with the best possible given the constraints. I rewrote the query to pull the cell value as this: [Address] & " " & Chr(13) & Chr(10) & [City] & ', ' & [State] & ' ' & [ZIP]. In Access, it will show correctly by having both Chr() codes. In Excel, by default, the address will be separated by a space - which users may be just fine with. Any users who want to see it on two rows can get it formatted that way with two clicks: select the column, click Wrap Text. This isn't perfect, but it's something I can live with and gives the option for either format and the two-line addresses can be had with only a couple clicks instead of clicking into every cell like I thought would be necessary.

Thank you for the suggestions. I appreciate you taking a look at the problem for me.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,869
Messages
5,655,705
Members
418,230
Latest member
Jimmy_Jef

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top