Remote Server Machine does not exit or is unavailable

[KarlAlsop]

Hi All,

I am having someprolems with the following code:

Dim FPath, AdminEMail, UserEMail As String
Dim SupUp, SysDef As Object
Dim QName, Name, LastName, QD As String
Dim i As Integer
Dim XL As Excel.Application
Dim Wbk As Workbook

FPath = DLookup("[Default Value]", "System Defaults", "[System Default ID] = 6")
Set SupUp = Application.CurrentDb.OpenRecordset("Supplier Updates - Names & Lines")
i = 1
SupUp.MoveFirst
LastName = "X"
Do
Name = SupUp.[Supplier]
If Name = LastName Then
GoTo Nxt
End If
QName = SupUp.[Supplier]
Name = Replace(Name, ".", "")
Name = Replace(Name, "^", "")
Name = Replace(Name, "\", "")
Name = Replace(Name, "/", "")
Name = Replace(Name, "$", "USD")
QD = "SELECT [Supplier Updates - Latest].MaxOfSent, [Supplier Updates - Latest].[Ordered Part Number], [Supplier Updates - Latest].Supplier, [Supplier Updates - Latest].[PO Number], [Supplier Updates - Latest].[PO Line Number], [Supplier Updates - Latest].[PO Open Qty], [Supplier Updates - Latest].[Customer Required Date], [Supplier Updates - Latest].[Promised Delivery Date], [Supplier Updates - Latest].Expeditor, [Supplier Updates - Latest].[New Promise], [Supplier Updates - Latest].Comments FROM [Supplier Updates - Latest] WHERE ((([Supplier Updates - Latest].Supplier) = " & "'" & QName & "'" & "));"
CurrentDb.CreateQueryDef Name, QD
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel5, Name, FPath & Name & " " & Format(Now(), "YYYY-MM-DD") & ".xls"
CurrentDb.QueryDefs.Delete Name
Nxt:
LastName = Name
SupUp.MoveNext
i = i + 1
Loop Until i = 5 'SupUp.EOF
i = 1
SupUp.MoveFirst
LastName = "X"
Do
Name = SupUp.[Supplier]
If Name = LastName Then
GoTo Nxt2
End If
Set XL = New Excel.Application
XL.Visible = True
Set Wbk = XL.Workbooks.Open(FPath & Name & " " & Format(Now(), "YYYY-MM-DD") & ".xls")
With Wbk
With .Sheets(1)
.Range(Range("A1").CurrentRegion).Select
.ListObjects.Add(xlSrcRange, Selection, , xlYes).Name = "SupplierUpdates"
.ListObjects("SupplierUpdates").TableStyle = "TableStyleLight1"
.Range("A:A").NumberFormat = "0"
.Cells.EntireColumn.AutoFit
.Columns("L:L").Select
.Range(Selection, Selection.End(xlToRight)).EntireColumn.Hidden = True
.Range("A1").End(xlDown).Offset(1).Rows.Select
.Range(Selection, Selection.End(xlDown)).EntireRow.Hidden = True
With .Range("J:J").Validation
.Delete
.Add xlValidateDate
.IgnoreBlank = True
.InputTitle = "Date"
.ErrorTitle = "Date"
.InputMessage = "Please enter a valid date in the format DD/MM/YYYY."
.ErrorMessage = "Please enter a valid date in the format DD/MM/YYYY."
.ShowInput = True
.ShowError = True
End With
.Columns("J:K").Locked = False
.Columns("K:K").ColumnWidth = 55
.Columns("J:J").NumberFormat = "dd/mm/yyyy"
End With
.ActiveSheet.Protect Password:="supup", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True
.Protect Password:="supup", Structure:=True, Windows:=True
.Save
.Close
End With
Nxt2:
LastName = Name
SupUp.MoveNext
i = i + 1
Loop Until i = 5 'SupUp.EOF

When I get to the lie in red I get an error, Run-time error '46': The remote server machine does not exit or is unavailabe.

Can anyone help?

Much appreciated!

Karl

[hansup]

Although you're using Access to automate Excel, it seems to me this problem is an Excel issue. Therefore I think you would be better off to post this question in the Excel Help section of this site.