sims address block

when running a report from sims into excel the address block data refuses to be a block and only ever comes out as a line which is not much use to me as i have to mail merge my spreadsheet to word and need the address in a block!

has anybody found this problem/ a way round this problem?

@winklehock:

Instead of using the address block you also have the option of individually choosing the address details which when you export into excel gives you what you require ok.

Just tried it on version 7.112 ok.

Yeah i would do what bossman says, and yeah you will get it all in one line, remember you are exporting to excel i.e. to a cell.

I'm on Dec, I've got X!$X instead of a newline. Just about to log it over to Capita.

 

@winklehock check your on Nov 2, failing that, do what bossman says.

Yes we have the same problem, it is very strange as it used to work fine. In fact we had a time when it worked on certain computers, but not others.

 

I believe there is a way around it, you need to do a find and replace on the incorrect newline character and replace it with the correct newline character. Someone showed me the other day, but they aren't in today :/

OK I have a solution, not very elegant but it works! I think there is a better way still..

 

1) export address block to word

2) replace ^p with a unique string (eg CRLF)

3) copy paste into excel

4) run this macro

 

Sub ReplaceCRLF()

Cells.Replace What:="CRLF", Replacement:=Chr(10), LookAt:=xlPart, _

SearchOrder:=xlByRows

End Sub

@Willzzz:

Hey if that is what works for you then good for you but I feel my method is much cleaner and quicker as the address block is what it states an address block so it will always be exported as just that "a block" but then I don't programme Sims.net just have to work with it ok. :)

You could, I think, add that macro to your default template, for a one off fix. At least until Capita fixes it

Forget what I said earlier

 

1) Export address line then replace ", " with Chr(10)

 

Using the individual address parts is awkward when some of them are blank isn't it?

I use the same method as Bossman.

I think there is a better way still..

 

Yeah, bossman's method works fine...

@winklehock:

Instead of using the address block you also have the option of individually choosing the address details which when you export into excel gives you what you require ok.

Just tried it on version 7.112 ok.

 

legend!

forgot about doing that, tried calling our support company and they apprently know of the problem and dont know how to fix it!

It's sitting with Capita's support team, I've logged it today, so it will be fixed within 20 working days.

This is something that got broken in either the August or Sept upgrade (I can't remember which). The work round I was given by Capita is to set the output to text - XML (without schema) and save the file somewhere handy . Open the resulting XML file with Excel and resave as an Excel workbook.

reports system now broken in November release HUH!!!

But not to worry i have a cunning plan Mr Rodway (webman) I have a patch hehe!! :)

Not another one that will take 6 hours to complete on a thursday evening preventing us from going to college?