MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Exporting Excel Data to Word & saving as an automated file name

  Asked By: Jayden    Date: Oct 01    Category: MS Office    Views: 2826

I currently have an excel spreadsheet that exports data to word and
saves it as a word document using a value in an excel field as a
file name.

1. How do I change the code so that it always incorporates the
word 'Number' and a space before the number in the cell as the file

eg If the value in A1 is '123456' then the Word file name should
be 'Number 123456'

Current code is below

If ActiveSheet.Range("a1").Value = "" Then
MsgBox "Please enter the ID"
'Stop Processing
Exit Sub
strNewFileName = ActiveSheet.Range("a1").Value
End If

2. I get a runtime error when word is automatically closed down. How
do a put a check in the code for the runtime error, so that if it
does happen the error will be automatically closed which will enable
word to be closed down.

The current code is detailed below

appWord.ChangeFileOpenDirectory "c:\Ed\saved numbers"
appWord.ActiveDocument.SaveAs strNewFileName, wdFormatDocument


I am using Word97 and Excel 97



2 Answers Found

Answer #1    Answered By: Fjodor Bonkob     Answered On: Oct 01

I would change

strNewFileName = ActiveSheet.Range("a1").Value


strNewFileName = "Number " & ActiveSheet.Range("a1").Value

Question 2 (you really should put your separate questions into separate
emails. Many times you will only get a partial answer or if someone
does not know the answer to both questions they will not answer either

Add Error Handling to your code  to catch the error. Something like:

On Error GoTo EH

'Place procedure steps here.

'Handle Errors Gracefully
Exit Sub


Select Case Err.Number
Case 91 'Or whatever error you are getting
On Error Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume Exit_EH
End Select

Answer #2    Answered By: Faizah Khan     Answered On: Oct 01

For the Question 1;

I prefer Val() Function

if val(Range("A1").value) = 0 then ' if it consists a text (not a number), val
returns 0
'This is not a number
strNewFileName = Range("A1").value
'This is a number
strNewFileName = "Number" & Range("A1").Value
end if

But remember if Range("A1") consists a number  and the number is 0,then the "IF
STRUCTURE" will act like that it is a text. I mean it won't make strNewFileName
= "Number 0". You can make a little special solution for this. For example you
can control it like this:

if val(Range("A1").value) = 0 and not Range("A1").text = "0"
strNewFileName = Range("A1").value
strNewFileName = "Number" & Range("A1").Value
end if

So it fixes the 0(zero) problem. This If Structure will always put "Number" in
front of it, if the value is a number.