Laurence Martin
asked on
Runtime Error on Workbooks.Add
I have an Access Database that wants to send information in an Excel spreadsheet.
An extract of the code is as follows, but the code falls over at .Workbooks.Add with the following error.
We only get this error in a terminal server environment. When on a Windows desktop it works fine.
Can anyone tell me what could be going wrong? or advise me how to make the code more robust.
Cheers
'Call OpenEDRTemplate(strFilenam e, True, strOrderNo)
Set myXL = New Excel.Application
With myXL
myXL.Visible = True
.Workbooks.Add "\\sc-addfs-01\dfs\Orders\ Templates\ Error Damage Master Template.xltx"
.ActiveWorkbook.SaveAs (strFilename)
Set myWkb = .ActiveWorkbook
Set myWks = myWkb.ActiveSheet
With myWks
.Range("dealer_name").Valu e = strDealer
.Range("sc_acno").Value = strACNo
.Range("sc_order_no").Valu e = strOrderNo
.Range("customer_name").Va lue = strCustomer
.Range("dealer_order_no"). Value = strDealerOrderNo
'write room names
bytItem = 1
.Range("b19").Select
End With
jump1:
stSelect = "SELECT dbo_tbl131OrderLine.f131in tLineNumbe r, dbo_tbl131OrderLine.f131st rRoom, dbo_tbl103Orders.f103strSC Order "
stFrom = "FROM (dbo_tbl103Orders INNER JOIN dbo_tbl130OrderLineBase ON dbo_tbl103Orders.f103intOr derId = dbo_tbl130OrderLineBase.f1 30intOrder Id) INNER JOIN dbo_tbl131OrderLine ON dbo_tbl130OrderLineBase.f1 30intOrder LineBaseId = dbo_tbl131OrderLine.f131in tOrderLine BaseId "
stWhere = "WHERE (((dbo_tbl131OrderLine.f13 1intLineNu mber) = " & bytItem & ") And ((dbo_tbl103Orders.f103str SCOrder) = '" & strOrderNo & "')) ORDER BY dbo_tbl131OrderLine.f131in tLineNumbe r;"
stSQL = stSelect & stFrom & stWhere
Set recEDROrder = CurrentDb.OpenRecordset(st SQL, dbOpenSnapshot, dbSeeChanges)
If recEDROrder.RecordCount = 0 Then
recEDROrder.Close
Else
recEDROrder.MoveFirst
myXL.ActiveCell.Offset(byt Item).Valu e = recEDROrder!f131strRoom
bytItem = bytItem + 1
recEDROrder.Close
GoTo jump1
End If
Set recEDROrder = Nothing
'protect
myWkb.ActiveSheet.Protect
.ActiveWorkbook.Close (True)
.Quit
End With
Set myWks = Nothing
Set myWkb = Nothing
Set myXL = Nothing
An extract of the code is as follows, but the code falls over at .Workbooks.Add with the following error.
We only get this error in a terminal server environment. When on a Windows desktop it works fine.
Can anyone tell me what could be going wrong? or advise me how to make the code more robust.
Cheers
'Call OpenEDRTemplate(strFilenam
Set myXL = New Excel.Application
With myXL
myXL.Visible = True
.Workbooks.Add "\\sc-addfs-01\dfs\Orders\
.ActiveWorkbook.SaveAs (strFilename)
Set myWkb = .ActiveWorkbook
Set myWks = myWkb.ActiveSheet
With myWks
.Range("dealer_name").Valu
.Range("sc_acno").Value = strACNo
.Range("sc_order_no").Valu
.Range("customer_name").Va
.Range("dealer_order_no").
'write room names
bytItem = 1
.Range("b19").Select
End With
jump1:
stSelect = "SELECT dbo_tbl131OrderLine.f131in
stFrom = "FROM (dbo_tbl103Orders INNER JOIN dbo_tbl130OrderLineBase ON dbo_tbl103Orders.f103intOr
stWhere = "WHERE (((dbo_tbl131OrderLine.f13
stSQL = stSelect & stFrom & stWhere
Set recEDROrder = CurrentDb.OpenRecordset(st
If recEDROrder.RecordCount = 0 Then
recEDROrder.Close
Else
recEDROrder.MoveFirst
myXL.ActiveCell.Offset(byt
bytItem = bytItem + 1
recEDROrder.Close
GoTo jump1
End If
Set recEDROrder = Nothing
'protect
myWkb.ActiveSheet.Protect
.ActiveWorkbook.Close (True)
.Quit
End With
Set myWks = Nothing
Set myWkb = Nothing
Set myXL = Nothing
ASKER
Yes, and the users can open the file manually.
You may have an issue with "\\sc-addfs-01\dfs\Orders\Templates\Error Damage Master Template.xltx", I mean "is the share server working in TS environment ?"
ASKER
I don't administer the server, so I don't know. Is there a way to test that?
Yes with an RDP client, goto explorer just key the path "\\sc-addfs-01\dfs\Orders\Templates" and look if you see the excel file
ASKER
Yes, that works.
Thank-you for your help Louis
Thank-you for your help Louis
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help. It would be interesting to know why the different environments behave differently,
Is excel installed on your server ?
regards