Close Excel via VBA

[scorpio1769]

Hi folks. First off, I have not idea what I'm doing so be gentle.

I have the following code that takes an excell template, fills in some data, saves the updated spreadsheet using SaveAs. Everything is kosher for that, however it seems that I cannot close the template file ".....RSP Purchase Order Form 081307.xls"

I'm extremely new to this and can't seem to close the template properly. Can someone give me some guidance?

Thank you much.

Option Compare Database

Private Sub ExportDishExcel()

Dim wrk As DAO.Workspace
Dim dbconn As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Set wrk = DAO.CreateWorkspace("myworkspace", "admin", "")
Set dbconn = wrk.OpenDatabase("\\Sheela-na-gig\company share\Databases\Sat Man.mdb")
Set rs = dbconn.OpenRecordset("Select * from [InventoryOrderExcelOuput]")


Dim excelapp As New Excel.Application
Dim excelfile As New Excel.Workbook
Dim excelsheet As New Excel.Worksheet
Dim savefilepath As String
Dim msgoption As Long


Set excelfile = excelapp.Workbooks.Open("\\Sheela-na-gig\company share\Databases\Dish Orders\Template\RSP Purchase Order Form 081307.xls")
Set excelsheet = excelfile.Worksheets(1)


rs.MoveFirst
savefilepath = rs.Fields("poparse") & ".xls"
While Not rs.EOF
excelsheet.Cells(3, 1) = "DEALER NAME: Satellite Country"
excelsheet.Cells(3, 4) = "DATE: " & CStr(rs.Fields("date"))
excelsheet.Cells(3, 5) = "TIME: " & CStr(rs.Fields("time"))
excelsheet.Cells(5, 1) = "DEALER #: 459522"
excelsheet.Cells(5, 2) = "CONTACT: Robert"
excelsheet.Cells(5, 4) = " PHONE #: 866-914-3474, Option 2"
excelsheet.Cells(5, 7) = "PO #: " & rs.Fields("PONumber")
excelsheet.Cells(58, 1) = rs.Fields("notes")
excelsheet.Cells(rs.Fields("lcellloc"), rs.Fields("rcelloc")) = rs.Fields("quantity")

rs.MoveNext

Wend

excelfile.SaveAs "\\Sheela-na-gig\company share\Databases\Dish Orders\" & savefilepath
'excelfile.Close
excelapp.ActiveWorkbook.Close True, "\\Sheela-na-gig\company share\Databases\Dish Orders" & savefilepath

'excelfile.Close
'Workbooks("\\Sheela-na-gig\company share\Databases\Dish Orders" & savefilepath).Close SaveChanges:=False


'excelapp.ActiveWorkbook.Close False, "\\Sheela-na-gig\company share\Databases\Dish Orders\Template\RSP Purchase Order Form 081307.xls"
excelapp.Quit

Set excelsheet = Nothing
Set excelfile = Nothing
Set excelapp = Nothing
msgoption = MsgBox("Done")

End Sub

 

[SkipVought]

Hi,

You must SET the Excel Application Object

    Dim msgoption As Long[b]
    
    Set excelapp = CreateObject("Excel.Application")[/b]
    
    Set excelfile = excelapp.Workbooks.Open("\\Sheela-na-gig\company share\Databases\DishOrders\Template\RSP Purchase Order Form 081307.xls")

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[JerryKlmns]

Skip,

scorpio1769 uses the so unrecommended
Dim excelapp As New Excel.Application

Also

Dim excelfile As New Excel.Workbook
creates a new empty excel workbook and then

Set excelfile = excelapp.Workbooks.Open ....

assigns a newly opened excel workbook to this variable

So scorpio1769, don't use Dim ... As New ... any more and here is why faq222-6008 (Ok it is VB but applies to VBA too!)

 

[scorpio1769]

Thank you much everyone