Dynamic Filename Problem

[Nick Transier]

Why does this line give me an error?
DocC.SaveAs "C:\" & Selection.Range.Text

I have tried everything, but I cannot figure out why this doesn't work. The
Selection.Range.Text does not include any illegal characters for saving.

Thanks- Nick

 

[Stephanie Krieger]

Hi, Nick,

As long as the DocC variable is defined correctly -- the
only thing that could cause an error is either lack of
access to your C root folder or something illegal in your
selection. Despite that there appear to be no illegal
characters in your selection, have you considered non-
printing characters which might not be visible on your
screen?

Could there be a paragraph mark, cell marker, tab
character, or other non-printing character (Word calls
these formatting marks) included in your selection? Those
will cause an error as well as text characters.

If you aren't viewing formatting marks, they can still be
included in the selection. To view them, either click the
paragraph mark icon on the standard toolbar, or turn them
on from Tools, Options, View.

Hope that helps,

Stephanie

 

[Nick Transier]

Stephanie,

Thanks for the help. I have thought about that, but my understanding of VBA
is that if you make a Selection.Range.Text it will not include formatting or
formatting marks while Selection.Range.FormattedText will inlude those
things. Either way, using Selection.Range.Text I am getting some strange
characters when I dump the selection into excel (little square boxes). Is
there a way to parse these out?

Thanks- Nick

 

[Stephanie Krieger]

Hi, Nick,

Those boxes are the non-printing characters.

FormattedText will take the text with its formatting
(i.e., character formatting like the font style, font
color (and, if a paragraph mark is included in the
selection, paragraph formatting like line spacing,
indents, etc., as well)) -- but either property will
include the formatting marks likes paragraph mark, cell
marker, tab character, etc. Those things aren't
considered formatting - they are actually seen as
characters.

You can remove them once pasted very easily with a
replace in Excel to remove Chr(13) -- which is the code
for paragraph mark.

If you're dumping into Excel, then I'm guessing you've
already set up the objects and references you need to
control Excel from Word VBA? If so, you can just add this
little replace code after the dump paste:

Cells.Replace What:=Chr(13), Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, ReplaceFormat:=False

That should do it.

Hope it helps!

Stephanie

 

[Nick Transier]

Stephanie,

I have setup my excel object as such:
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

and here is an example of how I paste into cells
ExcelSheet.Application.Cells(iExcelRow, iExcelColumn).Value = iHeaderLevel

However, your code will not work in my VBA macro as written. I tried to edit
it, but I am having lots of errors regarding the ":=" expression and the
commas are throwing errors as well. Please help!

Thansk- Nick

I am

 

[Stephanie Krieger]

Hi, Nick,

Probably just a minor syntax issue, but it's not the :=
character combo. That's standard across VBA for defining
terms for methods such as replace (usually an alternative
to enclosing terms within parens, separated by commas).

Select a line of text in Word with the paragraph mark
included in your selection, and try running this little
macro as is. Hopefully, that will help you find the issue
(I'd need to see exactly how you set up the code in your
macro to be able to tell you -- thought this would get a
faster answer for you than going back and forth again):
______________________________________

Sub testreplace()

Dim MyEx As Object
Set MyEx = CreateObject("Excel.Application")

MyEx.Visible = True
MyEx.Workbooks.Add
MyEx.ActiveWorkbook.ActiveSheet.Range("C7").Select
MyEx.ActiveCell.FormulaR1C1 = Selection.Range.Text
MyEx.Cells.Replace What:=Chr(13), Replacement:="",
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, ReplaceFormat:=False
MyEx.ActiveWorkbook.ActiveSheet.Range("C8").Select

End Sub
____________________

.... be sure you've made the Excel object model available
in Tools, References for whatever module you paste the
sample macro into.

I hope that's helpful.

Best,
Stephanie

-----Original Message-----
Stephanie,

I have setup my excel object as such:
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

and here is an example of how I paste into cells
ExcelSheet.Application.Cells(iExcelRow,

iExcelColumn).Value = iHeaderLevel

 

[Nick Transier]

Stephanie,

Sorry, I am showing me VBA noob-ness. I just needed to add underscores to
the end of each line to let VBA know that it was effectively one long
statement. I will try that and see if I can clear the Excel sheet now. The
subroutine that you wrote does work. I still wish there was a way to paste
it in without that problem - I feel like this is kinda a hack fix. I guess
that's VBA.

Nick



When I got to compile with you

 

[Stephanie Krieger]

Hi, Nick,

I'm glad that worked for you. There probably are other
options ... but if it works just with one extra line of
code, that can't be too bad -- right? ;-)

If you want to look at other alternatives, feel free to
send me the document and code your working with and I'll
see if I can make any other suggestions (email: MODD_2003
at msn dot com). Otherwise -- at least you have a
reasonably clean solution, even if it's imperfect

All the best,
Stephanie