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

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
Next

'Highlight the column headers and copy the header column
sht.Range("B1").Select
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
    rs.close
    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

Open in new window

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 15

Fabrice LambertConsultingCommented: 2017-11-16

Hi,

Never ever use the Selection object, it is user dependant, by nature chaotic, and it is slow as hell !!
As a developer, you don't want to use chaotic objects.
Plus: set rng = Selection
Selection alone is the same as ActiveWorkbook.ActiveSheet.Selection, wich guarantees do you have that the workbook you created a couple of instructions before is the active one ?
Answer is: None (the user can have one or more workbooks already open).

Replace your lines 19 to 21 by:

Set rng = sht.Range(sht.Range("B1"), sht.Range("B1").End(xlToRight))

and it should work.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 52

Dale FyeOwner, Developing Solutions LLCAuthor Commented: 2017-11-30

Thanks, Fabrice. Somehow this question had fallen off my radar, although I did use your suggestions to resolve the issue.

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

Who is Participating?