Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2008

    Unanswered: Problem with function

    Hi Everyone,
    I have a problem with a function which creates an Excel application. When I click a button on my form (frmExcelDataEntry), it runs the same function depending on the parameters. Problem is that the function creates a different new workbook and a new worksheet every time a call statement is made. I would like all the worksheets in only one workbook when all the call statements are made. Any suggestion?

    Private Sub cmdXcelRun_Click()
    If chkCommonAsset = True Then
    Call XcelF("CommonAssets", "qryXcelCommonAsset", "qryXlCommonDate")
    End If
    If chkDisposableGood = True Then
    Call XcelF("Disposable", "qryXcelDisposable", "qryXlDispDate")
    End If
    If chkSpecialGood = True Then
    Call XcelF("SpecialGood", "qryXcelSpecial", "qryXlSpecDate")
    End If
    End Sub

    ' In Module 1

    Public Function XcelF(WsName As String, qryType As String, qryDate As String)
    Dim xlApp As New Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set xlApp = CreateObject("Excel.application")
    xlApp.Visible = True

    Set xlWB = xlApp.Workbooks.Add ‘ I think the problem lies here
    Set xlWS = xlWB.Sheets.Add() ‘ I think the problem lies here

    xlWS.Name = WsName

    Set dbs = CurrentDb()

    If Forms!frmExcelDataEntry!SortOptions = 1 Then
    Set rst = dbs.OpenRecordset(qryType)'
    Set rst = dbs.OpenRecordset(qryDate)
    End If
    Set xlApp = Nothing
    Set rst = Nothing
    Set xlWS = Nothing
    Set xlWB = Nothing


  2. #2
    Join Date
    Jan 2008
    Problem with function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts