ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   What Causes Automation Error When Excel file on Network? (https://www.excelbanter.com/excel-programming/376522-what-causes-automation-error-when-excel-file-network.html)

Perico[_2_]
What Causes Automation Error When Excel file on Network?
 
I'm running Excel from Access 2003. The following code runs without error
when I create the Excel csv file on my local computer. But when I create the
Excel csv file on the Network, I get an error on this line:

Set xlWbDet = xlAppDet.workbooks.Add

Here is the relevant code:

Dim xlAppMst As Object
Dim xlWbMst As Object
Dim IStartedXL As Boolean
Dim i As Integer, iCount As Integer

On Error Resume Next
Set xlAppMst = GetObject(, "Excel.Application")
On Error GoTo 0
If xlAppMst Is Nothing Then
Set xlAppMst = CreateObject("Excel.Application")
IStartedXL = True
End If

Set xlWbMst = xlAppMst.workbooks.Add

xlAppMst.Range("A1:AA3000").NumberFormat = "@"

'xlWbMst.SaveAs fNameMst

'---Access
Dim rsCSVmst As DAO.Recordset
Set rsCSVmst = db.OpenRecordset("qtmpMasterCSV")
iCount = rsCSVmst.Fields.Count
'---

xlAppMst.Range("A1").Select

For i = 0 To iCount - 1
xlAppMst.Cells(1, i + 1).Value = rsCSVmst.Fields(i).Name
Next i
i = 0

xlAppMst.Range("A2").CopyFromRecordset rsCSVmst

xlWbMst.SaveAs fNameMst

xlWbMst.Close False
If IStartedXL Then xlAppMst.Quit

Set xlWbMst = Nothing
Set xlAppMst = Nothing

rsCSVmst.Close
Set rsCSVmst = Nothing

'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
Dim xlAppDet As Object
Dim xlWbDet As Object

On Error Resume Next
Set xlAppDet = GetObject(, "Excel.Application")
On Error GoTo 0
If xlAppDet Is Nothing Then
Set xlAppDet = CreateObject("Excel.Application")
IStartedXL = True
End If

Set xlWbDet = xlAppDet.workbooks.Add '<---AUTOMATION ERROR HERE

xlAppDet.Range("A1:AA3000").NumberFormat = "@"

'---Access
Dim rsCSVDet As DAO.Recordset
Set rsCSVDet = db.OpenRecordset("qtmpDetailCSV")
iCount = rsCSVDet.Fields.Count
'---

xlAppDet.Range("A1").Select
For i = 0 To iCount - 1
xlAppDet.Cells(1, i + 1).Value = rsCSVDet.Fields(i).Name
Next i
i = 0

xlAppDet.Range("A2").CopyFromRecordset rsCSVDet

xlWbDet.SaveAs fNameDet

xlWbDet.Close False
If IStartedXL Then xlAppDet.Quit

Set xlWbDet = Nothing
Set xlAppDet = Nothing

What would cause this?

Perico[_2_]
What Causes Automation Error When Excel file on Network?
 

I should have added that I don't get the error on Excel is running. I only
get it when Excel is not running.

"Perico" wrote:

I'm running Excel from Access 2003. The following code runs without error
when I create the Excel csv file on my local computer. But when I create the
Excel csv file on the Network, I get an error on this line:

Set xlWbDet = xlAppDet.workbooks.Add

Here is the relevant code:

Dim xlAppMst As Object
Dim xlWbMst As Object
Dim IStartedXL As Boolean
Dim i As Integer, iCount As Integer

On Error Resume Next
Set xlAppMst = GetObject(, "Excel.Application")
On Error GoTo 0
If xlAppMst Is Nothing Then
Set xlAppMst = CreateObject("Excel.Application")
IStartedXL = True
End If

Set xlWbMst = xlAppMst.workbooks.Add

xlAppMst.Range("A1:AA3000").NumberFormat = "@"

'xlWbMst.SaveAs fNameMst

'---Access
Dim rsCSVmst As DAO.Recordset
Set rsCSVmst = db.OpenRecordset("qtmpMasterCSV")
iCount = rsCSVmst.Fields.Count
'---

xlAppMst.Range("A1").Select

For i = 0 To iCount - 1
xlAppMst.Cells(1, i + 1).Value = rsCSVmst.Fields(i).Name
Next i
i = 0

xlAppMst.Range("A2").CopyFromRecordset rsCSVmst

xlWbMst.SaveAs fNameMst

xlWbMst.Close False
If IStartedXL Then xlAppMst.Quit

Set xlWbMst = Nothing
Set xlAppMst = Nothing

rsCSVmst.Close
Set rsCSVmst = Nothing

'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
Dim xlAppDet As Object
Dim xlWbDet As Object

On Error Resume Next
Set xlAppDet = GetObject(, "Excel.Application")
On Error GoTo 0
If xlAppDet Is Nothing Then
Set xlAppDet = CreateObject("Excel.Application")
IStartedXL = True
End If

Set xlWbDet = xlAppDet.workbooks.Add '<---AUTOMATION ERROR HERE

xlAppDet.Range("A1:AA3000").NumberFormat = "@"

'---Access
Dim rsCSVDet As DAO.Recordset
Set rsCSVDet = db.OpenRecordset("qtmpDetailCSV")
iCount = rsCSVDet.Fields.Count
'---

xlAppDet.Range("A1").Select
For i = 0 To iCount - 1
xlAppDet.Cells(1, i + 1).Value = rsCSVDet.Fields(i).Name
Next i
i = 0

xlAppDet.Range("A2").CopyFromRecordset rsCSVDet

xlWbDet.SaveAs fNameDet

xlWbDet.Close False
If IStartedXL Then xlAppDet.Quit

Set xlWbDet = Nothing
Set xlAppDet = Nothing

What would cause this?

Perico[_2_]
What Causes Automation Error When Excel file on Network?
 
I may have cured this problem by using a message box to permit, in effect
pause, the first automation routine to complete before inaugerating the
second automation routine.

"Perico" wrote:


I should have added that I don't get the error on Excel is running. I only
get it when Excel is not running.

"Perico" wrote:

I'm running Excel from Access 2003. The following code runs without error
when I create the Excel csv file on my local computer. But when I create the
Excel csv file on the Network, I get an error on this line:

Set xlWbDet = xlAppDet.workbooks.Add

Here is the relevant code:

Dim xlAppMst As Object
Dim xlWbMst As Object
Dim IStartedXL As Boolean
Dim i As Integer, iCount As Integer

On Error Resume Next
Set xlAppMst = GetObject(, "Excel.Application")
On Error GoTo 0
If xlAppMst Is Nothing Then
Set xlAppMst = CreateObject("Excel.Application")
IStartedXL = True
End If

Set xlWbMst = xlAppMst.workbooks.Add

xlAppMst.Range("A1:AA3000").NumberFormat = "@"

'xlWbMst.SaveAs fNameMst

'---Access
Dim rsCSVmst As DAO.Recordset
Set rsCSVmst = db.OpenRecordset("qtmpMasterCSV")
iCount = rsCSVmst.Fields.Count
'---

xlAppMst.Range("A1").Select

For i = 0 To iCount - 1
xlAppMst.Cells(1, i + 1).Value = rsCSVmst.Fields(i).Name
Next i
i = 0

xlAppMst.Range("A2").CopyFromRecordset rsCSVmst

xlWbMst.SaveAs fNameMst

xlWbMst.Close False
If IStartedXL Then xlAppMst.Quit

Set xlWbMst = Nothing
Set xlAppMst = Nothing

rsCSVmst.Close
Set rsCSVmst = Nothing

'DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
Dim xlAppDet As Object
Dim xlWbDet As Object

On Error Resume Next
Set xlAppDet = GetObject(, "Excel.Application")
On Error GoTo 0
If xlAppDet Is Nothing Then
Set xlAppDet = CreateObject("Excel.Application")
IStartedXL = True
End If

Set xlWbDet = xlAppDet.workbooks.Add '<---AUTOMATION ERROR HERE

xlAppDet.Range("A1:AA3000").NumberFormat = "@"

'---Access
Dim rsCSVDet As DAO.Recordset
Set rsCSVDet = db.OpenRecordset("qtmpDetailCSV")
iCount = rsCSVDet.Fields.Count
'---

xlAppDet.Range("A1").Select
For i = 0 To iCount - 1
xlAppDet.Cells(1, i + 1).Value = rsCSVDet.Fields(i).Name
Next i
i = 0

xlAppDet.Range("A2").CopyFromRecordset rsCSVDet

xlWbDet.SaveAs fNameDet

xlWbDet.Close False
If IStartedXL Then xlAppDet.Quit

Set xlWbDet = Nothing
Set xlAppDet = Nothing

What would cause this?


All times are GMT +1. The time now is 12:14 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com