Ok I have the following code, but I can't work out why it doesn't like the
set CoDoc = Sheets("EntryForm").Range(irow, 5)
every time I get to here i have the following error
application-defined or object defined erro - Run-time error '1004'
overview of the macro:
picks up site information and codes, fills it into relevant standard word forms, prints these off, saves it, and then goes to the next company form in the list and repeats.
set CoDoc = Sheets("EntryForm").Range(irow, 5)
every time I get to here i have the following error
application-defined or object defined erro - Run-time error '1004'
overview of the macro:
picks up site information and codes, fills it into relevant standard word forms, prints these off, saves it, and then goes to the next company form in the list and repeats.
Code:
Sub Word1()
Dim WordApp As Object
Dim Data As Range, SiteID As String
Dim CoDocument As String
Dim Company As String
Dim Records As Integer, i As Integer
Dim Region As String, SalesAmt As String, SalesNum As String
Dim SaveAsName As String
Dim TodayDate As String
Dim Author As String
Dim Value As String
Dim irow As Integer
' <<<< LOOP SETUP >>>>
irow = 5
While Not IsEmpty(Cells(irow, 1))
' Opens an instance of MS Word and then opens the document Below to be confirgured
Set Data = Sheets("EntryForm").Range("A1")
SiteName = Sheets("EntryForm").Range("B1")
SiteID = Sheets("EntryForm").Range("B2")
Value = Sheets("EntryForm").Range("B3")
TodayDate = Format(Date, "mmmm d, yyyy")
CoDoc = Sheets("EntryForm").Range(irow, 5)
Company = Sheets("EntryForm").Range(irow, 1)
Author = "David Armstrong"
SaveAsName = ThisWorkbook.Path & "\" & SiteName & " - " & Company & ".doc"
' <<<<<<<<<<<<<<START OF LOOP>>>>>>>>>>>>>>>>>>>>>
ActiveWindow.Visible = False
Windows("Form test.xls").Activate
Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True
'TBA EDIT THIS for release version
WDoc = ThisWorkbook.Path & "\" & CoDoc & ".doc"
WordApp.Documents.Open WDoc
' Paste from Excel Variables into Words Bookmarks
With WordApp
If .ActiveDocument.Bookmarks.Exists("Sitetext") Then
.ActiveDocument.Bookmarks("Sitetext").Range.Text = SiteName
End If
If .ActiveDocument.Bookmarks.Exists("reftext") Then
.ActiveDocument.Bookmarks("reftext").Range.Text = SiteID
End If
If .ActiveDocument.Bookmarks.Exists("createdate") Then
.ActiveDocument.Bookmarks("createdate").Range.Text = TodayDate
End If
If .ActiveDocument.Bookmarks.Exists("value") Then
.ActiveDocument.Bookmarks("value").Range.Text = SiteID
End If
If .ActiveDocument.Bookmarks.Exists("authortext") Then
.ActiveDocument.Bookmarks("authortext").Range.Text = SiteID
End If
.ActiveDocument.SaveAs Filename:=SaveAsName
' Print and close
.ActiveDocument.PrintOut
.ActiveDocument.Quit
End With
Set WordDoc = Nothing
Set WordApp = Nothing
Range(irow, 3).Select
ActiveCell.FormulaR1C1 = TodayDate
irow = irow + 1
Wend
' <<<<<<<<<<<<<<<End of loop>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
End Sub