Consulting

Results 1 to 7 of 7

Thread: How can I use the content of a table cell as the file name when saving?

  1. #1
    VBAX Newbie
    Joined
    May 2010
    Posts
    3
    Location

    How can I use the content of a table cell as the file name when saving?

    Hello,

    VBA is really new to me. I'm using the macro posted on this website to split a merged document with multiple records into single-paged documents (sorry I'm not allowed to post links yet - the post is called "Split document into multiple single-paged documents"). This macro creates sequentially-numbered files based upon the original file name but I actually need the file names to use the content of a table cell in my file (basically a company name).
    I assume I only need to modify a few lines on the macro but I am not advanced enough to do this. I'm using Office 2007 but need to generate files in Office 2003 format.

    Thanks in advance for your help.

  2. #2
    VBAX Expert Tinbendr's Avatar
    Joined
    Jun 2005
    Location
    North Central Mississippi (The Pines)
    Posts
    993
    Location
    At the top add:
    [vba]Dim aCell As Range[/vba]Just after
    'build a new sequentially-numbered file name
    add
    [vba]With docSingle
    'Get the cell contents in a range
    Set aCell = .Tables(1).Cell(1, 1).Range
    'Strip off the end-of-cell marker
    aCell.MoveEnd wdCharacter, -1
    End With
    'Then replace the next line with this.
    strNewFileName = aCell & ".doc"
    [/vba]This do not account for any error handling, such as commas in Company names.

    This also assumes the Company Name is always in Cell 1,1 in Table one.

    David


  3. #3
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    If you are going to use aCell as a Range object, it is better to be explicit and use .Text.[vba]
    strNewFileName = aCell.Text & ".doc"
    [/vba]

    BTW, it is a good idea to have a Function that gets cell text (stripping the end-of-cell marker). After all, getting text from a table cell is a fairly common thing to do. That way (assuming you have it in a global template), any time you need to get cell text, you can use the Function.

    Version A: this uses the string of the cell range
    [vba]
    Function CellText2(strIn As String)
    CellText2 = Left(strIn, Len(strIn) - 2)
    End Function
    [/vba]


    Version B: this uses the cell itself, as an object
    [vba]
    Function CellText(oCell As Cell)
    CellText = Left(oCell.Range.Text, _
    Len(oCell.Range.Text) - 2)
    End Function
    [/vba]

    using table.cell with Cell version
    strNewFileName = CellText(tableObject.Cell(x,y))

    using table.range.cells with Cell version
    strNewFileName = CellText(tableObject.Range.Cells(4))

    OR

    using table.cell with String version
    strNewFileName = CellText2(tableObject.Cell(x,y)).Range.Text

    using table.range.cells with String version
    strNewFileName = CellText2(tableObject.Range.Cells(4)).Range.Text

    Which one to use depends on whether there is an advantage to have access to the cell object properties, or not.

    If you just need the text, then using the string route is probably the way to go.

    But.....

    Say you want to get the string (text) of the cell, AND do something to the cell - highlight it as actioned; bold the text; change the borders....

    THEN, using the cell object itself makes sense.
    [vba]
    Sub TextAndChange()
    Dim oTable As Table
    Dim oCell As Cell
    Set oTable = ActiveDocument.Tables(1)
    Set oCell = oTable.Cell(2, 2)
    With oTable
    .Cell(3, 1).Range.Text = oCell.Range.Text
    With oCell
    MsgBox CellText(oCell)
    .Range.Font.Size = 16
    .Range.Font.Bold = True
    End With
    End With
    End Sub
    [/vba]This copies the text of Cell(2,2) into Cell(3,1) - keeping its original font attributes; displays a message of the text of Cell(2,2), and then changes the text to a different font/size/bold.

    In the example given by the OP, you could:

    highlight each cell used for a filename
    build a list of filenames used and put that in...another table? another document? Whatever.

  4. #4
    VBAX Newbie
    Joined
    May 2010
    Posts
    3
    Location

    Smile

    Thanks a lot for the quick replies

    I tried the first option as I needed to fix my issue quickly but I will definitely use the function as well with other forms I'm currently working on.

    Thanks again!
    Fred

  5. #5
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    Oh yes. As I stated, getting just the text out of table cells is a fairly common task.

    This is the whole point of building up your own library of useful Functions.

    For example, I have in my global template (VBAXpress.dot) a code module of just string functions (modStringFunctions).

    Const dhcDelimiters As String = " ,.!:;<>?"
    (the common non-alphnumeric characters used for delimiters)

    dhExtractString - Pull tokens out of a delimited list.

    dhExtractCollection - Return a collection containing all the tokens contained in a String, using the supplied delimiters

    dhCountIn - Determine the number of times strFind appears in strText

    dhTrimAll - Remove leading and trailing white space, and reduce any amount of internal white space (including tab characters) to a single space.

    dhCountWords - Return the number of words in a string

    dhFirstWord - (Optional ByRef strRest As String) Retrieve the first word of a string. Fill strRest with the rest of the strin

    dhLastWord - same as above except gets last word

    dhTranslate - various ByVal parameters Take a list of characters in strMapIn, match them one-to-one in strMapOut, and perform a character replacement in strIn.

    dhIsCharAlpha - Is the first character of strText an alphabetic character?

    dhIsCharAlphaNumeric - Is the first character of strText an alphanumeric character?

    dhTrimNull - Find the first vbNullChar in a string, and return everything prior to that character. Useful when combined with the Windows API function calls.

    dhSoundex - Create a Soundex lookup string for the input text.

    dhOrdinal - Given an integer, return a string representing the ordinal value.

    dhRoman - Convert a decimal number between 1 and 3999 into a Roman number.

    dhXORText - Encrypt or decrypt a string using the XOR operator.

    the table cell text functions and other stuff...on and on, yadda yadda yadda.

    This is part of the huge opportunity in using these types of forums. Yes, you can possibly tag threads to go back and look at, but the REAL advantage is stealing code, and building your own library of Functions and Subs.

    Like that CellText function. Getting text from table cells is a very common thing to do. The function(s) work, and you will use them.

  6. #6
    VBAX Newbie
    Joined
    May 2010
    Posts
    3
    Location
    Thanks Gerry. I will make good use of your advice. It's not very common to be invited to steal, is it? ;-)

  7. #7
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    It is not stealing if people post actual code. It the reason we do post, so that other can use things. You notice I did NOT post code for all the functions I mention. However, the Celltext function is so useful, so commonly needed, that there are multiple versions flying around. There is no point in not posting it.

Posting Permissions

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