seth_belgium said:
The errortext is "Object variable not set or with block variable not set".
What would be really usefull for us is to know what code triggered the error. Can you post them?
The code below is what caused the error 91, i use a button on a form to triggers the code.
Option Compare Database
Option Explicit
Private Sub cmd_Export_to_word__Bulk__Click()
Dim dbs As Database
Dim objDocs As Object
Dim objWord As Object
Dim prps As Object
Dim rst As Recordset
Dim blnSaveNameFail As Boolean
Dim strReportNumber As String
Dim strSampleNumber As String
Dim strNoSamples As String
Dim strSampleLocation As String
Dim strResult As String
Dim strDoc As String
Dim strDocsPath As String
Dim strSaveName As String
Dim strSaveNamePath As String
Dim strShortDate As String
Dim strTemplatePath As String
Dim strTest As String
Dim strTestFile As String
Dim strWordTemplate As String
Dim strMessageTitle As String
Dim strMessage As String
Dim intReturn As Integer
Dim intCount As Integer
Dim dteTodayDate As Date
'Create a word instance to use for the export; uses the existing word
'instance if there is one, otherwise creates a new instances
On Error Resume Next
Set objWord = GetObject(, "word Application")
If Err.Number = 429 Then
'Word is not running; creating a word object
Set objWord = CreateObject("Word.Application")
Err.Clear
'Sets up error handler for the rest of procedure
On Error GoTo cmdWordExport_ClickError
'Run make-table queries to create tables to use for export;
'I use make-table queries instead of select queries, because the
'queries have a criterion limiting the Bulk Number to the one selected
'on the form, and such parameter queries can't be used in a recordset.
'Instead, the make-table queries are run to create tables which will
'be used in the recordsets later in the code.
DoCmd.SetWarnings False
'DoCmd.OpenQuery "qmakExport"
DoCmd.OpenQuery "qmakExportDetails"
'Check that there is at least one detail item before creating Export
intCount = DCount("*", "tmakExportDetails")
Debug.Print "Number of Detail items: " & intCount
If intCount < 1 Then
MsgBox "No detail items for Export; canceling"
Exit Sub
End If
'Create recordset and get needed doc properties for this Export
Set dbs = CurrentDb
'Set rst = dbs.OpenRecordset("tmakExport", dbOpenDynaset)
With rst
'The Nz function is used to convert any Nulls to zeros or
'zero-length strings, to prevent problems with exporting
'to Word
strReportNumber = Nz(![ReportNumber])
strNoSamples = Nz(![NoSamples])
strSampleNumber = Nz(![SampleNumber])
strSampleLocation = Nz(![Location])
strResult = Nz(![Result])
End With
rst.Close
'The paths for Templates and Documents folders are picked up from the
'Windows Registry; they work for Office 97 and Windows 95/98, but may need
'to be changed for Office 2000 or NT (or you can just hard-code the paths)
strDocsPath = objWord.System.PrivateProfileString("", _
"HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders", _
"Personal") & "\"
Debug.Print "Docs path: " & strDocsPath
strTemplatePath = "C:\Documents and Settings\Alastair\Desktop\MergeToTables\Northwind Invoice"
strWordTemplate = strTemplatePath & "Material.dot"
'This date string is used in creating the Exports save name
strShortDate = Format(Date, "m-dd-yyyy")
'This date variable is used to print today's date on the Export
'(unlike a Word date code, it remains stable when the Export is
'reopened later)
dteTodayDate = Date
'Check for existence of template in template folder,
'and exit if not found
strTestFile = Nz(Dir(strWordTemplate))
If strTestFile = "" Then
MsgBox strWordTemplate & " template not found; can't create letter"
Exit Sub
End If
Set objDocs = objWord.Documents
objDocs.Add strWordTemplate
'Write information to Word custom document properties from
'previously created variables
Set prps = objWord.ActiveDocument.CustomDocumentProperties
prps.Item("ReportNumber ").Value = strReportNumber
prps.Item("NoSamples").Value = strNoSamples
prps.Item("SampleNumber").Value = strSampleNumber
prps.Item("SampleLocation").Value = strSampleLocation
prps.Item("Result").Value = strResult
'Highlight the entire Word document and update fields, so the data
'written to the custom doc props is displayed in the DocProperty fields
objWord.Selection.WholeStory
objWord.Selection.Fields.Update
objWord.Selection.HomeKey Unit:=6
objWord.Visible = True
objWord.Activate
'Go to table to fill with Details data
With objWord.Selection
.GoTo What:=wdGoToTable, Which:=wdGoToFirst, Count:=3, Name:=""
.MoveDown Unit:=wdLine, Count:=1
End With
'Set up recordset of linked Details data to put in table on
'Word Export
Set rst = dbs.OpenRecordset("tmakExportDetails", dbOpenDynaset)
'Save Details information to variables
'Use Format function to apply appropriate formatting to
With rst
.MoveFirst
Do While Not .EOF
'Move through the table, writing values from the variables
'to cells in the Word table
With objWord.Selection
.TypeText Text:=strReportNumber
.MoveRight Unit:=wdCell
.TypeText Text:=strNoSamples
.MoveRight Unit:=wdCell
.TypeText Text:=strSampleNumber
.MoveRight Unit:=wdCell
.TypeText Text:=strSampleLocation
.MoveRight Unit:=wdCell
.TypeText Text:=strResult
.MoveRight Unit:=wdCell
End With
.MoveNext
Loop
.Close
End With
dbs.Close
'Delete last, empty row
Selection.SelectRow
Selection.Rows.Delete
'Check for existence of previously saved letter in documents folder,
'and append an incremented number to save name if found
strSaveName = "Export to " & " for Export " _
& strReportNumber & " on " & strShortDate & ".doc"
intCount = 2
blnSaveNameFail = True
Do While blnSaveNameFail
strSaveNamePath = strDocsPath & strSaveName
Debug.Print "Proposed save name and path: " _
& vbCrLf & strSaveNamePath
strTestFile = Nz(Dir(strSaveNamePath))
If strTestFile = strSaveName Then
'Create new save name with incremented number
blnSaveNameFail = True
strSaveName = "Export " & CStr(intCount) & " to " & " for Order " & strReportNumber & " on " & strShortDate & ".doc"
strSaveNamePath = strDocsPath & strSaveName
intCount = intCount + 1
Else
blnSaveNameFail = False
End If
Loop
'Ask whether user wants to save the document
'If you prefer, you could eliminate the prompt and just
'save the document with the save name automatically.
strMessageTitle = "Save document?"
strMessage = "Save this document as " & strSaveName
intReturn = MsgBox(strMessage, vbYesNoCancel + _
vbQuestion + vbDefaultButton1, strMessageTitle)
If intReturn = vbNo Then
objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
GoTo cmdWordExport_ClickExit
ElseIf intReturn = vbYes Then
objWord.ActiveDocument.SaveAs strSaveNamePath
ElseIf intReturn = vbCancel Then
GoTo cmdWordExport_ClickExit
End If
cmdWordExport_ClickExit:
'Close any open recordset or database, in case code stops because
'of an error
On Error Resume Next
rst.Close
On Error Resume Next
dbs.Close
Exit Sub
cmdWordExport_ClickError:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume cmdWordExport_ClickExit
End If
End Sub
Any help would be greatefully received.
Regards
Alastair
- an idea of which line is causing the problem.
