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

[JonXL]

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

 

[Micron]

IIRC, in Excel vba a line wrap is just Chr(10). Try it without Chr(13).

 

[welshgasman]

When I wish to put text on the next line in a cell I use Alt + Enter ?
Could you emulate that.?

 

[JonXL]

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]

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]

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]

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.