Working on Excel Workbook from Access while other workbooks are open

[njacovides]

Hi all,
I've created a MS Access VBA automation where a table is exported to an excel workbook, and then the workbook is opened and a pivot table is created. The application works fine as long as there are no other excel workbooks open. If another workbook is open however, the application refuses to work. I've attached the function I created below. Any comments/ideas more than welcomed!



Function CreatePivot(fileName As String)
Dim objExcel As Excel.Application
Dim myWorkbook As Excel.workbook
Dim WorkbookPath As String
Dim WSD As Excel.Worksheet
Dim WSPT As Excel.Worksheet
Dim PTCache As Excel.PivotCache
Dim PT As Excel.PivotTable
Dim PRange As Excel.Range
Dim FinalRow As Long
Dim FinalCol As Long
Dim ExcelWasNotRunning As Boolean

WorkbookPath = Application.CurrentProject.Path

'------------------- Later Addition
ExcelWasNotRunning = False

On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")

If Err Then
ExcelWasNotRunning = True
Set objExcel = New Excel.Application
End If
'------------------- Later Addition

'Set objExcel = New Excel.Application
Set myWorkbook = objExcel.Workbooks.Open(fileName)

Set WSD = myWorkbook.Worksheets("pending faults (DATA)")
Set WSPT = myWorkbook.Sheets.Add(after:=Sheets(1), Type:=xlWorksheet, Count:=1)
WSPT.Name = "PivotTable"


myWorkbook.Save

' Delete any prior pivot tables
'For Each PT In WSPT.PivotTables
' PT.TableRange2.Clear
'Next PT

' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(myWorkbook.Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, myWorkbook.Application.Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = myWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange)

' Create the Pivot Table from the Pivot Cache
'Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:="PivotTable1")
Set PT = PTCache.CreatePivotTable(TableDestination:=WSPT. _
Cells(2, 2), TableName:="PivotTable1")

' Turn off updating while building the table
PT.ManualUpdate = True

' Set up the row & column fields
PT.AddFields RowFields:=Array("days dalay"), _
ColumnFields:="Region"

' Set up the data fields
With PT.PivotFields("Region")
.Orientation = xlDataField
.Function = xlCount
.Position = 1
End With

' Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True

' Save Workbook and Exit Excel.
myWorkbook.Save
myWorkbook.Close
If ExcelWasNotRunning = True Then
objExcel.Quit
End If
Set PRange = Nothing
Set PT = Nothing
Set PTCache = Nothing
Set WSD = Nothing
Set WSPT = Nothing
Set myWorkbook = Nothing
Set objExcel = Nothing
End Function

 

[PHV]

Always full qualify the Excel objects you use, eg:
Set WSPT = myWorkbook.Sheets.Add(after:=[!]myWorkbook.[/!]Sheets(1), Type:=xlWorksheet, Count:=1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[njacovides]

You are absolutely right!! I can't believe I didn't notice that. Programmers syndrome I guess.

Many many thanks for pointing it out!!!!

Best regards,
NJ.