+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
MIS Systems Thread, sims address block in Technical; when running a report from sims into excel the address block data refuses to be a block and only ever ...
  1. #1

    Join Date
    Dec 2008
    Posts
    9
    Thank Post
    1
    Thanked 1 Time in 1 Post
    Rep Power
    0

    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?

  2. #2

    bossman's Avatar
    Join Date
    Nov 2005
    Location
    England
    Posts
    4,033
    Thank Post
    1,262
    Thanked 1,107 Times in 785 Posts
    Rep Power
    338
    @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.

  3. Thanks to bossman from:

    webman (12th December 2008)

  4. #3
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    42
    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.

  5. #4

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,522
    Thank Post
    374
    Thanked 673 Times in 549 Posts
    Rep Power
    166
    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.

  6. #5

    Join Date
    Feb 2008
    Posts
    23
    Thank Post
    3
    Thanked 4 Times in 4 Posts
    Rep Power
    14
    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 :/

  7. #6

    Join Date
    Feb 2008
    Posts
    23
    Thank Post
    3
    Thanked 4 Times in 4 Posts
    Rep Power
    14
    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

  8. #7

    bossman's Avatar
    Join Date
    Nov 2005
    Location
    England
    Posts
    4,033
    Thank Post
    1,262
    Thanked 1,107 Times in 785 Posts
    Rep Power
    338
    @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.

  9. #8

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,522
    Thank Post
    374
    Thanked 673 Times in 549 Posts
    Rep Power
    166
    You could, I think, add that macro to your default template, for a one off fix. At least until Capita fixes it

  10. #9

    Join Date
    Feb 2008
    Posts
    23
    Thank Post
    3
    Thanked 4 Times in 4 Posts
    Rep Power
    14
    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?

  11. #10
    ChrisH's Avatar
    Join Date
    Jun 2005
    Location
    East Lancs
    Posts
    5,006
    Thank Post
    124
    Thanked 286 Times in 263 Posts
    Rep Power
    109
    I use the same method as Bossman.

  12. #11

    webman's Avatar
    Join Date
    Nov 2005
    Location
    North East England
    Posts
    8,420
    Thank Post
    644
    Thanked 966 Times in 666 Posts
    Blog Entries
    2
    Rep Power
    328
    Quote Originally Posted by Willzzz View Post
    I think there is a better way still..
    Yeah, bossman's method works fine...

  13. #12

    Join Date
    Dec 2008
    Posts
    9
    Thank Post
    1
    Thanked 1 Time in 1 Post
    Rep Power
    0
    Quote Originally Posted by bossman View Post
    @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!

  14. #13

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,522
    Thank Post
    374
    Thanked 673 Times in 549 Posts
    Rep Power
    166
    It's sitting with Capita's support team, I've logged it today, so it will be fixed within 20 working days.

  15. #14

    Join Date
    Feb 2006
    Location
    South Cumbria
    Posts
    199
    Thank Post
    26
    Thanked 29 Times in 24 Posts
    Rep Power
    22
    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.

  16. #15

    bossman's Avatar
    Join Date
    Nov 2005
    Location
    England
    Posts
    4,033
    Thank Post
    1,262
    Thanked 1,107 Times in 785 Posts
    Rep Power
    338
    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!!



SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. another 1 to block
    By in forum Links
    Replies: 33
    Last Post: 15th December 2006, 03:14 PM
  2. One to block
    By indiegirl in forum Jokes/Interweb Things
    Replies: 5
    Last Post: 14th June 2006, 03:25 PM
  3. block hotmail?
    By adamyoung in forum Windows
    Replies: 32
    Last Post: 16th March 2006, 11:00 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •