Error 91- Object variable or with block variable not set

[eleteroboltz]

Hey guys, I'm having a little trouble with my code here. The first step of the loop, the program runs fine, but in the second step of the loop, the compiler shows the message: Error 91 "Object variable or with block variable not set" on the lines:

With Template
ActiveWorkbook.SaveAs "C:\PATEC\" & pasta & MyRS!TAG & ".xls"
End With

I really don't know what is going wrong. I tried to do every thing but until now nothing seems to work.

The program basically opens the template.xls and another sheet, populates the template.xls with some data, saves the template.xls with another path and name, closes all excel applications opened and in the end it goes to the next record.

Please, can any one help me with this problem???
Thanks in advance


Option Compare Database
Public Declare Function PathFileExists Lib "shlwapi.dll" Alias "PathFileExistsA" (ByVal pszPath As String) As Long
'--------------------------------------------------------

Public Sub AtualizarTemplatePATEC()

Dim Unidade As Integer
Dim MyDB As Database
Dim MyRS As Recordset
Dim TAG As String
Dim pasta As String
Dim iResponce As Integer
Dim teste As Boolean
Dim mySheet2 As Excel.Application
Dim PATEC As Object
Dim Template As Object
Dim appExcel As Object

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("pumps")
MyRS.MoveFirst

Do While Not MyRS.eof

Past = "pumps"

If ArqExiste("C:\PATEC\" & pasta & MyRS!TAG & ".xls") Then

Set Template = CreateObject("Excel.Application")
Template.Workbooks.Open ("C:\template.xls") 'endereço
'Template.Visible = True

Set PATEC = CreateObject("Excel.Application")
PATEC.Workbooks.Open ("C:\PATEC\" & pasta & MyRS!TAG & ".xls") 'endereço
'PATEC.Visible = True


Template.Range("B" & 1).Value = MyRS!TAG
Template.Range("H" & 11).Value = MyRS!Operacao

Template.Range("B" & 2 & ":" & "D" & 14).Value = PATEC.Range("B" & 2 & ":" & "D" & 14).Value

Template.Range("H" & 2).Value = PATEC.Range("H" & 2).Value


With Template
ActiveWorkbook.SaveAs "C:\PATEC\" & pasta & MyRS!TAG & ".xls"
End With

PATEC.Workbooks.Close
Template.Workbooks.Close
Excel.Application.Quit

Set PATEC = Nothing
Set Template = Nothing

End If

MyRS.MoveNext
Loop
End Sub
'------------------------------------------------------
Public Function ArqExiste(ByVal Arquivo As String) As Boolean

ArqExiste = (PathFileExists(Arquivo) = 1)

End Function

 

[TheAceMan1]

How are ya eleteroboltz . . .

Could it be the missing dot in red [red].[/red] shown below:

[blue]   With Template
   [red][b].[/b][/red]ActiveWorkbook.SaveAs "C:\PATEC\" & pasta & MyRS!TAG & ".xls"
   End With[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[eleteroboltz]

TheAceMan1,
Thank you very much....
The code is running great now.

 

[JoeAtWork]

Use Option Explicit so that such simple errors are automatically caught.



Joe Schwarz
Custom Software Developer

http://www.plandev.net