P
Perico
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?
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?