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

[portella]

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.

[Tinbendr]

At the top add:
Dim aCell As RangeJust after
'build a new sequentially-numbered file name add
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"
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.

[fumei]

If you are going to use aCell as a Range object, it is better to be explicit and use .Text.
strNewFileName = aCell.Text & ".doc"


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

Function CellText2(strIn As String)
CellText2 = Left(strIn, Len(strIn) - 2)
End Function



Version B: this uses the cell itself, as an object

Function CellText(oCell As Cell)
CellText = Left(oCell.Range.Text, _
Len(oCell.Range.Text) - 2)
End Function


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.

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
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.

[portella]

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

[fumei]

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.

[portella]

Thanks Gerry. I will make good use of your advice. It's not very common to be invited to steal, is it? ;-)

[fumei]

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.