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

JonXL

Active Member
Joined
Feb 5, 2018
Messages
354
Office Version
365, 2016
Platform
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,882
Office Version
365
Platform
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
839
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
354
Office Version
365, 2016
Platform
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
354
Office Version
365, 2016
Platform
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
1,882
Office Version
365
Platform
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
354
Office Version
365, 2016
Platform
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,240
Messages
5,510,006
Members
408,771
Latest member
Sigmawolf101

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top