locked
Excel worksheet reference erroring RRS feed

  • Question

  • Hi, I am trying to do something simple. I have two Access tables. I want to put each tbl

    in its own worksheet wih the following code. I am somehow getting the reference to the

    worksheet wrong because it errors when I get to the second worksheet reference.

    Please help.

    Public Sub GET_DATA() Dim xlApp As Object Dim xlWB As Workbook Set xlApp = CreateObject("Excel.Application") Set xlWB = xlApp.Workbooks.Add MsgBox xlWB.ActiveSheet.Name Set xlWS = xlWB.Worksheets("Sheet1") Call GET_tblA(xlApp, xlWB, xlWS) Set xlWS = xlWB.Worksheets("Sheet2") 'errors here Call GET_tblB(xlApp, xlWB, xlWS) End Sub


    Monday, August 12, 2013 4:00 PM

Answers

  • Try this version:

    Public Sub GET_DATA()
        Dim xlApp As Object
        Dim xlWB As Workbook
        Set xlApp = CreateObject("Excel.Application")
        ' Create workbook with a single worksheet
        Set xlWB = xlApp.Workbooks.Add(-4167) ' xlWBATWorksheet
        ' Refer to the first (and only) worksheet
        Set xlWs = xlWB.Worksheets(1)
        Call GET_tblA(xlApp, xlWB, xlWs)
        ' Create a second worksheet
        Set xlWs = xlWB.Worksheets.Add(After:=xlWs)
        Call GET_tblB(xlApp, xlWB, xlWs)
    End Sub


    Regards, Hans Vogelaar

    Monday, August 12, 2013 4:21 PM

All replies

  • Try this version:

    Public Sub GET_DATA()
        Dim xlApp As Object
        Dim xlWB As Workbook
        Set xlApp = CreateObject("Excel.Application")
        ' Create workbook with a single worksheet
        Set xlWB = xlApp.Workbooks.Add(-4167) ' xlWBATWorksheet
        ' Refer to the first (and only) worksheet
        Set xlWs = xlWB.Worksheets(1)
        Call GET_tblA(xlApp, xlWB, xlWs)
        ' Create a second worksheet
        Set xlWs = xlWB.Worksheets.Add(After:=xlWs)
        Call GET_tblB(xlApp, xlWB, xlWs)
    End Sub


    Regards, Hans Vogelaar

    Monday, August 12, 2013 4:21 PM
  • It is erroring out here:

        ' Create a second worksheet
        Set xlWs = xlWB.Worksheets.Add(After:=xlWs)
    Error text: Run-time error '91': Object variable or With block variable not set


    Monday, August 12, 2013 5:42 PM
  • So sorry Hans, your code worked from above.

    My bad:

    I was killing the application object in the first function, so the second function was refering to a disposed application object and erroring...this was acutally my problem working with the initial code in the first place.

    Monday, August 12, 2013 5:58 PM