ADO help with Access button for Excel wb?

[BusMgr]

I am trying to create a button on an Access form that will save the current linked active Excel wb. The first time I want to save it the file name will be changed (using save as), after that I want to do a save or save as to save the updated wb to the same file.

My current code works some of the time (weird) and other times I get Err code 91-(Object variable not set) I've been told that I am not declaring the Excel wb object correctly and that ADO is the answer.

VBA code for button as follows:
Private Sub Command6_Click()
On Error GoTo Error_Command6_Click
Dim Wb As Workbook
Dim AWb As String
AWb = ActiveWorkbook.Name
Dim mypath As String
Dim filebody As String
mypath = "D:\RBI Tools\"
filebody = "Pipetally2"
wellinfo = Forms![Form2]![WellName]
ActiveWorkbook.SaveAs Filename:=mypath & filebody & wellinfo & ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
' Close Excel and clear the object varibles
ActiveWorkbook.Close
Application.Quit
Exit_Command6_Click:
Exit Sub
Error_Command6_Click:
MsgBox CStr(Err) & " " & Err.Description & "
Resume Exit_Command6_Click
End Sub

Thanks in advance.

BusMgr

 

[shaddow]

Are u using this code under Excel? or under Access? ________

George

 

[BusMgr]

It is written under Access for a button on a form to save the linked to Excel wb.

 

[shaddow]

Try to create an object for each
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
and then do what u need to do...
Add to your References on the project Microsoft Excel...
and see if it works... ________

George