+ Post New Thread
Results 1 to 8 of 8
Coding Thread, Excel/Word VB Coding question in Coding and Web Development; I'm looking to take a list of staff and create their E-mail signatures (becuase they can't be trusted to do ...
  1. #1

    Join Date
    Nov 2007
    Location
    Rotherham
    Posts
    1,679
    Thank Post
    122
    Thanked 126 Times in 102 Posts
    Rep Power
    46

    Excel/Word VB Coding question

    I'm looking to take a list of staff and create their E-mail signatures (becuase they can't be trusted to do it themselves) and I'm looking for some ideas.

    The signature files are saved as 2 text files where just the variables such as phone number, name and job title would change.

    now I could create all this as a mail merge but I would need to save each file individually (which would be the issue). Alternatively if could output direct from excel into the text file this would be fine.

    But I'm at a bit of a loss about the best way to create the output. Is there a way to use the VB script to acomplish this task.

    If it makes more sense I would start with a list

    Person1 Job1 Phone1
    Person2 Job2 Phone2


    And export the text
    Person1
    Job1
    Phone1
    <SchoolAddress>

    To a file <filename>.txt

  2. #2

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,824
    Thank Post
    371
    Thanked 544 Times in 507 Posts
    Rep Power
    184
    Do you actually want this in word/excel? Or is it just pure txt files and vbscript?

    Steve

  3. #3

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,824
    Thank Post
    371
    Thanked 544 Times in 507 Posts
    Rep Power
    184
    For a really lazy way - Macro based excel

    Example data:
    test111.jpg

    Macro:

    Code:
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    Dim stream As TextStream
    
    For i = 1 To 10 Step 1
    
    Set stream = fso.CreateTextFile("C:\test\" & Worksheets("Sheet1").Cells(i, "A").Value & ".txt", True)
    stream.WriteLine Worksheets("Sheet1").Cells(i, "A").Value
    stream.WriteLine Worksheets("Sheet1").Cells(i, "B").Value
    stream.WriteLine Worksheets("Sheet1").Cells(i, "C").Value
    stream.WriteLine Worksheets("Sheet1").Cells(1, "F").Value
    stream.Close
    
    Next
    Outcome:
    test1111.jpg

    Any use?

    Steve

  4. Thanks to Steve21 from:

    Stuart_C (11th June 2013)

  5. #4

    Join Date
    Oct 2008
    Location
    Lincolnshire
    Posts
    2,344
    Thank Post
    13
    Thanked 240 Times in 228 Posts
    Rep Power
    70
    There is software that runs on your exchange box that can pull details from AD so it will always be up to date providing AD is and only has to be changed in one place.

    This is it.

  6. #5

    Join Date
    Nov 2007
    Location
    Rotherham
    Posts
    1,679
    Thank Post
    122
    Thanked 126 Times in 102 Posts
    Rep Power
    46
    @Steve21 I'm outputting to a text file, once I save the files in the correct location these are read by the mail server's web interface. That's probably enough to get me started and working. As I'll have everyone's job titles and details in a excel list it's the place I was looking to start from. It's a bit of a pet project so I'll give it a go when I get time, hopefully in the next week or so.
    @MatthewL Cheers for the suggestion but I'm not using Exchange.

  7. #6

    Join Date
    Nov 2007
    Location
    Rotherham
    Posts
    1,679
    Thank Post
    122
    Thanked 126 Times in 102 Posts
    Rep Power
    46
    So thanks to @Steve21 that has worked fantastically for part 1 but I'm struggling with part 2.
    I've used the above commands to create a pure text file, but I need write another that creates a text file with some HTML in.
    So I'm trying th fit the line (for example)
    Code:
    <div style="color: rgb(0, 0, 0); font-family: verdana; font-size: 13.3333px; font-style: normal; font-weight: 400;">
    in one of the stream.writeline commands but it doesn't work.

    I know it's going horribly wrong becuase of the use of speach marks for starters but I was hoping someone had some advice on how to write this into a line on a text file.

  8. #7

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by Stuart_C View Post
    So thanks to @Steve21 that has worked fantastically for part 1 but I'm struggling with part 2.
    I've used the above commands to create a pure text file, but I need write another that creates a text file with some HTML in.
    So I'm trying th fit the line (for example)
    Code:
    <div style="color: rgb(0, 0, 0); font-family: verdana; font-size: 13.3333px; font-style: normal; font-weight: 400;">
    in one of the stream.writeline commands but it doesn't work.

    I know it's going horribly wrong becuase of the use of speach marks for starters but I was hoping someone had some advice on how to write this into a line on a text file.
    You need to escape your speech marks by doubling them up, like so:

    Code:
    <div style=""color: rgb(0, 0, 0); font-family: verdana; font-size: 13.3333px; font-style: normal; font-weight: 400;"">
    It'll only write a single quote to the output, but you have to escape them like this so the compiler knows it's not the end of a string

  9. Thanks to LosOjos from:

    Stuart_C (21st June 2013)

  10. #8

    Join Date
    Nov 2007
    Location
    Rotherham
    Posts
    1,679
    Thank Post
    122
    Thanked 126 Times in 102 Posts
    Rep Power
    46
    That looks good! Cheers
    Just need to do a load of copy and pasting and then test.
    Thanks.



SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2007] Word Field Code Fun
    By Stuart_C in forum Office Software
    Replies: 0
    Last Post: 2nd May 2011, 10:54 AM
  2. Writing to Excel from .vbs script
    By Samson in forum Windows
    Replies: 6
    Last Post: 15th October 2008, 08:34 AM
  3. VBS code to restart a service.
    By FN-GM in forum Scripts
    Replies: 4
    Last Post: 8th October 2008, 10:18 PM
  4. VB code to colour cells in a word table
    By park_bench in forum Coding
    Replies: 4
    Last Post: 30th June 2008, 09:52 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
  •