troubleshooting Question

Excel automation from Access: Err# 462 , The remote server machine does not exist

Avatar of Dale Fye
Dale FyeFlag for United States of America asked on
Microsoft AccessMicrosoft ExcelMicrosoft Office
2 Comments1 Solution103 ViewsLast Modified:
I'm exporting an Access query to Excel and attempting to format some of the rows and columns.  

Sometimes, this code works, other times, I get the "The remote server machine does not exist or is unavailable" error message on the line indicated.  When it doesn't work, the header row is already populated with the column names.

Code is basically:
Dim rs as dao.Recordset
Dim xl as object
dim wbk as object
dim sht as object
dim rs as object

set rs = currentdb.querydefs("myQueryName").openrecordset

set xl = createobject("Excel.Application")
set wbk = xl.workbooks.Add
set sht = wbk.sheets(1)

'Write the column headers
For intLoop = 1 to rs.fields.count
     sht.cells(1, intLoop).Value = rs.fields(intloop-1).Name

'Highlight the column headers and copy the header column
sht.Range(Selection, Selection.End(xlToRight)).Select     'this is the line that raises the error
set rng = Selection                                       'this is used later in the code
with rng.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.14996795556505
    .PatternTintAndShade = 0
End With

sht.Range("A2").CopyFromRecordset rs

if NOT (rs is nothing) then
    set rs = nothing
end if

'whole bunch of additional formatting functions here

if NOT (rng is Nothing) then set rng = nothing
if NOT (sht is nothing) then set sht = nothing
if NOT (wbk is nothing) then set wbk = nothing
if NOT (xl is nothing) then set xl = nothing
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros