Saving Excell Document from Access 1

[jedel]

Hi all,

I'm working on a database that exports data from a table in Access to an Excell spreadsheet in columns.

Everything is going smoothly, but now I have an issue with saving the excell templates as an XLS file and in the location that I want to.

here is the line that I'm trying to use to save the document:

ActiveWorkbook.SaveAs FileName:=Me.cboMonth & "Roster" & Format(Date, "yyyy")

It basically should save the file as "monthRoster2008.xls" and I want to save it in the save location as the front end database.

ANy help would be appreciated

Cheers

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[jedel]

OK, I have a little more information. It seems that the code will work, but only once. When I try it again i get the error

"Eorror is Object variable or With block variable not set"

Here is the entire module:

Private Sub cbomonth_AfterUpdate()
On Error GoTo err_Handler

    'Excel object variables
    Dim appExcel As Excel.Application
    Dim wbk As Excel.Workbook
    Dim wks As Excel.Worksheet
    
    Dim sTemplate As String
    Dim sTempFile As String
    Dim sOutput As String
    Dim Counter
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim sSQL As String
    Dim IRecords As Long
    Dim iRow As Integer
    Dim iCol As Integer
    Dim iFld As Integer
    Dim mth As String
    Dim path As String

path = Application.CurrentProject.path
MsgBox path

    mth = Me.cboMonth
    'MsgBox "month is: " & mth, vbOKOnly
    
    vcol = Array("b", "c", "d", "e", "f", "g", "i", "j", "k", "l")
    
   sOutput = CurrentProject.path & "\RosterTemplate.xlt"
    
    'Create the Excel Application, Workbook and Worksheet and Database object
    Set appExcel = New Excel.Application
    appExcel.Visible = True
    Set wbk = appExcel.Workbooks.Open(sOutput)
    
    sSQL = "SELECT * FROM tblSample" _
    & " WHERE dteMonth='" & Forms!DteSelectFRM!cboMonth _
    & "' ORDER BY AppointmentDate, AppointmentTime"
    Debug.Print sSQL

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(sSQL, dbOpenDynaset)
    
    If Not rst.BOF Then
    rst.MoveFirst
    
Counter = 0
    Do While Not rst.EOF
        With wbk
            .Sheets("Week1,2").Cells("1", vcol(Counter)) = rst.Fields("AppointmentDate")
            .Sheets("Week1,2").Cells("2", vcol(Counter)) = rst.Fields("AppointmentDesc")
            .Sheets("Week1,2").Cells("4", vcol(Counter)) = rst.Fields("AppointmentTime")
            .Sheets("Week1,2").Cells("5", vcol(Counter)) = rst.Fields("MC")
            .Sheets("Week1,2").Cells("6", vcol(Counter)) = rst.Fields("Who")
            .Sheets("Week1,2").Cells("7", vcol(Counter)) = rst.Fields("LeadVox")
            .Sheets("Week1,2").Cells("8", vcol(Counter)) = rst.Fields("Vox1")
            .Sheets("Week1,2").Cells("9", vcol(Counter)) = rst.Fields("vox2")
            .Sheets("Week1,2").Cells("10", vcol(Counter)) = rst.Fields("vox3")
            .Sheets("Week1,2").Cells("11", vcol(Counter)) = rst.Fields("vox4")
            .Sheets("Week1,2").Cells("12", vcol(Counter)) = rst.Fields("vox5")
            .Sheets("Week1,2").Cells("13", vcol(Counter)) = rst.Fields("vox6")
            .Sheets("Week1,2").Cells("14", vcol(Counter)) = rst.Fields("piano")
            .Sheets("Week1,2").Cells("15", vcol(Counter)) = rst.Fields("keys1")
            .Sheets("Week1,2").Cells("16", vcol(Counter)) = rst.Fields("keys2")
            .Sheets("Week1,2").Cells("17", vcol(Counter)) = rst.Fields("LGtr")
            .Sheets("Week1,2").Cells("18", vcol(Counter)) = rst.Fields("RGtr")
            .Sheets("Week1,2").Cells("19", vcol(Counter)) = rst.Fields("AccGtr")
            .Sheets("Week1,2").Cells("20", vcol(Counter)) = rst.Fields("Bass")
            .Sheets("Week1,2").Cells("21", vcol(Counter)) = rst.Fields("sax")
            .Sheets("Week1,2").Cells("22", vcol(Counter)) = rst.Fields("Drums")
            .Sheets("Week1,2").Cells("23", vcol(Counter)) = rst.Fields("FOH")
            .Sheets("Week1,2").Cells("24", vcol(Counter)) = rst.Fields("SndStg")
            .Sheets("Week1,2").Cells("25", vcol(Counter)) = rst.Fields("Light")
            .Sheets("Week1,2").Cells("26", vcol(Counter)) = rst.Fields("LightAss")
            .Sheets("Week1,2").Cells("27", vcol(Counter)) = rst.Fields("Graphic")
            .Sheets("Week1,2").Cells("28", vcol(Counter)) = rst.Fields("vision")
            .Sheets("Week1,2").Cells("29", vcol(Counter)) = rst.Fields("cam1")
            .Sheets("Week1,2").Cells("30", vcol(Counter)) = rst.Fields("cam2")
            .Sheets("Week1,2").Cells("31", vcol(Counter)) = rst.Fields("rec")
            .Sheets("Week1,2").Cells("32", vcol(Counter)) = rst.Fields("Items")
            .Sheets("Week1,2").Cells("33", vcol(Counter)) = rst.Fields("Songlist")
    
' repeat these statements for each field
            Counter = Counter + 1

        End With
        rst.MoveNext
    Loop
End If
vcol2 = Array("M", "N", "O", "P", "Q")

sSQL = "SELECT * FROM tblSample" _
& " WHERE dteMonth='" & Forms!DteSelectFRM!cboMonth _
& "' And (Not (tblSample.Cdir) = 'isnull')" _
& " ORDER BY tblSample.AppointmentDate;"

Set rst = dbs.OpenRecordset(sSQL, dbOpenDynaset)

If Not rst.BOF Then
rst.MoveFirst
    
Counter = 0
    Do While Not rst.EOF
        With wbk
            .Sheets("Week1,2").Cells("1", vcol2(Counter)) = rst.Fields("AppointmentDate")
            .Sheets("Week1,2").Cells("5", vcol2(Counter)) = rst.Fields("Cdir")
            .Sheets("Week1,2").Cells("7", vcol2(Counter)) = rst.Fields("c1")
            .Sheets("Week1,2").Cells("8", vcol2(Counter)) = rst.Fields("c2")
            .Sheets("Week1,2").Cells("9", vcol2(Counter)) = rst.Fields("c3")
            .Sheets("Week1,2").Cells("10", vcol2(Counter)) = rst.Fields("c4")
            .Sheets("Week1,2").Cells("11", vcol2(Counter)) = rst.Fields("c5")
            .Sheets("Week1,2").Cells("12", vcol2(Counter)) = rst.Fields("c6")
            .Sheets("Week1,2").Cells("13", vcol2(Counter)) = rst.Fields("c7")
            .Sheets("Week1,2").Cells("14", vcol2(Counter)) = rst.Fields("c8")
            .Sheets("Week1,2").Cells("15", vcol2(Counter)) = rst.Fields("c9")
            .Sheets("Week1,2").Cells("16", vcol2(Counter)) = rst.Fields("c10")
            .Sheets("Week1,2").Cells("17", vcol2(Counter)) = rst.Fields("c11")
            .Sheets("Week1,2").Cells("18", vcol2(Counter)) = rst.Fields("c12")
            .Sheets("Week1,2").Cells("19", vcol2(Counter)) = rst.Fields("c13")
            .Sheets("Week1,2").Cells("20", vcol2(Counter)) = rst.Fields("c14")
            .Sheets("Week1,2").Cells("21", vcol2(Counter)) = rst.Fields("c15")
            .Sheets("Week1,2").Cells("22", vcol2(Counter)) = rst.Fields("c16")
            .Sheets("Week1,2").Cells("23", vcol2(Counter)) = rst.Fields("c17")
            .Sheets("Week1,2").Cells("24", vcol2(Counter)) = rst.Fields("c18")
            .Sheets("Week1,2").Cells("25", vcol2(Counter)) = rst.Fields("c19")
            .Sheets("Week1,2").Cells("26", vcol2(Counter)) = rst.Fields("c20")
    
' repeat these statements for each field
            Counter = Counter + 1
    
    End With
        rst.MoveNext
    Loop
End If

ActiveWorkbook.SaveAs FileName:=Me.cboMonth & "Roster" & Format(Date, "yyyy")

rst.Close
   
exit_Here:
'Cleanup all objects (resume next on errors)
Set wbk = Nothing


appExcel.Quit
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
Exit Sub

err_Handler:
    MsgBox "Error is " & Err.Description
    Resume exit_Here



End Sub

I have no idea why the object variable works the first time the database is opened and then will not work again after that. Any ideas?

Cheers

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[PHV]

Replace ActiveWorkbook with wbk

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jedel]

PHV,

You are a legend, take a star. Would you mind telling me why this would solve the problem?


-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[PHV]

ActiveWorkbook is an Excel object and thus you should use appExcel.ActiveWorkbook.
But the workbook object was already instantiated earlier in the code (Set wbk = ...)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886