Acess/Excel VBA: ActiveCell Refers to Wrong Workbook

[kestefon]

I have a procedure in Access that exports three reports to Excel, combines them in a single workbook, and then applies Excel formatting to some of the sheets. This procedure works the first time, but if I run it again it fails. In particular, I notice that things like "ActiveCell" and "Selection" will continue to refer to the previous workbook (dim xlWkb), even though I've activated the current worksheet/workbook.



Any ideas what I might be doing wrong here?

Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlBwkb As Excel.Workbook


Set xlApp = New Excel.Application





With xlApp
    .Visible = True
    Set xlWkb = .Workbooks.Add
End With

'BillingTemp, etc. are defined earlier in the procedure. I essentially am exporting Access reports to temporary Excel files, and then opening the files and putting them in a single workbook.


Set xlBwkb = xlApp.Workbooks.Open(BillingTemp)
xlBwkb.Sheets(1).Copy After:=xlWkb.Sheets(1)
xlBwkb.Close False


Set xlBwkb = xlApp.Workbooks.Open(PrismTemp)
xlBwkb.Sheets(1).Copy After:=xlWkb.Sheets(2)
xlBwkb.Close False


Set xlBwkb = xlApp.Workbooks.Open(InvoiceTemp)
xlBwkb.Sheets(1).Copy After:=xlWkb.Sheets(3)


xlBwkb.Close False


xlWkb.Sheets("Sheet1").Delete




'After combining the exported reports into one Excel workbook, I apply some formatting to some of the sheets.




Dim i As Integer
Dim rng As Range
Dim b As Range
Dim lastRowB As Integer
Dim Indication As String
Dim ws As Worksheet


xlWkb.Activate


Set ws = xlWkb.Sheets("BillingWorkbook")


ws.Activate


lastRowB = ws.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = ws.Range("B1:B" & lastRowB)
Indication = ws.Range("A2").Value




For Each b In rng
    b.Activate


    Select Case b.Value
    
    Case "2014 Billed in 2014_Planned"


    b.Offset(0, 4).Range("A1").Value = "'January 2014"
    b.Offset(0, 5).Range("A1").Value = "'February 2014"
    b.Offset(0, 6).Range("A1").Value = "'March 2014"
    b.Offset(0, 7).Range("A1").Value = "'April 2014"
    b.Offset(0, 8).Range("A1").Value = "'May 2014"
    b.Offset(0, 9).Range("A1").Value = "'June 2014"
    b.Offset(0, 10).Range("A1").Value = "'July 2014"
    b.Offset(0, 11).Range("A1").Value = "'August 2014"
    b.Offset(0, 12).Range("A1").Value = "'September 2014"
    b.Offset(0, 13).Range("A1").Value = "'October 2014"
    b.Offset(0, 14).Range("A1").Value = "'November 2014"
    b.Offset(0, 15).Range("A1").Value = "'December 2014"
    b.Offset(0, 3).Resize(1, 13).Interior.Color = 39423
    
 'Etc, etc.
    


    End Select


Next b








    ws.Columns.EntireColumn.Select
    Selection.NumberFormat = "$#,##0_);[Red]($#,##0)"
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
    End With




    ws.Columns(2).Select
    Selection.Find(What:="2014 Billed in 2014_Planned", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Select
    


       'Etc. Removed excess code for this example. 
 






Set ws = Nothing
Set xlBwkb = Nothing
Set xlWkb = Nothing
Set b = Nothing
Set rng = Nothing
Set xlApp = Nothing

[June7]

What do you mean by 'run it again' - next time you open the db or repeat in the same db session? I don't see code that quits Excel. Opening Excel objects sets a process that can be seen listed in Windows Task Manager. Might need to make sure this process is killed at the end of the procedure by quiting Excel.

[kestefon]

I have the procedure linked to a form button. So, I just mean pressing the button and running the procedure again. The end result of the procedure is a an Excel workbook that the user can manually save, so wouldn't quitting Excel require saving the file with VBA?

[June7]

I have encountered frustration with this type of procedure before, review http://forums.aspfree.com/microsoft-...el-413629.html. Actually, I thought setting the Excel app visible should allow the process to clear when user saves and quits.

I don't have requirements to export to Excel. I do have one Excel file that programmatically pulls data from Access.

[kestefon]

Thanks. I removed new from my Set statement: "Set xlApp = New Excel.Application", so it now reads "Set xlApp = Excel.Application.", and I used the ".Visible" trick from your post. Both factors seemed to have been contributing to my problem, so now it's working.