Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    61
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Accessing Excel spreadsheet from Word and finding right column

    Man oh man, this one's giving me grey hairs.......

    I have set up an Excel spreadsheet that contains branch names in row 1, and the column under each branch name contains the branch details. (Hopefully I've attached it right)
    Branch Addresses.xlsx I know which branch the user works at and I have worked out how to access the Excel spreadsheet from my Word template, but I haven't got past using coordinates (e.g. 5,2) to access a cell.

    I want to be able to add new branches easily, so I want to make my code flexible (i.e. keep checking until I hit a blank branch name. I realise this means I'll have to insert something in A1.)

    I'd like to access the spreadsheet and then work across Row 1, checking each branch name to see if it matches the user's branch name. If it does, I want to select the required information from the column. (The first row contains address, the next postal address, then telephone, etc.) My first column contains a description of the row contents so ideally once I've located the correct branch column, I'd like to then check the first column to find the row that contains "Postal Address". Hopefully this would future proof it in case someone decides to insert a new row into the Excel spreadsheet.

    I'm sure it's easy peasy to do, but blowed if I can work it out.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Nikki,

    What I don't understand is why each branch doesn't have it own set of templates. This would be far easier to maintain than trying to get the data from an Excel workbook every time a document is created. At the most, all you'd need is an INCLUDETEXT field in each template pointing to a document that holds the formatted branch data and, perhaps, a macro to unlink that field in the new document (so that later changes to the branch details don't affect earlier documents).

    FWIW, your workbook is actually structured the opposite of how one would normally do this. Ordinarily, the branches would be in rows, with the particulars in the columns. That way, you can use mailmerge, for example, to generate letters, etc. Not that having it the way you do makes it any harder to process with vba.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    61
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I am totally open to ideas Paul, so I can easily swap my spreadsheet around. Originally we had a set of templates for each branch - so 4 memo templates and 4 letter templates, but I thought it would be easier to manage one template. I'll do some research on INLCUDETEXT. I can format the branch details, but some of the fields will be inserted into different places within documents, and there won't always be a standard format. E.g. the branch details are displayed quite differently in the Letter template than they are in the Memo template.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    With INCLUDETEXT fields, if you bookmark a range in the source file, you can reference the bookmark so as to import only the bookmarked text. This gives you the ability to re-arrange the data for the different letter, memo, etc. templates.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Niki

    I think your approach is sound and it is better to maintain one template rather than building a series of them for each location.<br><br>As per Paul's recommendation, I would also start by transposing the data in your spreadsheet so the locations go down column A and the categories go across the first row. This gives you more flexibility and allows easier coding. If you did this (as per the sample I attached) then the following code could be adapted to work for your situation.

    Note that this code sample is populating a listbox in a userform which you would need to construct first unless you were intending to obtain the current user's location some other way.
    Code:
    Private Sub UserForm_Initialize()'NOTE         : Requires a reference to "Microsoft ActiveX Data Objects 2.x Library"
    '               (Developed with reference to version 2.8 of the above)
        Dim cnt As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        Dim sPath As String, strSQL As String, clTrgt As Range
        
        sPath = ActiveDocument.AttachedTemplate.Path & "\BranchAddresses.xlsx"
        strSQL = "Select * from [Sheet1$]"
        sConn = "Data Source =" & sPath & "; Extended Properties =""Excel 12.0 Xml;HDR=YES"";"
        
        With cnt
          .Provider = "Microsoft.ACE.OLEDB.12.0"
          .ConnectionString = sConn
          .Open
        End With
        rst.Open strSQL, cnt, adOpenStatic
    
    
        'Count the number of fields and records
        Debug.Print "No. of fields: " & rst.Fields.Count
        rst.MoveLast
        Debug.Print "Recordcount: " & rst.RecordCount
        
        rst.MoveFirst
        Do While rst.EOF = False
          With ctlList
            .AddItem rst!Location  'adds location entry to userform listbox
            Debug.Print rst!StreetAddress
          End With
          rst.MoveNext
        Loop
            
    EarlyExit:
      'Close and release the ADO objects
      rst.Close
      cnt.Close
      Set rst = Nothing
      Set cnt = Nothing
    End Sub
    Attached Files Attached Files
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #6
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    61
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi Andrew. Thanks for this. While I'm finding this quite rewarding, it's also extremely overwhelming so it's great to have some help.

    I do indeed want to populate a listbox in a userform (I was going to worry about that once I'd sussed out how to access the Excel spreadsheet so you've killed two birds with one stone).

    Because I'm a complete novice (with only basic experience with a couple of templates, and a course you ran in Auckland last year under my belt), can I confirm how to use this code?

    Firstly, I tried to set up a ListBox on my UserForm and all I got was a square box. When I select ComboBox I got a blank drop down list and an arrow. I can see a RowSource property for both - do I need to put something in here for the code to work?

    On to the code...
    Most of it doesn't make sense and I can handle that, but the following code:
    With ctlList
    .AddItem rst!Location 'adds location entry to userform listbox
    Debug.Print rst!StreetAddress

    Seems to be where I need to 'personalise' it. If this code inserts a value into a ListBox, why is it using StreetAddress (or is that a typo)?

    When I run the code, I get "run-time error '424' Object required". When I hover the cursor over that line, it says rst!Location = "Auckland" so we must be doing something right!

    Also, the spreadsheet seems to be 'locked' now since I ran the code. Is this because it wasn't getting to the EarlyExit statement?

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    See the attached for a demonstration of another approach you could take. There are two files:
    Branches.docx, a document that contains all the branch details in a table. Each table cell is bookmarked.
    Letterhead.dotm, a macro-enabled template with a Document_New macro that inserts the branch details for a given branch into the header of any new document based on the template, in the format & layout you might want.
    As supplied, any document you create from the Letterhead.dotm template will display the details for Auckland. To have the letterhead supply the details for another branch, simply access the template's 'Office' property (File|Info|Properties|Advanced > Custom) and change it to, say, Wellington, then save & close the template.

    Should the details for a branch change, or a branch be added/closed, simply update the table & bookmarks in Branches.docx.

    To use any of the details for, say, a memo template, simply make a copy of Letterhead.dotm and re-arrange the fields in the header.

    All you need to do is to keep the Branches.docx file in the same folder as the templates that reference it.
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    61
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi Paul. Thanks for your reply. I can see what you've done and it is simplier, but I can see it all falling apart when someone enters a new branch in the branch document and doesn't add the matching bookmarks.

    While I don't understand Andrew's code properly, and I haven't got it working correctly yet, I think it'll be easier to maintain long-term.

    Regards and thanks
    Niki

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi niki,
    I can see it all falling apart when someone enters a new branch in the branch document and doesn't add the matching bookmarks.
    Surely this kind of updating would be done in one location only and tested before being released into production???

    In any event, maintaining the branch data in a Word document imposes far fewer overheads than maintaining the data in any other application.

    See if the attached better suits your needs - it's fully macro-driven and doesn't rely on bookmarks in the Branches.docx file.
    Attached Files Attached Files
    Last edited by macropod; 2012-08-08 at 02:45. Reason: Revised attachment - more efficient code
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Niki - good to hear from you again.

    A list box is normally quite tall, it kind of acts like a spreadsheet so you need to see multiple rows (and possibly also columns). It is populated in the same way as a combobox but it just depends on how you want the userform to look. The listbox has an added benefit of letting you have multiple selections which you can't do with a combobox.

    The code bypasses the need to put anything in the RowSource property. This property is used when the entries are static.

    The rst!Location and rst!StreetAddress entries are the field names and should correspond with the titles in the first row in Excel. You will note that I took the spaces out of the cells because the field name needs to be a single word.

    The error may be that your combobox is not named ctlList but I might need to see the template to work it out.

    The code does open the spreadsheet to read it. If the code doesn't run to completion, the spreadsheet remains open and you will need to get it to finish (or use task manager) to close the spreadsheet again.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  11. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    Niki B (2012-08-13)

  12. #11
    Star Lounger
    Join Date
    Oct 2011
    Location
    Auckland. New Zealand
    Posts
    61
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks for the responses. Once I renamed the ComboBox, the functionality worked perfectly and I had the confidence to do things like default the combobox when needed. Interestingly I have another task that will require the entry of more than one selection so I'll have to try out the Listbox then.

    VBA is challenging but boy you feel good when it's working (and you understand how)!

Posting Permissions

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