Prev: Posting and Email Notificatio
Next: Public Const strConstantBlue As String = "RGB(79, 129, 189)"throwing error messages
From: Scrunge on 23 Jan 2010 05:46
I need to save a word Document on a tool bar button
The SaveAs name needs to be different each time ( a quote number, on the
document). I have managed to do this in Excel as shown below

SaveAs Filename:=
"\\Quotes\Quote No" & Range("Quote_Number") & ".xls",

How can I do the same in word. Do I reference the excel spreadsheet that the
quote number is linked to. If so how or do I perhaps bookmark the quote
number from the document?

Any advise would be apprieciated
From: Graham Mayor on 23 Jan 2010 06:43
Based on code you will find on the mvp web site. The following should work,
saving the active document with the name derived from the named range
"Quote_Number" in the Excel workbook defined in the macro.
http://www.gmayor.com/installing_macro.htm

Sub SaveUsingExcelData()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String
Dim sDocumentPath As String
Dim oDoc As Document
Dim sText As String
Set oDoc = ActiveDocument
'specify the workbook to work on
WorkbookToWorkOn = "D:\My Documents\quote.xls"
'Specify the path to save the document
sDocumentPath = "D:\My Documents\"
'If Excel is running, get a handle on it; otherwise start a new instance of
Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
If Err Then
ExcelWasNotRunning = True
Set oXL = CreateObject("Excel.Application")
End If
On Error GoTo Err_Handler
'If you want Excel to be visible, you could add the line:
'oXL.Visible = True 'here; but your code will run faster if you don't make
it visible
'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)
With oXL.ActiveWorkbook.Worksheets(1)
If oXL.Range("Quote_Number") <> "" Then
sText = oXL.Range("Quote_Number")
'MsgBox sText
ActiveDocument.SaveAs sDocumentPath & "Quote No " & sText
End If
End With
If ExcelWasNotRunning Then
oXL.Quit
End If
'Make sure you release object references.
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing
'quit
Exit Sub
Err_Handler:
MsgBox WorkbookToWorkOn & " caused a problem. " & Err.Description,
vbCritical, _
"Error: " & Err.Number
If ExcelWasNotRunning Then
oXL.Quit
End If
End Sub

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>


"Scrunge" <Scrunge(a)discussions.microsoft.com> wrote in message
news:7DE968B0-D9ED-415A-B9E5-480E01C12B38(a)microsoft.com...
>I need to save a word Document on a tool bar button
> The SaveAs name needs to be different each time ( a quote number, on the
> document). I have managed to do this in Excel as shown below
>
> SaveAs Filename:=
> "\\Quotes\Quote No" & Range("Quote_Number") & ".xls",
>
> How can I do the same in word. Do I reference the excel spreadsheet that
> the
> quote number is linked to. If so how or do I perhaps bookmark the quote
> number from the document?
>
> Any advise would be apprieciated


 | 
Pages: 1
Prev: Posting and Email Notificatio
Next: Public Const strConstantBlue As String = "RGB(79, 129, 189)"throwing error messages